Re: Deadlock with ShareLocks?

Mario Weilguni <mweilguni(at)sime(dot)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