Transactions and the DataFlex DB2 Driver
The DataFlex DB2 Driver allows DataFlex programs to access data in IBM DB2 Universal Databases. DB2 is a client-server relational database management system (RDBMS) that runs on a variety of operating systems. SQL is used to access data in a DB2 database.
Transactions and Locking
DB2 fully supports the ACID transaction properties. In addition to the normal rollback facilities, DB2 also supports roll-forward functionality. DB2 supports all isolation levels, but the driver will always use the Read Uncommitted isolation level. Transactions in DB2 are logged, allowing the DB2 database system to recover from system failures.
The lock granularity used by DB2 is row (or record) locking. It is possible that the database manager replaces multiple row locks with a single table lock, a process known as lock escalation. Lock escalation is implemented in several database platforms. If many records on a page or table are locked, the overhead of managing the locks becomes significant, making it cheaper and/or faster to escalate the lock to encompass an entire page or table. In DB2, three different types of locks are possible:
- Share: Other transactions are limited to read-only access.
- Update: Other transactions are limited to read-only access unless they declare an intention to update the row. This type of locking is known as intent locking, where the transaction informs the database manager to lock a row because it intends to update the data in that row.
- Exclusive: Other transactions cannot access the data at all, except those transactions that use an Isolation Level of Read Uncommitted.
DB2 will place exclusive locks on rows that are inserted, updated, or deleted. The DataFlex DB2 Driver uses the Update lock provided by DB2. This type of lock is applied by using a special form of the SQL Select statement that includes an update clause. Update locks are placed while finding rows in a table. Once the row is actually updated, the lock transforms into an exclusive lock.
The update clause is only supported when used in a select statement that does not include an order by clause. The only statement generated by the DataFlex DB2 Driver that does not use an order by clause is the one generated for a Find Equal operation. Therefore, the driver will only place Update locks on rows found through a Find Equal operation after the database has been locked. A Reread generates a Find Eq by Rowid after a lock, so rows found by a Reread will also be locked. All rows found in other ways will be locked when they are updated or deleted.
The moment at which a lock is applied differs from the moment the native DataFlex database acquires a lock. In native DataFlex, a lock is acquired when the lock command is issued. In DB2, locks are acquired as data is read in a locked state or as it gets updated or deleted.
When accessing DB2, ensure rows are locked, and always use either Reread or a Find Eq by RowId in a locked state. Data Sets and Data Dictionaries will use Reread when updating data. Programs using the DDO/DSO objects will therefore correctly lock all data that is updated. Procedural programs may require code changes for all locks to be applied.
A further difference between DataFlex and DB2 is the amount of data updated in an update operation. DataFlex reads and writes entire records, while DB2 can update one or more columns in a row. For example, if you want to update the name of a customer, DataFlex will read the entire record, modify the name, and write the entire record back to the data file. In contrast, DB2 will only write the modified customer name to the database.
The ability to update specified columns reduces the amount of procedural code that needs adjusting. If the program overwrites columns with values not based on previous values of columns in the row, there is no need to ensure the row is locked. However, if the program uses previous values of columns in the row to calculate new values, you must ensure the row is locked. In these cases, a Reread or Find Eq by Recnum must be added to the code if it is not already present. Below is an example of a procedure that does not need to be adjusted and one that must be adjusted.
Procedure NoNeedToAdjust
Clear SomeTable
Begin_Transaction
Repeat
Find Gt SomeTable By SomeIndex
If (Found) Begin
Move "SomeValue" To SomeTable.SomeColumn
SaveRecord SomeTable
End
Until (Not(Found))
End_Transaction
End_Procedure // NoNeedToAdjust
Procedure MustBeAdjusted
Clear SomeTable
Begin_Transaction
Repeat
Find Gt SomeTable By SomeIndex
If (Found) Begin
Move (SomeTable.SomeColumn * 1.16) To SomeTable.SomeColumn
SaveRecord SomeTable
End
Until (Not(Found))
End_Transaction
End_Procedure
The procedure that must be adjusted can be modified as follows:
Procedure MustBeAdjusted
Clear SomeTable
Begin_Transaction
Repeat
Find Gt SomeTable By SomeIndex
If (Found) Begin
Reread
Move (SomeTable.SomeColumn * 1.16) To SomeTable.SomeColumn
SaveRecord SomeTable
Unlock
End
Until (Not(Found))
End_Transaction
End_Procedure
Data Access has recommended the use of Reread when updating records for quite some time. It is expected that the number of programs needing adjustment in this area is limited. If you want both procedures to apply update locks on the records involved, you should adjust both procedures.
Deadlocks
Since DB2 uses row locking, deadlocks can occur. Deadlocks are detected by the DB2 Deadlock Detector. When a deadlock is detected, the deadlock detector arbitrarily selects one transaction to roll back. When the rollback occurs, the locks are released, allowing other transactions to proceed. The rolled-back transaction (application) will be notified of the event by receiving the deadlock notification error.
It is possible to set up the interval between two deadlock detection events. In addition to deadlock detection, DB2 also supports a configurable lock timeout. If a lock timeout occurs, the transaction trying to acquire the lock is rolled back and notified by receiving the timeout notification error. The DataFlex DB2 Driver will translate both these errors to DFERR_LOCK_TIMEOUT (4106), enabling the automatic retry mechanism.
If the translation of deadlock and lock timeout errors is switched off, the DataFlex DB2 Driver will report both errors in the same way, as they result in the same behavior for the transaction involved. The error is passed to DataFlex as DFMSSQLERR_DEADLOCK_OR_TIMEOUT (12303). When not using the lock error translation, it is the programmer’s responsibility to handle deadlock or lock timeouts. The DataFlex programmer can check for the error condition and act upon it. The code below shows a sample of retrying the transaction 9 times.
Procedure Retry9TimesWhenDeadlockOrTimeout
Boolean bAborted bRetry
Integer iRetryCount
Repeat
Send InitializeForTransaction
Begin_Transaction
Send DoSomeDB2Updates
End_Transaction
Get_attribute DF_TRANSACTION_ABORT To bAborted
If (bAborted And (LastErr = DB2ERR_DEADLOCK_OR_TIMEOUT)) ;
Move (True) to bRetry
Else ;
Move (False) to bRetry
Increment iRetryCount
Until ((Not(bRetry) Or (iRetryCount > 10)))
If (bAborted) ;
Send InformUser LastErr
End_Procedure