Optimistic transactions

A major problem with converting legacy KISAM databases to an SQL database is identifying transactions so that they can be enclosed in a WHILE TRANS block. This can be complicated if forms input occurs during an update or if the update code is not retryable after a deadlock. Without explicit transactions KCML will auto-commit each statement with a significant overhead in start and stopping a transaction for the purpose. Furthermore locks taken with _KDB_LOCK will not persist as they are discarded with the auto-committed transaction.

Enabling optimistic transaction mode on a connection can improve performance by grouping multiple update statements into an ad hoc 'optimistic' transaction. This allows locks to persist without the code being changed to use long term locks. KCML will start an optimistic transaction on the first operation that needs one such as a KI_WRITE or a KI_READ_RANDOM with a lock. It will stay in the transaction until an event occurs that requires a commit. These events might be

the program blocks on form input
a client side $DECLARE function is called
a deadlock is detected
DDL is executed
a table open on the connection is closed with KI_CLOSE
one of these statements is executed:
- LOAD
- $RELEASE
- $BREAK
- $END
- PANIC

The event will trigger a commit of the current database ending the optimistic transaction. Any outstanding locks are converted into long term locks to persist them. If a WHILE TRANS for the connection is executed it will be ignored.

You can force an optimistic transaction to commit with $BREAK 0.

If a deadlock is detected in this mode KCML will commit the current transaction which should allow the other party to complete its transaction. The failed lock will then be repeated.

Optimistic transactions are enabled for a connection by setting the _KDB_CONN_ATTR_OPT_TRANS attribute to TRUE with connection attributes on the connection used for the legacy code.

Compatibility

Optimistic transaction mode was introduced in 6.69 for SQL databases only.