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