18

I am new for PostgreSQL. I want to simulate deadlock for this schedule:
Image of sample data

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3388473
  • 953
  • 2
  • 12
  • 25

2 Answers2

26
  1. Open two connections in parallel, like two instances of psql or two query windows in pgAdmin (each has its own session).
  2. Start a transaction in each connection. BEGIN;
  3. Run mutually conflicting commands in turns.
  4. Before you can commit, one of the two will be rolled back with a deadlock exception.
  5. 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 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

The OP user3388473 contributed this screenshot after verifying the solution:

Screenshot reproducing this in psql

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you for your answer! I have run 2 queries(see in edited question). 2 queries are giving same error: ERROR: in PL/pgSQL it not allowed to start/finish transaction HINT: Use block BEGIN with EXCEPTION. CONTEXT: function PL/pgSQL transaction3(), line 11, operator SQL-operator. Does this mean deadlock happened? – user3388473 Apr 01 '14 at 17:33
  • 1
    You *cannot* do that inside a PL/pgSQL function. A function is always executed inside a transaction, as a whole. You need plain SQL commands. – Erwin Brandstetter Apr 01 '14 at 17:57
  • I have inserted plain sql command(see edited question). But for some reason, deadlock is not happening. I do not have idea why. What I am doing wrong? – user3388473 Apr 01 '14 at 18:20
  • I have tried this code in one sql query then in 2 query screens. Result returned in 13-14ms. Deadlock did not happen. – user3388473 Apr 01 '14 at 19:17
  • @user3388473. Works for me. I tested. You must be missing something. Two separate transactions (two psql sessions or two separate query windows in pgAdmin). Execute commands in turn, not all at once. The referenced rows have to exist! Or no lock will be taken. – Erwin Brandstetter Apr 01 '14 at 19:38
  • @ErwinBrandstetter do you speak Russian? The screenshot. – Nick Apr 25 '18 at 00:24
  • @Nick: Njet. :) The OP contributed the screenshot after he verified my solution. – Erwin Brandstetter Apr 25 '18 at 00:49
  • @ErwinBrandstetter Does this still work in 2018? My second instance just hangs and waits on the the first one after the `FOR UPDATE;`. It starts responding again once I finish the first transaction. – Cramps Jul 23 '18 at 16:42
  • @Cramps: Still true in the latest versions (and not likely to change, ever). You need *mutually conflicting* commands like demonstrated. In your case, the second instance should lock something that the first instance tries to lock in return. – Erwin Brandstetter Jul 23 '18 at 16:59
  • I see. I ran the same query on both. I tried `BEGIN; SELECT * FROM table FOR UPDATE;` and `BEGIN; SELECT * FROM table WHERE id = 1 FOR UPDATE;` and both result in the second instance being hung until the first one finishes (e.g., committed). Any idea why? (compiled Postgres from source on a Mac) – Cramps Jul 23 '18 at 17:07
  • 1
    @Cramps: Running the same queries in both sessions *cannot* produce a deadlock (while they lock rows in deterministic fashion). That's actually the recommended strategy to *avoid* deadlocks (example: https://dba.stackexchange.com/a/195220/3684). Try my example above to produce a deadlock. – Erwin Brandstetter Jul 23 '18 at 17:12
0

Does this mean deadlock happened?

No. It does mean what it says, you can not use commit in pgsql, clearly said here.

Str.
  • 1,389
  • 9
  • 14