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 Window Workstation. SQL is used to access data in a SQL Server database.

Transactions and Locking

SQL Server fully supports the ACID transaction properties. Next to the normal rollback facilities SQL Server also supports so called roll forward functionality. SQL Server supports all Isolation Levels. The driver will always use the Read Uncommitted isolation level.  Transactions in SQL Server are logged. The SQL Server database system can recover from system failures.

The lock granularity used by SQL Server is row (or record) locking. It is possible that the database manager replaces multiple row locks with a single page or table lock, this is called lock escalation. Lock escalation is implemented in several database platforms. If a lot of records on a page or table are locked the overhead of administrating the locks gets so big that it is cheaper and/or faster, to escalate the lock to lock an entire page or table. In SQL Server three different types of lock are possible:

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.  The statement uses a so-called update clause. Update locks are placed while finding rows in a table. Once the row is actually updated, the lock will transform into an exclusive lock.

The moment at which a lock is applied is different 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 will periodically scan sessions waiting on a lock. If in such a scan a session is detected that was waiting in the previous periodic scan, a deadlock detection search is started. If a deadlock is detected, the least expensive transaction to undo is chosen as the deadlock victim. This victim transaction is rolled back. When the rollback occurs, the locks are released, thus offering other transactions the possibility to proceed. The rolled back transaction (application) will get notified of the event by receiving the deadlock notification error. It is possible for a transaction to “volunteer” as victim by setting the deadlock priority. The DataFlex SQL Server Driver does not use this attribute. Next to deadlock detection, SQL Server also supports a configurable lock timeout. If a lock timeout occurs, the transaction trying to acquire the lock will be rolled back and is notified by receiving the timeout notification error. The DataFlex SQL Server Driver will translate both these errors to DFERR_LOCK_TIMEOUT (4106), thus enabling the automatic retry mechanism.

If the translation of deadlock and lock timeout errors is switched off, the DataFlex SQL Server Driver will report both these errors in the same way, since 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

    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) ;

        SendInformUser LastErr

End_Procedure // Retry9TimesWhenDeadlockOrTimeout

 

See Also

Transactions