Re: Deadlock with ShareLocks?

Mario Weilguni <mweilguni@sime.com> writes:
> Version:     8.1
> Query        : update last_modified set dataend=now() where type='list'
> DB-Error     : ERROR:  deadlock detected
> DETAIL:  Process 10454 waits for ShareLock on transaction 1347632; blocked by 
> process 15920.
> Process 15920 waits for ShareLock on transaction 1347633; blocked by process 
> 10454.

> I thought ShareLock is not really blocking, or am I wrong?

You're wrong.  This looks like a deadlock occasioned by trying to update
the same two rows in different orders in different transactions.  In a
pre-8.1 release I'd have guessed that this might be a deadlock on
foreign key master rows, but in 8.1 that can't happen anymore.

If "WHERE type = 'list'" selects multiple rows, and someone else might
be trying to update more than one of those same rows using a different
WHERE clause, deadlock is definitely possible.  You may not have much
choice but to take a table-level lock before starting the updates.
        regards, tom lane