Re: Deadlock with ShareLocks?

Am Dienstag, 13. Dezember 2005 16:35 schrieb Tom Lane:
> 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.

Hi Tom,

there must be something different here. In fact, this is the real data from
the table:
type | dataend
---------------+-------------------------------
applikationen | 2004-09-03 14:44:44.63422+02
xslt | 2005-12-07 21:30:08.183392+01
red | 2005-12-08 19:36:50.357642+01
list | 2005-12-13 14:35:44.544795+01
struktur | 2005-12-13 16:21:52.645182+01

Table "public.last_modified"
Column | Type | Modifiers
---------+--------------------------+-----------
type | character varying(32) | not null
dataend | timestamp with time zone | not null
Indexes:
"last_modified_pkey" PRIMARY KEY, btree ("type")

Since the type field is PK, there cannot be 2 rows with type='list', I guess
the deadlock must have some different explanation. There are no foreign key
constraints, triggers, rules involved.

Best regards,
Mario Weilguni