Transaction support for KCML databases

The WHILE TRANS and THROW ROLLBACK statements in KCML are used to control database transactions in a formal way. Transactions are defined in the context of a database connection and the connection handle is used to specify the context.

KCML databases are initially in an auto commit mode whereby a transaction is started automatically when any database statement, such as a KI_WRITE changes the database. This implied transaction is automatically committed if the statement succeeds and rolled back if it fails. However this is not very efficient as there is an overhead to start and stopping transactions. Furthermore it makes locking rows impossible as the locks are discarded by the automatic commit. So whenever a row is locked, say by a KI_READ_HOLD operation, KCML will start an implied transaction which will last until that particular row is unlocked by a KI_REWRITE, KI_DELETE, KI_UNLOCK or by closing the table containing the row. In the last two cases the transaction will be rolled back rather than committed.

A program can start an explicit transaction with the WHILE TRANS statement and the transaction will be commited on the corresponding END TRANS. At any point in the transaction the programmer can abandon any changes by issuing a THROW ROLLBACK statement which rolls back the changes and restarts the program after the END TRANS.

A WHILE TRANS/END TRANS block can be nested however KCML will consider them to belong to the same transaction context. A nested inner END TRANS has no effect and the commit will only happen at the outer END TRANS. A THROW ROLLBACK will rollback to the outer transaction and restart after its END TRANS updating only its transaction status variable.

If a WHILE TRANS is issued when the program is already in an implied transaction by virtue of a row being locked in auto commit mode, then the explicit transaction takes ownership of the implied transaction which will be committed at its END TRANS.

A program already in a transaction, either implied or explicit, can issue an autonomous transaction which will execute in a new transactional context. Inside an autonomous transaction any changes made by the outer transaction will not be seen and it will be possible to deadlock agains that transaction. An autonomous transaction is started by add a TRUE flag for the third argument in WHILE TRANS and it ends on reaching its END TRANS when a commit takes place. A roolback in the outer transaction after the autonomous one has committed cannot undo the changes committed by that transaction. Autonomous transactions can be nested but they each execute in their own separate transactional context.

There is no support for distributed transactions at this time and all nested transactions must be on the same connection.

Once a transaction has been started subsequent attempts to lock a row will block indefinitely if another process already has it locked. This is to aid in deadlock detection. If a deadlock is detected then KCML will rollback the current transaction to the outer WHILE TRANS and restart from there. Consequently it is very important that programmers take this into account and reinitialize all counters and states at the start of the transaction block in case it is re-executed.

All locks taken inside a transaction are released when the transaction completes. A lock that spans a transaction is called a long term lock and must be specified using the _KDB_LOCK_EXT enumeration on the KI_READ_HOLD call.

If a program executes a data definition language SQL statement such as a CREATE TABLE or CREATE INDEX within a transaction KCML will return an error. This is because some databases (e.g. Oracle) issue a commit before a DDL statement and this would prematurely and silently terminate the transaction. By returning an error KCML will alert the programmer to this behaviour.

If a form blocks waiting for input while any sort of transaction is active then an error will be thrown. However it perfectly possible to embed an entire transaction within a form's event handler.

A program can discover the transaction depth from the INFO_CONN_TransactionDepth field returned by a KI_INFO call on the connection handle.

In the diagram the program was originally state T0 with no transaction in effect. Taking an implied lock with a KI_READ_HOLD or issuing a WHILE TRANS will move to a transactional state of T1. Nested WHILE TRANS FALSE statements will stay in that transactional context. If an autonomous transaction is started by a WHILE TRANS TRUE then that takes place in an independent transaction T2. Not shown in the diagram is the extra transactional contexts that result from nesting autonomous transactions.

Compatibility

This transaction model was introduced with KCML 6.60 and initially only implemented for Oracle and PostgreSQL databases. The previous transaction support in KDB was withdrawn at that time.

When converting legacy applications without any explicit transactions declared, it may be possible to get better performance using Optimistic Transaction mode which will attempt to batch updates into ad hoc transactions.