Deadlocks are an important issue and can happen in every database I am aware of. Basically, a deadlock will happen if two transactions have to wait on each other.
In this section, you will see how this can happen. Let us suppose we have a table containing two rows:
CREATE TABLE t_deadlock (id int);
INSERT INTO t_deadlock VALUES (1), (2);
The next listing shows what can happen:
|
Transaction 1 |
Transaction 2 |
|
BEGIN; |
BEGIN; |
|
UPDATE t_deadlock SET id = id * 10 WHERE id = 1; |
UPDATE t_deadlock SET id = id * 10 WHERE id = 2; |
|
UPDATE t_deadlock SET id = id * 10 WHERE id = 2; |
|
|
Waiting on transaction 2 |
UPDATE t_deadlock SET id = id * 10 WHERE id = 1; |
|
Waiting on transaction 2 |
Waiting on transaction 1 |
|
Deadlock will be resolved after one second (deadlock_timeout) |
|
|
COMMIT; |
ROLLBACK; |
As soon as the deadlock is detected, the following error message...
