Resolving Conflicts among Concurrent Transactions

Several kinds of conflict can occur between transactions that are contending for access to the same data. The following three are typical cases:

One transaction has locked an object that another transaction needs and is in a wait state.
Two transactions each need an object the other transaction has locked in the same DBEnvironment and are both in a wait state.
Two transactions each need an object the other transaction has locked in another DBEnvironment and are both in a wait state.

The first conflict results in a lock wait, which simply means that the second transaction must wait until the first transaction releases the lock. The second conflict is known as conventional deadlock, which is automatically resolved by ALLBASE/SQL. The third conflict is an undetectable deadlock, which cannot be automatically resolved.

Lock Waits

When a transaction is waiting for a lock, the application pauses until the lock can be acquired. When a transaction is in a wait state, some other transaction already has a lock on the row, page, or table that is needed. When the transaction that is holding a lock on the requested row, page, or table releases its lock through a COMMIT WORK or ROLLBACK WORK statement, the waiting transaction can then acquire a new lock and proceed.

The amount of time an application waits for a lock depends on the timeout value. A timeout value is the amount of time a user waits if a requested database resource is unavailable. If an application times out while waiting for a lock, an error occurs and the transaction is rolled back. See the SET USER TIMEOUT statement in the "SQL Statements" chapter of this manual for more information.

The larger the number of lock waits, the slower the performance of the DBEnvironment as a whole. You can observe the lock waits at any given moment in the DBEnvironment by issuing the following query:

isql=> SELECT * FROM SYSTEM.CALL WHERE STATUS = 'WAITING ON LOCK';

The use of isolation levels less severe than Repeatable Read can improve concurrency by reducing lock waits. For example, reporting applications that do not depend on perfect consistency can use the Read Uncommitted level, while applications that scan an entire table to update just a few rows can use Read Committed with REFETCH or Read Uncommitted with REFETCH for the greatest concurrency. Applications that intend to update a larger number of rows can use Cursor Stability.

You can set the amount of time a transaction will wait for a lock by using the SET USER TIMEOUT statement, or by setting a default timeout value using the ALTDBE command in SQLUtil. If no timeout value is set as a default, the transaction will wait until the resource is released. Consult your database administrator about default timeout values.

Deadlocks

The second kind of conflict is known as a deadlock between two transactions. This happens when two transactions both need data or indexes that the other already has locked. Deadlocks involving system catalog pages are also possible. ALLBASE/SQL detects and resolves deadlocks when they occur. If different priority numbers are assigned to the transactions in the BEGIN WORK statement, the transaction with the larger priority number is rolled back. If no priorities are assigned, the more recent transaction is rolled back.

ALLBASE/SQL resolves deadlocks between two transactions at a time. Therefore, if more than two transactions are deadlocked at one time, the transaction aborted may not be the transaction with the largest priority number or the newest transaction among all transactions deadlocked.

By default, the action taken to resolve a deadlock is to roll back one of the transactions. However, it is also possible to set the deadlock action for a transaction to roll back the current command instead of the entire transaction by using the SET SESSION or SET TRANSACTION statements.

Table Type and Deadlock

Specific table types are likely to incur particular types of deadlock. Two transactions can deadlock on the same PUBLIC or PUBLICROW table when the transactions attempt to access the same page or row. The larger the table, the less likely it is that two transactions will need to access the same page or row, so deadlock is reduced. If the table is small, there is less chance of deadlock when it is defined PUBLICROW rather than PUBLIC.

The following scenario illustrates the development of a deadlock involving two fairly large PUBLIC tables with indexes in the sample DBEnvironment PartsDBE. Assume that both transactions are at the RR isolation level.

Transaction 1: UPDATE PurchDB.Parts SET            Obtains IX lock on table,
               SalesPrice = 1.2*SalesPrice;        X on each page.
Transaction 2: SELECT * FROM PurchDB.SupplyPrice;  Obtains S lock on table.
Transaction 1: UPDATE PurchDB.SupplyPrice SET      Waits for IX on table
               UnitPrice = 1.2*UnitPrice;   
Transaction 2:  SELECT * FROM PurchDB.Parts;    Deadlock.

This sequence results in a deadlock which causes ALLBASE/SQL to choose a transaction to roll back. In the example, since no priorities are assigned, ALLBASE/SQL rolls back both of user 2's queries and displays an error message. User 1's second update then completes. Title not available shows the deadlock condition that results from the previous example.

Figure 5-10 Deadlock

The use of PRIVATE tables ensures there will be no deadlock on the same table, because access to the table is serialized. However, deadlock across two or more tables is common with PUBLICREAD and PRIVATE tables that are accessed by different transactions in different order. The following example shows a deadlock involving a PRIVATE table:

   Transaction 1: SELECT * FROM TABLEA;       Obtains X lock on table.
   Transaction 2: SELECT * FROM TABLEB;       Obtains X lock on table.
   Transaction 1: SELECT * FROM TABLEB;       Waits for X on table.
   Transaction 2:   SELECT * FROM TABLEA;   Deadlock.

