<< Click to Display Table of Contents >> Navigation:  Application development > Transactions > Degree of transaction isolation

The degree of isolation provided by transactions on standard databases is Degree 0. This means that a transaction does not overwrite another transaction’s dirty data.

Because only Degree 0 isolation is supported, transactions on standard databases are subject to the following limitations:

•Possible lost updates Two transactions could perform reads without locking records, that is, using dbiNOLOCK protocol. If these two transactions post their updates independently, the final result set might include only one transaction’s changes, losing updates of the other transaction.

•Transaction not isolated from dirty reads A transaction T1 could read a record previously updated by another transaction T2 and make further modifications to that record. The record read by T1 might be inconsistent, because it is not the final update produced by T2. Hence the read of transaction T1 was a dirty read.

•Unrepeatable reads not prevented A transaction T1 reads a record twice, once before transaction T2 updates it and once after committed transaction T2 has updated it. The two read operations return different values for the record and the first read is not repeatable.

By using the appropriate locking mechanism during the updates, the clients can provide a higher degree of transaction isolation. For example, lost updates can be prevented if a transaction always gets a read lock on a record it is about to modify. No user-requested locks are promoted, that is, if a user requests to read a record by using DbiGetRecord with dbiNOLOCK protocol, that record is not locked and that read operation might be a dirty read. However, in the case of inserts and modifications, records are locked with dbiWRITELOCK and locks are held until that transaction ends.

The function DbiBeginTran supports several transaction isolation levels:

•xilDIRTYREAD (Uncommitted changes read),

•xilREADCOMMITTED (Committed changes, no phantoms), and

•xilREPEATABLEREAD (full read repeatability).

For SQL tables, appropriate transaction isolation levels can be requested depending on the destination SQL server capabilities. The xilREADCOMMITTED isolation level precludes lost updates and dirty reads. The xilREPEATABLEREAD isolation level prevents unrepeatable reads.

Limitation: Because the transaction feature for local (standard) database tables supports Degree 0 isolation, only the xilDIRTYREAD option is accepted in DbiBeginTran. If a higher degree of isolation is requested, an error message is returned. For the same reason, xendCOMMITKEEP is not supported by DbiEndTran.

Because all updates are atomic, users will be informed about the lock conflicts immediately. No deadlock detection is performed. A deadlock occurs when each of two transactions waits for locks held by the other. If there are any lock conflicts between different transactions, an error message is returned to the clients. When a deadlock occurs, it is up to the clients to decide which transactions to rollback.