Re: Deadlock with ShareLocks?

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

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+02xslt          | 2005-12-07
21:30:08.183392+01red          | 2005-12-08 19:36:50.357642+01list          | 2005-12-13 14:35:44.544795+01struktur
| 2005-12-13 16:21:52.645182+01
 
         Table "public.last_modified"Column  |           Type           | Modifiers
---------+--------------------------+-----------type    | character varying(32)    | not nulldataend | timestamp with
timezone | 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