Обсуждение: Deadlock with ShareLocks?

I've an interesting error with deadlocks on a quite simple table:

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? The bad thing is, 
that it's quite difficult to get the info what the other backend was doing at 
the same time. Maybe it would be a good idea to log the statements of both 
backends (if available). Would make detecting such errors quite easier.

Best Regards,Mario Weilguni

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

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

Mario Weilguni <mweilguni@sime.com> writes:
> Since the type field is PK, there cannot be 2 rows with type='list', I guess 
> the deadlock must have some different explanation.

Then the deadlock must involve rows in two different tables.  What else
are you doing in the same transaction(s) as updating last_modified?
        regards, tom lane

Am Dienstag, 13. Dezember 2005 16:52 schrieb Tom Lane:
> Mario Weilguni <mweilguni@sime.com> writes:
> > Since the type field is PK, there cannot be 2 rows with type='list', I
> > guess the deadlock must have some different explanation.
>
> Then the deadlock must involve rows in two different tables.  What else
> are you doing in the same transaction(s) as updating last_modified?

That's what I think too, unfortunatly, I can't say for sure. The application 
uses around 1000 different queries, and I had no logging on for this case. So 
I guess two backends might have issued interleaved updates.

I think without logging this cannot be solved here. 

Best regards,Mario Weilguni

Mario Weilguni <mweilguni@sime.com> writes:
> I guess two backends might have issued interleaved updates.
> I think without logging this cannot be solved here. 

Yeah, it's annoying that there is no convenient way to see the contents
of pg_locks at the instant of the deadlock.  Knowing which tuple locks
were held would give at least some additional information.
        regards, tom lane

On Tue, 2005-12-13 at 11:04 -0500, Tom Lane wrote:
> Mario Weilguni <mweilguni@sime.com> writes:
> > I guess two backends might have issued interleaved updates.
> > I think without logging this cannot be solved here. 
> 
> Yeah, it's annoying that there is no convenient way to see the contents
> of pg_locks at the instant of the deadlock.  Knowing which tuple locks
> were held would give at least some additional information.

Can we LOG that info? If the deadlock detector knows about it, surely we
can dump it out usefully.

Best Regards, Simon Riggs