What is a deadlock in SQL databases, how does the database handle it, and how do you prevent it?
What is a deadlock in SQL databases, how does the database handle it, and how do you prevent it?
A deadlock happens when two or more transactions each hold a lock the other needs, so none can proceed. The database detects the cycle and kills one transaction (the victim) so the rest can continue.
-- Session A -- Session B
BEGIN; BEGIN;
UPDATE accounts SET balance = balance - 10 UPDATE accounts SET balance = balance - 10
WHERE id = 1; WHERE id = 2;
UPDATE accounts SET balance = balance + 10 UPDATE accounts SET balance = balance + 10
WHERE id = 2; -- waits for B WHERE id = 1; -- waits for A: deadlock
| Engine | Error |
|---|---|
| PostgreSQL | 40P01 deadlock_detected (checked after deadlock_timeout, 1 s by default) |
| MySQL InnoDB | error 1213 Deadlock found when trying to get lock; try restarting transaction |
| SQL Server | error 1205, "chosen as the deadlock victim" |
| Oracle | ORA-00060: deadlock detected while waiting for resource |
The victim is rolled back; the application must retry the whole transaction.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.