pgsql: Avoid spurious deadlocks when upgrading a tuple lock

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Avoid spurious deadlocks when upgrading a tuple lock

Álvaro Herrera
Avoid spurious deadlocks when upgrading a tuple lock

When two (or more) transactions are waiting for transaction T1 to release a
tuple-level lock, and transaction T1 upgrades its lock to a higher level, a
spurious deadlock can be reported among the waiting transactions when T1
finishes.  The simplest example case seems to be:

T1: select id from job where name = 'a' for key share;
Y: select id from job where name = 'a' for update; -- starts waiting for X
Z: select id from job where name = 'a' for key share;
T1: update job set name = 'b' where id = 1;
Z: update job set name = 'c' where id = 1; -- starts waiting for X
T1: rollback;

At this point, transaction Y is rolled back on account of a deadlock: Y
holds the heavyweight tuple lock and is waiting for the Xmax to be released,
while Z holds part of the multixact and tries to acquire the heavyweight
lock (per protocol) and goes to sleep; once X releases its part of the
multixact, Z is awakened only to be put back to sleep on the heavyweight
lock that Y is holding while sleeping.  Kaboom.

This can be avoided by having Z skip the heavyweight lock acquisition.  As
far as I can see, the biggest downside is that if there are multiple Z
transactions, the order in which they resume after X finishes is not
guaranteed.

Backpatch to 9.6.  The patch applies cleanly on 9.5, but the new tests don't
work there (because isolationtester is not smart enough), so I'm not going
to risk it.

Author: Oleksii Kliukin
Discussion: https://postgr.es/m/B9C9D7CD-EB94-4635-91B6-E558ACEC0EC3@...

Branch
------
REL_11_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/85600b7b5da42c5166eb1188c173beb3cc356178

Modified Files
--------------
src/backend/access/heap/README.tuplock             |  10 ++
src/backend/access/heap/heapam.c                   |  84 +++++++++---
.../expected/tuplelock-upgrade-no-deadlock.out     | 150 +++++++++++++++++++++
src/test/isolation/isolation_schedule              |   1 +
.../specs/tuplelock-upgrade-no-deadlock.spec       |  57 ++++++++
5 files changed, 281 insertions(+), 21 deletions(-)

Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Avoid spurious deadlocks when upgrading a tuple lock

Tatsuo Ishii-3
I am a little bit confused. Here T1 and X are the same transaction?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Avoid spurious deadlocks when upgrading a tuple lock
>
> When two (or more) transactions are waiting for transaction T1 to release a
> tuple-level lock, and transaction T1 upgrades its lock to a higher level, a
> spurious deadlock can be reported among the waiting transactions when T1
> finishes.  The simplest example case seems to be:
>
> T1: select id from job where name = 'a' for key share;
> Y: select id from job where name = 'a' for update; -- starts waiting for X
> Z: select id from job where name = 'a' for key share;
> T1: update job set name = 'b' where id = 1;
> Z: update job set name = 'c' where id = 1; -- starts waiting for X
> T1: rollback;
>
> At this point, transaction Y is rolled back on account of a deadlock: Y
> holds the heavyweight tuple lock and is waiting for the Xmax to be released,
> while Z holds part of the multixact and tries to acquire the heavyweight
> lock (per protocol) and goes to sleep; once X releases its part of the
> multixact, Z is awakened only to be put back to sleep on the heavyweight
> lock that Y is holding while sleeping.  Kaboom.
>
> This can be avoided by having Z skip the heavyweight lock acquisition.  As
> far as I can see, the biggest downside is that if there are multiple Z
> transactions, the order in which they resume after X finishes is not
> guaranteed.
>
> Backpatch to 9.6.  The patch applies cleanly on 9.5, but the new tests don't
> work there (because isolationtester is not smart enough), so I'm not going
> to risk it.
>
> Author: Oleksii Kliukin
> Discussion: https://postgr.es/m/B9C9D7CD-EB94-4635-91B6-E558ACEC0EC3@...
>
> Branch
> ------
> REL_11_STABLE
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/85600b7b5da42c5166eb1188c173beb3cc356178
>
> Modified Files
> --------------
> src/backend/access/heap/README.tuplock             |  10 ++
> src/backend/access/heap/heapam.c                   |  84 +++++++++---
> .../expected/tuplelock-upgrade-no-deadlock.out     | 150 +++++++++++++++++++++
> src/test/isolation/isolation_schedule              |   1 +
> .../specs/tuplelock-upgrade-no-deadlock.spec       |  57 ++++++++
> 5 files changed, 281 insertions(+), 21 deletions(-)
>


Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Avoid spurious deadlocks when upgrading a tuple lock

Alvaro Herrera-9
On 2019-Jun-14, Tatsuo Ishii wrote:

> I am a little bit confused. Here T1 and X are the same transaction?

Argh :-(  Yes, X and T1 are the same transaction.



--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services