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?
-
Can you show cut down queries/schema for where your deadlocks are occuring? Do you have hash-indexes in use within your transactions?Chris Farmiloe– Chris Farmiloe2013-04-16 17:46:32 +00:00Commented Apr 16, 2013 at 17:46
Add a comment
|
2 Answers
You don't need any explicit LOCK to go into a deadlock. Here's a very simple demo from scratch with only INSERTs:
create table a(i int primary key);
create table b(i int primary key);
Session #1 does:
begin;
insert into a values(1);
Then session #2 does:
begin;
insert into b values(1);
insert into a values(1);
-- here it goes into waiting for session #1 to finish its transaction
Then session #1 does:
insert into b values(1);
And then the deadlock occurs:
ERROR: deadlock detected
DETAIL: Process 9571 waits for ShareLock on transaction 4150; blocked by process 9501.
Process 9501 waits for ShareLock on transaction 4149; blocked by process 9571.
HINT: See server log for query details.
The same could happen with simple UPDATEs or a combination of UPDATEs and INSERTs. These operations take implicit locks, and if they happen in different sessions in different orders, they may deadlock.
Comments
I would suspect hash indexes first.
- Switch any
hash-indexes you have toB-tree - Use
Serializableisolation level if it seems appropriate.
2 Comments
Vadim Samokhin
I do not have any hash indexes, anyway how can they be the reason?
Chris Farmiloe
Possible since hash-bucket-level locks are used for read/write access as part of their implementation.