Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
Thx, by the way, will there be a deadlock in the same situation in MySQL? – Zapadlo Apr 16 at 17:50
1  
I think most DBMSes will get a deadlock for this scenario. – Colin 't Hart Apr 16 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 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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