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.
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, the program then manipulates data in the database. Eventually the transaction is committed, or rolled back. A commit will make all changes made by the transaction permanent. A rollback will remove all changes made by the transaction just like 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, the highest level will not. There is an inverse relation between Isolation Level and concurrency. The higher the Isolation Level, the lower the concurrency.
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 implementation level basically databases support the following lock granularities: row locking, page locking and 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 bigger the concurrency. If for example 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 and only one user can lock the table.
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. Transaction A waits for B and B waits for A. In a deadlock situation transactions wait indefinitely, this is an unacceptable situation. The database system must either prevent deadlocks from happening or detect deadlocks when they happened and resolve that situation.
The scenario below gives a sample of a deadlock situation with two transactions. The sample uses 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 |
|
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. Deadlock can be resolved by choosing a victim transaction in the list of deadlocked transactions. The victim is then stopped by issuing a rollback. This will free the locks claimed by the victim transaction thus allowing other transactions to obtain those locks and continue.
It is important to understand that deadlocks are not a programming error. It is a situation that can occur in certain environments. It is the application programmer’s responsibility to handle deadlocks in a proper way. Application programmers typically handle deadlock errors in two ways, one is to report an error to the user and let the user re-enter the information. The other is to automatically retry the transaction a designated number of times.
Again, deadlock is not a programming error. It is a condition that can occur in a certain environment. It depends on the application and database back-end what the chance is for deadlock to occur. Usually this chance is very small. Nevertheless, application programmers should handle deadlock no matter how small the chances are for it ever occurring.
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/Reread/Begin_Transaction command that started the transaction. The retry can be attempted multiple times; the number of times can be set using the Set_Transaction_Retry command and it can be queried using the Get_Transaction_Retry 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/Begin_Transaction command.
If a program does rely on changing program variables in transactions, the translation can be switched off using the 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 what error numbers or SQL States correspond to deadlock and/or lock timeout errors. This can be setup in database-specific configuration files.