Geek Answers Handbook

How to simulate deadlock in PostgreSQL?

I am new to PostgreSQL. I want to simulate a dead end for this graph: enter image description here

How to simulate deadlock in PostgreSQL? Is it even possible? How to lock a specific column?
EDIT:

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;

On another screen, I ran 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 does a dead end not occur? Can you give a suggestion that I should change in order to stimulate an impasse?

+8
source share
2 answers
  1. Open two connections in parallel, like two psql instances or two query windows in pgAdmin (each has its own session).
  2. Start a transaction on each connection. BEGIN;
  3. Execute mutually conflicting commands in turn.
  4. Before you can commit, one of the two will be rolled back with the exception of the deadlock.
  5. You can roll back another. ROLLBACK;

Explicitly locking tables is as simple as:

 LOCK tbl;

String locking can be done with:

 SELECT * FROM tbl WHERE boo = 3 FOR UPDATE;

Or FOR SHARE , etc. Details in the manual here.

example

Your added example cannot be deadlocked. Both try to first take the same lock on the same row of the same table. The second will wait for the first to finish.

Example for creating a dead end (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!

Result

OP user3388473 provided this screenshot after checking my solution:

enter image description here

+14
source

Does this mean that a dead end has occurred?

No. This means that it says you cannot use commit in pgsql, clearly stated here .

0
source

More articles:


All Articles