Deadlocks are an important issue and can happen in every database. 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's suppose we have a table containing two rows:
CREATE TABLE t_deadlock (id int); INSERT INTO t_deadlock VALUES (1), (2);
The following example 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 1 second (deadlock_timeout) |
|
|
COMMIT; |
ROLLBACK; |
As soon as the deadlock is detected, the following error message will show up:
psql: ERROR: deadlock detected
DETAIL: Process 91521 waits for ShareLock...
