Deadlock Example
For the following example, SET AUTOCOMMIT is off.
User1 initiates a multiple query transaction to read all the data from the EMP table and insert a record with the department name "Sales" into the DEPT table. User2 initiates a multiple query transaction to read all the data from the DEPT table and to insert a record with the employee name of "Bill" into the EMP table.
Here is the sequence of operations:
1. User1 issues the statement:
select * from emp;
2. User1’s transaction requests a shared lock on the EMP table, and execution of the SELECT statement begins.
3. User2 issues the statement:
select * from dept;
4. User2’s transaction requests a shared lock on the DEPT table, and execution of the SELECT statement begins.
5. User1 inserts into DEPT:
insert into dept (dname) values 'Sales';
6. User2 inserts into EMP:
insert into emp (name) values 'Bill';
7. User1 requests an IX lock on the DEPT table but is blocked because there is a shared lock on the table.
8. User2 requests an IX lock on the EMP table but is blocked because there is a shared lock on the table.
9. User1’s transaction must wait for User2’s transaction to release the shared lock on the department table, but this cannot happen because User2’s transaction needs to obtain an exclusive lock on the employee table, which it cannot get as User1 is holding a shared lock on it.
Neither transaction can finish. The locking system periodically checks transactions waiting for locks to make sure deadlock has not occurred.
When a deadlock is detected, the locking system aborts one of the transactions, rolling back all changes made and issuing an error; this allows the other transaction to continue.
Programs should be coded to trap deadlock errors and take appropriate action.