Everyone knows what a deadlock is: a situation in which two or more competing processes are waiting for the other to finish, and thus neither ever does. The purpose of this post is to help people understanding the deadlock a little better with a view to enable them to fix the problem when they find one.
Assume that there are two processes running, A & B and that they require a (shared) file and a printer to do their work. Process A locks up the printer, and Process B locks up the file for its own use. Now, none of the processes can complete because they do not have all the resources needed for their completion, and neither will they release the resource they have: they will keep on waiting for the second resource.
Let us create a deadlock now, using Oracle database and SQL Plus client.
We opened two sessions, and executed “set autocommit off” as the first statement.
Now in the first session we executed:
UPDATE ps_voucher SET grp_ap_id='A' WHERE voucher_id='00692096' AND invoice_dt='2-JAN-2002';
second session:
UPDATE ps_voucher SET grp_ap_id='A' WHERE voucher_id='00692096' AND invoice_dt='13-MAR-2007';
back to the first:
UPDATE ps_voucher SET address_seq_num=2 WHERE voucher_id='00692096';
and then the second:
UPDATE ps_voucher SET address_seq_num=2 WHERE voucher_id='00692096'
BAM! Deadlock. See screenshots:
What went wrong? There existed two vouchers in the system, with the same VOUCHER_ID but with different INVOICE_DTs (invoice dates). Each process first locked up one of those vouchers, and then – as the second UPDATE – tried to update both. (On the database side, a process gets a lock on a specific row when it UPDATEs that row, and the lock is released when the process COMMITs or ROLLBACKs.)
Yes, the programmer could have been smarter and written better code: if he had put the INVOICE_DT clause in the second statement also we would have been fine. However, in practice, with huge systems having tons of code – programmer will sometimes make mistakes. Even if they do not, deadlocks will occur: not all deadlocks are caused by SQL issues.
From a system design perspective, what can be done to prevent deadlocks? One way is for the execution of each process to have a unique ID – let’s call it process instance (PI). So if a process ABC is run once, it will have a PI of 1222 and when it’s run next it will have a PI of 1224. If, after this process PQR is run, it will have a PI of 1223. Before changing any transactions, the process can update it own PI on the transactions that qualify:
UPDATE ps_voucher
SET pi=1223
WHERE <process specific selection criteria>
AND pi=0;
COMMIT;
The commit here is important – only then will other processes be able to see the ‘locking’.
Thereafter the normal processing SQLs can be changed as below:
UPDATE ps_voucher
SET grp_ap_id='1'
WHERE <process specific criteria>
AND pi=1223;
At the end, set the transactions back to ‘open for processing’ by setting PI to zero:
UPDATE ps_voucher
SET pi=0
WHERE pi=1223;
If there are other ways to achieve this, please let me know by posting comments.
The DBA is usually able to specify the SQL queries involved in a deadlock. Many times one process is UPDATing the rows that the other is DELETing.
