|
|
|
Deadlock is a different condition than waiting for a lock. It occurs when one transaction is waiting for a lock held by another transaction at the same time that the other transaction is waiting for a lock held by the first. Both transactions block each other from completing. One of the transactions must be aborted to break the deadlock and allow the other to proceed.
Deadlock should be avoided.
|
|
|
This example (where the set autocommit option is off) depicts a situation that produces deadlock.
User1 initiates a multiple query transaction to read all the data from the employee table and insert a record with the department name Sales into the DEPT table. Shortly after, User2 initiates a multiple query transaction to read all the data from the DEPT table and to insert a record with the employee name Bill into the EMP table.
Here is the sequence of operations:
select * from emp;
select * from dept;
insert into dept (dname) values 'Sales';
insert into emp (name) values 'Bill';
User1's transaction must wait for User2's transaction to release the shared lock on the department table, but this can never happen unless User2's transaction can finish. To finish, User2's transaction needs to obtain an exclusive lock on the employee table, which it cannot get until User1's transaction releases its shared lock on it.
Thus, both transactions are waiting for each other. Neither transaction can finish until the locking system checks on all transactions waiting for locks to make sure deadlock has not occurred.
When a deadlock is discovered, the locking system aborts one of the transactions, allowing the other transaction to continue. The user whose transaction was aborted receives an error.
All updates made by the transaction are backed out. For this reason, the deadlock error must be trapped and the transaction retried in an application program.
Deadlock does not occur frequently if transactions are concise and no lock escalation occurs (either page to table or shared lock to exclusive lock). A deadlock is always logged to the error log.
|
|
|
Because the locking system uses page-level locking, accumulating locks one by one, deadlock can occur even when single query transactions are being used. At least two transactions must be accessing the database, and at least one user must be modifying rows. Deadlock does not occur when only select statements are executing, because shared locks do not conflict with each other.
It is possible for deadlock to occur during a single query transaction when:
Lock escalation deadlock can be caused by any of the following:
|
|
|
Multiple transactions updating table data using different access paths can cause single query deadlocks.
Consider the following example in which the EMP table has an ISAM structure indexed on name and a hash secondary index on empno.
|
|
|
When multiple transactions are updating a table, and lock escalation occurs, they can deadlock. This escalation is probably caused by one of three situations:
If you are running into locking limits, either raise these limits or shorten the multiple query transactions.
If lock escalation deadlock is occurring frequently, consider using the set lockmode statement to force table-level locking on the table or to increase maxlocks.
To understand how lock escalation can produce deadlock, consider the following example in which two users are trying to insert into the same table that has many overflow pages:
User1 tries to insert a record, and because of the long overflow chain exclusively locks ten pages. Meanwhile, User2 also tries to insert a record and grants locks down another overflow chain.
During the processing of User1's query, the transaction reaches maxlocks pages and needs to escalate to an exclusive table-level lock; but, because User2 still holds an intent exclusive (IX) lock on the table, User1's request must wait.
User2's query also needs to lock more than maxlocks pages, so a request is made to escalate to an exclusive table-level lock. User2's request is also blocked, because User1 is holding an intent exclusive (IX) lock on the table.
Deadlock occurs in that neither user can proceed because each is blocking the other.
When many concurrent users are inserting into a small B-tree table, index splits are likely to occur and deadlock can occur because the locking level in the index must be escalated to exclusive.
|
|
|
Tables with excessive overflow pages can cause locking problems because all overflow pages must be searched. Each page is locked individually and locks are kept all the way down the overflow chain. Escalation to table-level locking while locking an overflow chain can cause deadlock in heavily concurrent environments, as well as slow down the query processing time. If you have a table with many unavoidable overflow pages (that is, they are still present after a remodify), use the set lockmode statement to do the following:
|
|
|
The following program sample checks for deadlock after each statement of a multiple query transaction. If deadlock occurs when a statement is issued, and that statement is the victim, the entire transaction containing the statement aborts and the application is sent back to the beginning of the transaction, where it is retried until it completes without deadlock.
This sample program is written in embedded SQL/Fortran:
exec sql include SQLCA;
exec sql whenever sqlerror goto 100;
exec sql whenever not found continue;
exec sql begin declare section;
integer*4 x;
exec sql end declare section;
x = 0;
exec sql commit;
10 continue;
exec sql select max(empno) into :x from emp;
exec sql insert into emp (empno) values (:x + 1);
exec sql commit;
goto 200;
100 if (sqlcode .eq. -4700) then goto 10
endif
200
.
.
In this example, if deadlock occurs, there is no need to issue the rollback statement, because the transaction has already been aborted.
If deadlock was not checked for and handled, and the select statement to retrieve the maximum employee number failed with a deadlock, the program flow continues and the next statement issued, the insert statement, is completed:
insert into emp (empno) values (:x + 1)
Because the select statement did not complete, this statement inserts the value "1," which probably is not the maximum employee number.
The default behavior in embedded SQL programs is to continue when an error occurs, and that errors are not printed by default. To handle an error, you need to specify the desired behavior in the whenever sqlerr statement or to check the sqlca.sqlcode manually after each SQL statement.
Ingres 4GL provides the while and endloop statements that perform the function of a goto statement and allow for checking and handling of deadlock. The following is an example of Ingres 4GL:
initialize(flag=integer2 not null,
err=integer2 not null) =
{
}
'Go' = {
flag := 1;
a: while 1=1 do
b: while flag=1 do
repeated update empmax
set maxempno=maxempno + 1;
inquire_ingres (err = errno);
if err = 49900 then
endloop b; /* jump to endwhile of loop b */
endif;
repeated insert into emp (empno)
select maxempno from empmax;
inquire ingres (err = errorno);
if err = 49900 then
endloop b; /* jump to endwhile of loop b */
endif;
flag := 0; /*resets flag if MST successful */
endwhile; /* end of loop b */
if flag = 0 then
commmit
endloop a; /* jump to endwhile of loop a */
endif;
endwhile; /* end of loop a */
}