Transactions and the DataFlex SQL Server Driver

The DataFlex SQL Server Driver allows DataFlex programs to access data in Microsoft SQL Server databases. SQL Server is a high-performance, Client/Server relational database management system (RDBMS). SQL Server runs on Microsoft Windows Server-based networks and can be installed as a desktop database system on a Windows Workstation. SQL is used to access data in a SQL Server database.

Transactions and Locking

SQL Server fully supports the ACID transaction properties. In addition to the normal rollback facilities, SQL Server also supports roll forward functionality. SQL Server supports all Isolation Levels, but the driver will always use the Read Uncommitted isolation level. Transactions in SQL Server are logged, allowing the SQL Server database system to recover from system failures.

The lock granularity used by SQL Server is row (or record) locking. It is possible for the database manager to replace multiple row locks with a single page or table lock; this is called 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 can become significant, making it cheaper and/or faster to escalate the lock to encompass an entire page or table. In SQL Server, 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 provided they do not 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.

SQL Server will place exclusive locks on rows that are inserted, updated, or deleted. The DataFlex SQL Server Driver uses the Update lock provided by SQL Server. This type of lock is applied by setting an attribute of the cursor used for the statement, which utilizes 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 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 SQL Server, locks are acquired as data is read in a locked state.

Deadlocks

Since SQL Server uses row locking, deadlocks can occur. SQL Server periodically scans sessions waiting on a lock. If a session is detected that was waiting in the previous periodic scan, a deadlock detection search is initiated. If a deadlock is detected, the least expensive transaction to undo is chosen as the deadlock victim, which is then rolled back. When the rollback occurs, the locks are released, allowing other transactions to proceed. The rolled-back transaction (application) receives a deadlock notification error.

It is possible for a transaction to "volunteer" as a victim by setting the deadlock priority; however, the DataFlex SQL Server Driver does not use this attribute. In addition to deadlock detection, SQL Server supports a configurable lock timeout. If a lock timeout occurs, the transaction trying to acquire the lock will be rolled back and notified by receiving the timeout notification error. The DataFlex SQL Server Driver translates both these errors to DFERR_LOCK_TIMEOUT (4106), enabling the automatic retry mechanism.

If the translation of deadlock and lock timeout errors is disabled, the DataFlex SQL Server 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 deadlocks 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
    Integer bAborted
    Integer bRetry
    Integer iRetryCount
    Repeat
        Send InitializeForTransaction
        Begin_Transaction
        Send DoSomeSQLServerUpdates
        End_Transaction
        Get_Attribute DF_TRANSACTION_ABORT To bAborted
        If (bAborted And (LastErr = DFMSSQL2ERR_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 // Retry9TimesWhenDeadlockOrTimeout

See Also

• Transactions