1

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock was detected is wrapped in transaction block though. Anyway, how comes it?

7

Suppose session 1 does:

begin transaction;
update a set col = val where pk = 1;
update a set col = val where pk = 2;
commit;

and at the exact same time session 2 does the opposite:

begin transaction;
update a set col = val where pk = 2;
update a set col = val where pk = 1;
commit;

Then session 1 will be waiting for session 2 to commit or rollback the update on the row where pk = 2 while at the same time session 2 will be waiting for session 1 to commit or rollback the update on the row where pk = 1. Deadlock.

| improve this answer | |
  • Thx, by the way, will there be a deadlock in the same situation in MySQL? – Vadim Samokhin Apr 16 '13 at 17:50
  • 1
    I think most DBMSes will get a deadlock for this scenario. – Colin 't Hart Apr 16 '13 at 17:56
  • 1
    @Zapadlo: yes, absolutely. Every DBMS will wind up in a deadlock situation with this example. – a_horse_with_no_name Apr 16 '13 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.