WHILE TRANS

The WHILE TRANS block allows a KCML programmer to specify the range of a database transaction. All the code between the WHILE TRANS and the END TRANS is considered to be part of a transaction for the database identified by the connection number. When KCML reaches the END TRANS statement it will commit the transaction updating the database and set the status code receiver to _KE_SUCCESS. If the transaction is abandoned due to a THROW ROLLBACK being executed then the status code is set to the status passed in the THROW ROLLBACK. If the commit fails the status code is set to KE_TRANSINTERNAL and you should check the last error on the connection handle to discover why it failed. Programmers must check the status code after a transaction to confirm that it was successful. The transaction status code variable should be a local variable and not used for any other purpose.

The WHILE TRANS block can be automatically and transparently restarted if KCML detects a database deadlock, if the database disconnects and reconnects following the failure of a node in a database cluster, or upon a serialization failure in a serializable transaction. Therefore the block should be coded to handle this. In particular all sequential access should be to result sets defined inside the transaction. Any counters should be zeroed at the top of the block and not outside the block.

A DO TRANS is a special form of WHILE TRANS that will not attempt to restart after a deadlock or failover is detected though it will rollback the database. This should only be used if it is not possible to code a restartable transaction. The application must check the transaction status and handle this situation some other way.

A transaction takes place in a particular database identified by a connection handle. There is no support for distributed transactions across more than one database. If the connection handle is omitted then the current default database is used. Note that unless this default has been explicitly set in KI_ALLOC_CONNECT then an error will occur as the natural default database does not support transactions.

Nested transactions

It is permitted to nest the WHILE TRANS/END TRANS blocks but the database layer only supports one transaction at a time so the inner transactions are considered part of the outermost one. Any rollback will undo the entire transaction. In the event of the transaction being abandoned with a THROW ROLLBACK, control will resume after the END TRANS of the outermost transaction block with the status code receiver from the WHILE TRANS statement updated with the status code thrown.

A $COMPLIANCE level of at least 3 is required to use WHILE TRANS. It is not permitted to transfer control out of a transaction block with a RETURN, BREAK, CONTINUE or any form of GOTO.

Handling database rollback

An application can force a rollback using the THROW ROLLBACK statement. This will abandon the transaction rolling back any uncommitted work. If the transaction is not nested within an outer transaction then control resumes after the END TRANS with this transactions status code updated.

If transactions are nested then KCML will look for an outer transaction on the same connection and if there is one then it will unwind the return stack to switch back to that context. This will be repeated until it reaches the outermost transaction block. As subroutines are unwound they will have their FINALLY clauses executed allowing local cleanup. Control will resume with the END TRANS of the outermost transaction with the status code receiver for that transaction updated with the status from the THROW ROLLBACK. Inner transactions will not have their status code receivers updated.

Handling deadly embraces

If the KDB database detects a deadly embrace then it will rollback the whole transaction executing any FINALLY clauses on the stack and restart the outermost block. The rollback will release all the locks held as part of the transaction. Consequently transaction blocks must be written in such a way that they can be executed repeatedly. All counters should be initialized inside the block. FINALLY code executes after the rollback and after new transaction has been started.

Before restarting after a deadly embrace, KCML will delay for a short random period to avoid a race with the other process. If restarted a further time that delay will be increased up to a maximum which, if exceeded, will trigger a rollback and the transaction will be abandoned.

Deadlock recovery code can be tested by setting the _KDB_CONNECT_ATTR_TEST attribute on the database connection to force every nth lock to deadlock or programmatically with the THROW RETRY statement.

DO TRANS will not attempt to recover from a deadlock and will fail the transaction setting the connection status to _KE_DEADLOCK

Handling database failover

Some clustered databases such as Oracle RAC support failover allowing KCML to detect a database server crash and to switch transparently to another node on the cluster. See KI_CONNECT for more on how to configure this. If the process is executing a WHILE TRANS executing a transaction, the transaction will be replayed on the new database server by restarting the WHILE TRANS as it would for a deadly embrace.

DO TRANS will not attempt to recover from a database failover and will fail the transaction setting the connection status to _KE_DEADLOCK.

Example

LOCAL DIM eTranStatus AS KDB_ERROR_ENUM
LOCAL DIM eDbStatus AS KDB_ERROR_ENUM
...
WHILE TRANS eTranStatus, hConnection
	...
	eDbStatus = 'WriteRow(...)
	IF (eDbStatus <> _KE_SUCCESS)
		// Duplicate key?
		THROW ROLLBACK eDbStatus
		// can't get here
	END IF
END TRANS
IF (eTranStatus <> _KE_SUCCESS)
	// transaction failed and was rolled back
	...
END IF

Compatibility

WHILE TRANS/END TRANS was introduced in KCML 6.20.

See also:

Exception Handling
THROW ROLLBACK
Database transaction states
Failover in database clusters