Concurrency
Concurrency is the ability of multiple users to access and modify data simultaneously and is of vital importance in any database environment. The database should handle multiple users accessing the database simultaneously in a correct way. The means to achieve this are transactions and locking.
Transactions
A transaction is a unit of work that is done as a single atomic operation. Transactions either succeed or fail as a whole. For example, consider a transaction that transfers money from one bank account to another. This involves withdrawing money from one account and depositing the money in the other account. It is important that both actions occur; it is unacceptable for one action to succeed and the other to fail. A database that supports transactions is able to guarantee that either both steps succeed or both steps fail.
The transaction support of a database system must have the ACID (Atomicity, Consistency, Isolation, and Durability) properties:
- Atomicity: A transaction must be an atomic unit of work.
- Consistency: When completed, a transaction should leave the database in a consistent state. Internal data structures such as indexes must be correct at the end of the transaction.
- Isolation: Modifications made by the current transaction must be isolated from modifications made by other concurrent transactions. A transaction should not be able to “see” intermediate results of another transaction.
- Durability: After a transaction has completed, its effects are permanent.
Transactions are started by a program, which then manipulates data in the database. Eventually, the transaction is either committed or rolled back. A commit will make all changes made by the transaction permanent, while a rollback will remove all changes made by the transaction as if the transaction was never started.
Most databases support all of the transaction properties. The Isolation property usually can be set up to be less restrictive. So-called Isolation Levels have been defined. The lowest level will make intermediate results available to other transactions, while the highest level will not. There is an inverse relation between Isolation Level and concurrency; the higher the Isolation Level, the lower the concurrency.
Locking
A lock guarantees exclusive access to the object on which it is applied. All databases that support multi-user access use some form of locking. Locking is used to enforce the Isolation transaction property. Although there are a number of variants at the implementation level, databases generally support the following lock granularities:
- Row locking
- Page locking
- Table locking
Usually, a database supports one or more of the lock granularities or uses a mechanism that will automatically select the best locking granularity for the task at hand. There is an inverse relation between lock granularity and concurrency; the smaller the granularity, the greater the concurrency. For example, if we have a table with 10 rows made up of 5 pages, each page having 2 rows: - 10 users can simultaneously lock a row. - 5 users can simultaneously lock a page. - Only one user can lock the table.
Deadlocks
The use of locks can introduce a side effect known as a deadlock. A deadlock is a situation where two or more transactions are waiting on each other’s locks to be released. For instance, Transaction A waits for Transaction B, and Transaction B waits for Transaction A. In a deadlock situation, transactions wait indefinitely, which is unacceptable. The database system must either prevent deadlocks from happening or detect deadlocks when they occur and resolve that situation.
The scenario below illustrates a sample deadlock situation with two transactions, using record locking. In this case, the lock must be part of the find operation. One can only lock a record after it has been found. To indicate this special type of find, we use XFind.
| TRANSACTION A | TIME | TRANSACTION B |
|---|---|---|
| XFind R1 | T1 | |
| T2 | XFind R2 | |
| XFind R2 | T3 | Wait |
| Wait | T4 | XFind R1 |
| Wait | Wait |
If the above situation occurs, the two transactions are waiting for each other indefinitely. This is an unacceptable situation, so the DBMS either needs to prevent deadlocks from happening or detect when they happen and resolve that situation.
There are deadlock avoidance strategies. The native DataFlex database uses one of these strategies. Most database systems, however, use a deadlock detection and resolving strategy. Deadlocks can be resolved by choosing a victim transaction from the list of deadlocked transactions. The victim is then stopped by issuing a rollback, which will free the locks claimed by the victim transaction, allowing other transactions to obtain those locks and continue.
It is important to understand that deadlocks are not a programming error. They are a situation that can occur in certain environments. It is the application programmer’s responsibility to handle deadlocks properly. Application programmers typically handle deadlock errors in two ways: 1. Report an error to the user and let the user re-enter the information. 2. Automatically retry the transaction a designated number of times.
Again, deadlocks are not a programming error; they are a condition that can occur in certain environments. The likelihood of deadlocks occurring depends on the application and database back-end, but usually, this chance is very small. Nevertheless, application programmers should handle deadlocks, regardless of how small the chances are for them to occur.
Handling Deadlocks Automatically
DataFlex has a built-in mechanism to automatically handle deadlocks or lock timeout situations. If the runtime detects the DFERR_LOCK_TIMEOUT (4106 "Lock time-out") error, it will roll back the current transaction and retry it by jumping to the [Lock](../LanguageReference/Lock_Command.md), [Reread](../LanguageReference/Reread_Command.md), or [Begin_Transaction](../LanguageReference/Begin_transaction_Command.md) command that started the transaction. The retry can be attempted multiple times; the number of attempts can be set using the [Set_Transaction_Retry](../LanguageReference/Set_transaction_retry_Command.md) command and can be queried using the [Get_Transaction_Retry](../LanguageReference/Get_transaction_retry_Command.md) command. A single retry consists of a two-step process: first, the function Verify_Retry is called; if the return value is 0 (zero), the transaction is retried; if the return value is non-zero, the transaction will be aborted. The function will be called by the object that initiated the transaction, i.e., the object with the Lock, Reread, or Begin_Transaction command.
If a program relies on changing program variables in transactions, the translation can be switched off using the [Use_DF_LockError](MSSQLDRV.INT.md#Use_DF_LockError) driver configuration keyword. Alternatively, the program can be adjusted to use the automatic retry mechanism. A Verify_Retry function should be created that resets the program variables.
The SQL Server and DB2 drivers will automatically translate deadlock and timeout errors. The ODBC driver allows a way to define which error numbers or SQL States correspond to deadlock and/or lock timeout errors. This can be set up in database-specific configuration files.