Deadlock with ShareLocks?

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+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

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