A common deadlock scenario for PUBLICREAD tables is to do a SELECT, thus obtaining a table level share lock, and then an UPDATE, which must upgrade the lock to exclusive:

   Transaction 1: SELECT * FROM TABLEA;       Obtains S lock on table.
   Transaction 2: SELECT * FROM TABLEA;       Obtains S lock on table.
   Transaction 1: UPDATE TABLEA;              Waits to upgrade to X on table.
   Transaction 2:   UPDATE TABLEA;          Deadlock.

The need to upgrade frequently results in deadlock.

Table Size and Deadlock

The size of a table is another factor affecting its susceptibility to deadlock. If the table is small, it is highly probable that several users may need the same pages, so deadlocks may be relatively frequent when page level locking is used. The probability of collision is highest when the table is small and its rows are also small, with many stored on one page. If the table is large, it is relatively unlikely that multiple users will want the same pages at the same time, so page level locking should cause relatively few deadlocks.

Avoiding Deadlock

The tradeoff between deadlock and throughput is one of the central issues in concurrency control. It is important to minimize the number of deadlocks while permitting the greatest possible concurrent access to database tables.

Avoiding Deadlock by Using the Same Order of Execution

To avoid deadlock among multiple tables, be sure to have all transactions access them in the same order. This can often be done by modifying programs to use the same algorithms to access data in the same order (for example, first update table 1, then table 2), rather than accessing data in random order. This strategy cannot always be followed, but when it can be used, processes will wait their turn to use a particular data object rather than deadlocking.

Avoiding Deadlock by Reading for Update

You can avoid deadlocks that stem from upgrading locks by designing transactions that use SIX locks, which have the effect of serializing updates on a table while permitting concurrent reads. To employ SIX locks, read the table with a cursor that includes a FOR UPDATE clause. You can also obtain SIX locks by using the LOCK TABLE statement, specifying the SHARE UPDATE option.

Avoiding Deadlock by Using the LOCK TABLE Statement

Locking at the table level should reduce deadlocks when all or most pages in a PUBLIC table (rows in a PUBLICROW table) are accessed in a query. Locking the table in share update mode obtains SIX locks on the table and its pages (or rows) when you are reading data with the intention of updating some data.

Avoiding Deadlock on Single Tables by Using PUBLICREAD and PRIVATE

The use of PUBLICREAD and PRIVATE tables decreases the chance of encountering a deadlock by forcing serialization of updates within a single table, that is, requiring one update transaction to be committed before another can obtain any locks on the same table. Obviously, this reduces concurrency during update operations. You can also use the LOCK TABLE statement for transactions on PUBLICREAD tables that read data prior to updating it.

Avoiding Deadlock by Using the KEEP CURSOR Option

In applications that declare cursors explicitly, you can use the KEEP CURSOR option in the OPEN statement to release exclusive locks as quickly as possible. When you use the KEEP CURSOR option for a cursor you explicitly open in a program, you can use the COMMIT WORK statement to end the transaction and release locks without losing the cursor's position. Furthermore, you can either retain or release the locks on the page or row pointed to by the current cursor position. When you use the KEEP CURSOR option, your transaction holds individual exclusive locks only for a very short time. Thus, the chance of deadlock is reduced, and throughput is improved dramatically. For details, refer to the chapter entitled "Processing with Cursors" in the ALLBASE/SQL application programming guide for the language of your choice.

Undetectable Deadlock

Applications that connect to multiple DBEnvironments may encounter deadlocks that cannot be detected and resolved by ALLBASE/SQL. An example follows:

Transaction 1:   SET CONNECTION 'DBE1';    
                 UPDATE TABLEA SET COL1 = 5;   Obtains X table lock.
Transaction 2:   SET CONNECTION 'DBE2';   
                 UPDATE TABLEB SET COL1 = 7;   Obtains X table lock.
Transaction 1:   SET CONNECTION 'DBE2'; 
                 SELECT * FROM TABLEB;         Waits.
Transaction 2:   SET CONNECTION 'DBE1';  
                   SELECT * FROM TABLEA;     Waits--Undetectable Deadlock.

This kind of deadlock is called undetectable because ALLBASE/SQL can only detect a deadlock within a single DBEnvironment session. It is your responsibility to coordinate your system's use of distributed transactions so as to prevent undetectable deadlock. You can enable ALLBASE/SQL to identify and roll back what probably are undetectable deadlocks by setting appropriate user timeout values for each DBEnvironment connection. For more information refer to "Using Multiple Connections and Transactions with Timeouts" in Chapter 2 “Using ALLBASE/SQL”

A similar condition known as an undetectable wait state can also arise when you are using multi-connect functionality. An undetectable wait occurs when you connect more than once to the same DBEnvironment from the same application in multi-transaction mode and attempt to obtain resources held by your other connection. For example:

   CONNECT TO 'DBE1' AS 'CONNECT1';a  
   CONNECT TO 'DBE1' AS 'CONNECT2';  
   SET CONNECTION 'CONNECT1';    
   UPDATE TABLEA SET COL1 = 5;                  Obtains X table lock.
   SET CONNECTION 'CONNECT2';    
   UPDATE TABLEA SET COL1 = 7;                Waits--Undetectable wait.

In this instance, you are waiting on your own resources. To avoid situations like this, be sure to set user timeout values when you use multi-connect functionality.