Prev: [HACKERS] Concurrent MERGE
Next: ECPG dynamic cursor fix for UPDATE/DELETE... WHERE CURRENT OF :curname
From: Josh Berkus on 5 Aug 2010 18:49

> Hm? Please explain what you're talking about.

Transaction A locks 1 and wants a lock on 2
Transaction B locks 2 and wants a lock on 3
Transaction C locks 3 and wants a lock on 1

I've never had the deadlock detector successfully deal with the above.
Let alone a 4-way.

> Not sure I believe this either; one deadlock kills one transaction.
> If you lose multiple transactions I think you had multiple deadlocks.

Deadlock termination kills *all* of the transactions involved in the
deadlock; what else could it do? This is as opposed to serialization
failures, in which usually only one of the transactions involved fails.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Mark Kirkwood on 5 Aug 2010 19:06
On 06/08/10 10:49, Josh Berkus wrote:
>
>> Hm? Please explain what you're talking about.
>>
> Transaction A locks 1 and wants a lock on 2
> Transaction B locks 2 and wants a lock on 3
> Transaction C locks 3 and wants a lock on 1
>
> I've never had the deadlock detector successfully deal with the above.
> Let alone a 4-way.
>
>

Hmm - seems to work ok for me (8.3.11 with pgbench schema updating
branches table by bid):

UPDATE branches SET filler='filled' WHERE bid=:x

I get transaction A succeeds, B is rolled back by the deadlock detector,
C left waiting for A to commit or rollback. What do you find?

Cheers

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andres Freund on 5 Aug 2010 19:07
On Thu, Aug 05, 2010 at 03:49:05PM -0700, Josh Berkus wrote:
>
> > Hm? Please explain what you're talking about.
>
> Transaction A locks 1 and wants a lock on 2
> Transaction B locks 2 and wants a lock on 3
> Transaction C locks 3 and wants a lock on 1
>
> I've never had the deadlock detector successfully deal with the above.
> Let alone a 4-way.
Hm. I have seen 5way deadlocks getting resolved just recently. I can
find the relevant if you find it interesting, but I doubt it is.

> > Not sure I believe this either; one deadlock kills one transaction.
> > If you lose multiple transactions I think you had multiple deadlocks.
>
> Deadlock termination kills *all* of the transactions involved in the
> deadlock; what else could it do? This is as opposed to serialization
> failures, in which usually only one of the transactions involved fails.
Uhm:

postgres=# CREATE TABLE a();
CREATE TABLE
postgres=# CREATE TABLE b();
CREATE TABLE

a: postgres=# BEGIN;LOCK a;

b: postgres=# BEGIN;LOCK b;
BEGIN;LOCK a;

a: postgres=# lock b;

b:
ERROR: deadlock detected
DETAIL: Process 12016 waits for AccessExclusiveLock on relation 24585 of database 11564; blocked by process 12011.
Process 12011 waits for AccessExclusiveLock on relation 24588 of database 11564; blocked by process 12016.
HINT: See server log for query details


Afaik it worked like that for years.


Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 5 Aug 2010 19:08
Josh Berkus <josh(a)agliodbs.com> writes:
>> Hm? Please explain what you're talking about.

> Transaction A locks 1 and wants a lock on 2
> Transaction B locks 2 and wants a lock on 3
> Transaction C locks 3 and wants a lock on 1

> I've never had the deadlock detector successfully deal with the above.
> Let alone a 4-way.

>> Not sure I believe this either; one deadlock kills one transaction.
>> If you lose multiple transactions I think you had multiple deadlocks.

> Deadlock termination kills *all* of the transactions involved in the
> deadlock; what else could it do? This is as opposed to serialization
> failures, in which usually only one of the transactions involved fails.

I'm not sure whose deadlock detector you're talking about, but it's
not Postgres'.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Josh Berkus on 5 Aug 2010 19:24

>> I've never had the deadlock detector successfully deal with the above.
>> Let alone a 4-way.
> Hm. I have seen 5way deadlocks getting resolved just recently. I can
> find the relevant if you find it interesting, but I doubt it is.

Ah, I didn't know that it was even *supposed* to resolve
larger-than-2-way deadlocks, so I didn't attempt to look for more
granular information. Next time I need to resolve one of these, I'll
get analysis information about exactly which kinds of locks are being
held where. I've seen it happen at multiple sites running 8.3 and 8.4,
so whatever code is supposed to resolve circular deadlocks doesn't work
all the time.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: [HACKERS] Concurrent MERGE
Next: ECPG dynamic cursor fix for UPDATE/DELETE... WHERE CURRENT OF :curname