How to simulate deadlock in PostgreSQL?
- Open two connections in parallel, like two instances of
psql
or two query windows in pgAdmin (each has its own session). - Start a transaction in each connection.
BEGIN;
- Run mutually conflicting commands in turns.
- Before you can commit, one of the two will be rolled back with a deadlock exception.
- You may want to roll back the other.
ROLLBACK;
Explicitly locking tables is as simple as:
LOCK tbl;
Locking rows can be done with:
SELECT * FROM tbl WHERE boo = 3 FOR UPDATE;
Or FOR SHARE
etc. Details in the manual.
(Or implicitly with UPDATE
or DELETE
.)
Example
Your added example cannot deadlock. Both try to take the same lock on the same row of the same table first. The second will wait for the first to finish.
Example to actually produce a deadlock (rows must exist or no lock will be taken):
Transaction 1 Transaction 2BEGIN; BEGIN;SELECT salary1 FROM deadlock_demonstrationWHERE worker_id = 1FOR UPDATE; SELECT salary1 FROM deadlock_demonstration WHERE worker_id = 2 FOR UPDATE;UPDATE deadlock_demonstrationSET salary1 = 100WHERE worker_id = 2; UPDATE deadlock_demonstration SET salary1 = 100 WHERE worker_id = 1; --> ... 💣 deadlock!
Result
The OP user3388473 contributed this screenshot after verifying the solution:
Does this mean deadlock happened?
No. It does mean what it says, you can not use commit
in pgsql, clearly said here.