Re: deadlock problem

On 05/30/2011 10:04 PM, Sebastian Böhm wrote:

>     Acquired by CREATE INDEX (without CONCURRENTLY).
>
> so where the "ShareLock" is acquired? I don't create an index here.

There's some confusing historical terminology involved here, I'm afraid.

The documentation you referred to talks about table-level locks, used
when a whole table is partially or wholly locked.

There are *also* row-level locks of both exclusive and shared kinds.

I *think* the deadlock you are experiencing is on a row-level ShareLock,
rather than a table-level lock.

Here's a demo. 1> and 2> are two different psql sessions open at once
and the sequence of commands shown below causes them to deadlock with
each other, giving a message just like yours:

1> create table a (x integer);
1> insert into a(x) values (1),(2),(3);

1> begin;
1> delete from a where x = 1;
2> begin;
2> delete from a where x = 2;
2> delete from a where x = 1;
1> delete from a where x = 2;

Now one of the transactions will abort with:

ERROR:  deadlock detected
DETAIL:  Process 15727 waits for ShareLock on transaction 1272; blocked
by process 15725.
Process 15725 waits for ShareLock on transaction 1273; blocked by
process 15727.
HINT:  See server log for query details.


Hope this helps.

--
Craig Ringer