I am new for PostgreSQL. I want to simulate deadlock for this schedule:
How to simulate deadlock in PostgreSQL? Is it possible at all? How to lock particular column?
BEGIN;
UPDATE deadlock_demonstration
SET salary1=(SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 1
FOR UPDATE)+100
WHERE worker_id=1;
SELECT pg_sleep(5);
commit;
SELECT salary2 FROM deadlock_demonstration WHERE worker_id = 1 FOR UPDATE;
In another screen, I have run this:
BEGIN;
UPDATE deadlock_demonstration
SET salary2=(SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 1
FOR UPDATE)+200
WHERE worker_id=1;
SELECT pg_sleep(5);
commit;
SELECT salary1 FROM deadlock_demonstration WHERE worker_id = 1 FOR UPDATE;
Why deadlock is not happening? Can you give a suggestion, what I should change in order to stimulate deadlock?
psql
or two query windows in pgAdmin (each has its own session).BEGIN;
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
.)
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 2
BEGIN;
BEGIN;
SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 1
FOR UPDATE;
SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 2
FOR UPDATE;
UPDATE deadlock_demonstration
SET salary1 = 100
WHERE worker_id = 2;
UPDATE deadlock_demonstration
SET salary1 = 100
WHERE worker_id = 1;
--> ... 💣 deadlock!
The OP user3388473 contributed this screenshot after verifying the solution:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With