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.
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:
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, the transaction tells the database manager to lock a row because it intents to update the data in the 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. 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.
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