Print service provided by iDogiCat: http://www.idogicat.com/
home logo





Home > IT > Database > Database Deadlock

Database Deadlock

How can deadlocks occur

Database deadlocks usually occur when there are circular lock sequences. For example, when one transaction locks resource A and tries to lock resource B, while at the same time another transaction locks resource B and tries to lock resource A, then the two transactions will wait for each other for ever. This can also happen with more than two transactions, such as T1 needs to lock A & B, T2 needs B & C, T3 needs C & A.

Deadlock can happen on different levels, such as table level locks and row level locks.

Example #1:

user1:

select * from table1; -- shared lock on table1

user2:

select * from table2; -- shared lock on table2

user1:

insert into table2 values (...); -- requesting IX lock on table2

user2:

insert into table1 values (...); -- requesting IX lock on table1

Example #2:

account transfers:

user1: (transfer $10 from account #1 to #2)

update account set balance = balance - 10 where id = 1; -- lock row 1

user2: (transfer $100 from account #2 to #1)

update account set balance = balance - 100 where id = 2; -- lock row 2 update account set balance = balance + 100 where id = 1; -- trying to lock row 1

user1: (continue the previous operation)

update account set balance = balance + 10 where id = 2; -- trying to lock row 2

Other situations:

Different access paths can also result in a deadlock: user1 accesses a table via index, and locks 4th page; user2 accesses the same table directly and locks 3rd page; user1 needs to lock 3rd page and user2 needs to lock 4th page.

Lock escalation can also cause a deadlock: https://docs.actian.com/ingres/10s/index.html#page/DatabaseAdmin%2FLock_Escalation_as_a_Source_of_Deadlock.htm%23

How to avoid deadlocks

  • make sure that all applications using a database acquire locks on multiple objects in a consistent order
  • try to hold locks for as short a period as possible. for example, inside a transaction, don't wait for user to input, instead, collect user's input first then enter transaction.