Transactions

Transactions

Top  Previous  Next

 

A transaction is a group of related database updates to be treated as a unit that must either happen in its entirety or not at all. From a programmer's point of view, the updates are enclosed between two QMBasic statements, BEGIN TRANSACTION and END TRANSACTION. All writes and deletes appearing during the transaction are cached and only take place when the program executes a COMMIT statement. The program can abort the transaction by executing a ROLLBACK statement which causes all updates to be discarded.

 

An alternative transaction syntax is available using the TRANSACTION START, TRANSACTION COMMIT and TRANSACTION ABORT statements. The two styles may be mixed in a single application.

 

Transactions apply the ACID criteria:

Atomicity. Updates applied within a transaction are treated as a single action. They are all applied at commit or all discarded at roll-back.

Consistency. The database remains in a consistent state. Each committed transaction moves the database forwards from one valid state to another.

Isolation. At no point are partially committed transactions visible to other processes so long as they follow the standard locking rules.

Durability. The updates applied by a transaction are permanent unless undone by a further transaction. QM also support non-durable transactions where updates from a child transaction started inside another transaction are inherited by the parent transaction at commit and will be rolled back if the parent transaction does not also commit.

 

Even though updates made within a transaction are not written until the transaction is committed, attempting to read a record that has been updated within the same transaction or a parent transaction will return the updated data from the transaction cache. Creation of a select list inside a transaction will reflect the state of the file as though the transaction had been committed. Note that the alternate key indices are only updated when the transaction commits. The query processor will ignore alternate key indices in a transaction if the transaction has applied updates to the file referenced by the query command.

 

Transactions affect the operation of file and record locks. Outside a transaction, locks are released when a write or delete occurs. Transactional database updates are deferred until the transaction is committed and all locks acquired inside the transaction are held until the commit or roll-back. Because of this change to the locking mechanism, converting an application to use transactions is usually rather more complex than simply inserting the transaction control statements into existing programs. The retention of locks can give rise to deadlock situations.

 

There are some restrictions on what a program may do inside a transaction. In general, QM tries not to enforce prohibitive rules but leaves the application designer to consider the potential impact of the operations embedded inside the transaction. Note carefully, that developers should try to avoid user interactions (e.g. INPUT statements) inside a transaction as these can result in locks being held for long periods if the user does not respond quickly.

 

Testing the value of the STATUS() function immediately after the END TRANSACTION statement will return 0 if the transaction committed successfully, ER_COMMIT_FAIL if an error occurred during the commit action, or ER_ROLLBACK if the transaction was rolled back.

 

Sometimes an application may open a file for which updates are not to be treated as part of any transaction within which they occur. The OPEN and OPENPATH statements both have an option to open the file in a non-transactional manner. Alternatively, the presence of the N option in field 6 of the F-type VOC entry will cause a non-transactional open.

 

 

Example

 

BEGIN TRANSACTION

  READU CUST1.REC FROM CUST.F, CUST1.ID ELSE ROLLBACK

  CUST1.REC<C.BALANCE> -= TRANSFER.VALUE

  WRITE CUST1.REC TO CUST.F, CUST1.ID

 

  READU CUST2.REC FROM CUST.F, CUST2.ID ELSE ROLLBACK

  CUST2.REC<C.BALANCE> += TRANSFER.VALUE

  WRITE CUST2.REC TO CUST.F, CUST2.ID

  COMMIT

END TRANSACTION

 

The above program fragment transfers money between two customer accounts. The updates are only committed if the entire transaction is successful.