regression, deadlock in high frequency single-row UPDATE

Andrew Sackville-West wrote:

Hi,

I've discussed this problem on irc a couple of times and think I've
found a regression that plagues our application, introduced in some
version newer than 9.1.9, and still present in 9.3.5.

I don't think you have provided everything: for instance I see that your
log has INSERTs into table access_tokens, which has a foreign key
relationship to/from some other table you showed.

I'm too lazy to unscramble the whole thing into a real schema right now.
pg_dump output would be more helpful, as would some trivial sample data
enough to reproduce the problem.

It'd probably be easier to reproduce if you wrap the statements in
transactions and have them sleep before COMMIT. (If you run two of
these updates in parallel, the first one blocks the second until the
first one commits, because they update the same row. What happens if
the blocking session runs the aforementioned INSERT while the other is
blocked? Perhaps three sessions are needed to show a problem without
explicit transactions.)

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On Fri, Aug 1, 2014 at 2:30 AM, Andrew Sackville-West <awest@janrain.com> wrote:

I have not been able to reproduce the deadlock by making concurrent
UPDATEs via what amounts to a bash fork-bomb w/ psql, but I suspect my
methodology might be too crude there.

Another way is using pgbench. Write a script file with the SQL query
in it (must be on a single line). Then launch it as:
pgbench -f scriptfile.sql -n -c 10 dbname

Regards,
Marti

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

On Fri, Aug 01, 2014 at 06:40:51PM +0300, Marti Raudsepp wrote:

On Fri, Aug 1, 2014 at 2:30 AM, Andrew Sackville-West <awest@janrain.com> wrote:

I have not been able to reproduce the deadlock by making concurrent
UPDATEs via what amounts to a bash fork-bomb w/ psql, but I suspect my
methodology might be too crude there.

Another way is using pgbench. Write a script file with the SQL query
in it (must be on a single line). Then launch it as:
pgbench -f scriptfile.sql -n -c 10 dbname

a perfect, thanks I'll see if I can reproduce this way and follow up.

A

--

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

On Thu, Jul 31, 2014 at 08:59:50PM -0400, Alvaro Herrera wrote:

Andrew Sackville-West wrote:

Hi,

I've discussed this problem on irc a couple of times and think I've
found a regression that plagues our application, introduced in some
version newer than 9.1.9, and still present in 9.3.5.

I don't think you have provided everything: for instance I see that your
log has INSERTs into table access_tokens, which has a foreign key
relationship to/from some other table you showed.

Ah you're correct, I'll attach the description of that table. But,
really, that is all that's going on in the database.

I'm too lazy to unscramble the whole thing into a real schema right now.
pg_dump output would be more helpful, as would some trivial sample data
enough to reproduce the problem.

It's really minimal data. I'll attach a dump of the access_grants and
oauth_clients table. The access_tokens table is not empty, but I'm not
sure how relevant the data is. What is shown in the log is
typical. There is another table in play here, refresh_tokens, but
we're not actually using them in this case -- the table is empty, and
the reference to it in access_tokens in null.

It'd probably be easier to reproduce if you wrap the statements in
transactions and have them sleep before COMMIT.

The issue here is, with no code changes, this works fine under very
heavy load in production against 9.0.x, 9.1.9, etc. But under
9.3.{3,4,5} it fails. That code has no transaction. I think adding a
transaction is a distraction from what appears to me to be a
regression.

Interestingly, if I change the code to include a transaction around
the UPDATE, the problem goes away (or so it seems so far in my testing).

(If you run two of
these updates in parallel, the first one blocks the second until the
first one commits, because they update the same row. What happens if
the blocking session runs the aforementioned INSERT while the other is
blocked? Perhaps three sessions are needed to show a problem without
explicit transactions.)

I've run pg_bench from 3 machines all pointing at the same database
running this same set of statements and it fails to induce the
deadlock.

It occurs to me that I haven't completely described the sequence of
actions, so here is a snippet of the actual SQL that a given action
induces, when it works as expected:

SET standard_conforming_strings TO on;

SET datestyle TO ISO;

SET client_encoding TO utf8;

SELECT "user_1"."id", "user_1"."uuid", "user_1"."lastUpdated" AT TIME
ZONE 'UTC', "user_1"."id" as _id
FROM "z8z6px927zu6qzzbnb5ntgghxg"."user_1" AS "user_1" WHERE "user_1"."id" = 129;

UPDATE "z8z6px927zu6qzzbnb5ntgghxg"."access_grants" ag
SET last_issued=DEFAULT
FROM "z8z6px927zu6qzzbnb5ntgghxg"."oauth_clients" oc
WHERE oc.id = ag.client_id AND ag.entity_name = 'user'
AND ag.entity_id = 129 AND oc.client_id = '3hp45h9d4f9wwtx7cvpus6rdb4s5kb9f'
RETURNING ag.id;

INSERT INTO "z8z6px927zu6qzzbnb5ntgghxg"."access_tokens"
(token, access_id, refresh_token_id, issued, expiry)
VALUES
('xys7vzs94huex2y7', 2, null, timezone('UTC'::text, now()), timezone('UTC'::text, now()) + '1:00:00':: interval);

This represents one complete action. There are several of these
running concurrently, interleaved together. There is no transaction
involved here. As far as I can tell, the application is using
bog-standard libpq (via FFI wrapper in Haskell) with no non-default
configuration applied to the connection.

I realize there's not a lot to go on here, and I don't expect help
debugging my application. I've got a patch that seems to resolve the
problem for us. But I'm suspicious of this change in behavior and hope
that pointing it out can help in some way.

regards,

A

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--

Andrew Sackville-West wrote:

I don't think you have provided everything: for instance I see that your
log has INSERTs into table access_tokens, which has a foreign key
relationship to/from some other table you showed.

Ah you're correct, I'll attach the description of that table. But,
really, that is all that's going on in the database.

See, access_tokens has a foreign key to access_grants. So when the
insert to access_tokens happens, the process also acquires a lock on a
tuple on access_grants, which should not conflict with the UPDATE; but
apparently it is blocking in some cases, for unknown reasons. But note
that one of the original tables had a large number of foreign keys to
other tables; are those necessary to reproduce the problem? I mean, if
you just copy those three tables to an otherwise empty database and then
run the hanging application, do you see a problem? If not, does adding
all those other tables cause the problem to show up?

Note that the set of columns covered by unique indexes is very
important. Your \d does not have an unique index on the updated column
AFAICS; but if it did have one, things would be completely different
(because then the insert would definitely conflict with the update).

It would help a lot of you posted the table definitions as pg_dump -t
output rather than \d, because it's much easier for me to reproduce the
setup, rather than cutting and pasting the \d output into CREATE TABLE
commands.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Some months later (stupid business priorities...) we have something to
report on this. We have been able to produce a minimal schema to
demonstrate the problem, and successfully induced the deadlock using
pgbench (thanks to Paulo Tanimoto, cc-ed here).

Please see:

https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3

for detailed instructions on how to trigger the deadlock.

I would be thrilled to learn that we've done something wrong here,
otherwise I think this represents a regression introduced in 9.3.

A

On Mon, Aug 04, 2014 at 12:30:27PM -0400, Alvaro Herrera wrote:

Andrew Sackville-West wrote:

I don't think you have provided everything: for instance I see that your
log has INSERTs into table access_tokens, which has a foreign key
relationship to/from some other table you showed.

Ah you're correct, I'll attach the description of that table. But,
really, that is all that's going on in the database.

See, access_tokens has a foreign key to access_grants. So when the
insert to access_tokens happens, the process also acquires a lock on a
tuple on access_grants, which should not conflict with the UPDATE; but
apparently it is blocking in some cases, for unknown reasons. But note
that one of the original tables had a large number of foreign keys to
other tables; are those necessary to reproduce the problem? I mean, if
you just copy those three tables to an otherwise empty database and then
run the hanging application, do you see a problem? If not, does adding
all those other tables cause the problem to show up?

Note that the set of columns covered by unique indexes is very
important. Your \d does not have an unique index on the updated column
AFAICS; but if it did have one, things would be completely different
(because then the insert would definitely conflict with the update).

It would help a lot of you posted the table definitions as pg_dump -t
output rather than \d, because it's much easier for me to reproduce the
setup, rather than cutting and pasting the \d output into CREATE TABLE
commands.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

--

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

On 09/12/14 12:55, Andrew Sackville-West wrote:

Some months later (stupid business priorities...) we have something to
report on this. We have been able to produce a minimal schema to
demonstrate the problem, and successfully induced the deadlock using
pgbench (thanks to Paulo Tanimoto, cc-ed here).

Please see:

https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3

for detailed instructions on how to trigger the deadlock.

I would be thrilled to learn that we've done something wrong here,
otherwise I think this represents a regression introduced in 9.3.

That is interesting - FWIW I can reproduce:

- 9.2.9 no deadlock
- 9.4rc1 many deadlocks

so something has changed after 9.2 for sure!

Just mucking about I changed certain things in your schema setup
(removed ON DELETE CASCADE, use CURRENT_TIMESTAMP instead of
now()...lessen the use of DEFAULT)...however still seeing deadlocks in
9.4, so at least it is easy to reproduce!

Regards

Mark

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

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking
...

depesz

On Tue, Dec 9, 2014 at 6:58 AM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz

hubert depesz lubaczewski wrote:

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking

I'm not surprised in the least, TBH.

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

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

On Mon, Dec 8, 2014 at 9:58 PM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz

wrote:

On 09/12/14 12:55, Andrew Sackville-West wrote:

https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3

for detailed instructions on how to trigger the deadlock.

That is interesting - FWIW I can reproduce:

- 9.2.9 no deadlock
- 9.4rc1 many deadlocks

so something has changed after 9.2 for sure!

Just mucking about I changed certain things in your schema setup (removed
ON DELETE CASCADE, use CURRENT_TIMESTAMP instead of now()...lessen the use
of DEFAULT)...however still seeing deadlocks in 9.4, so at least it is easy
to reproduce!

Can you speak to what benefits those changes might yield? Or point to
relevant docs?

thanks

A

On Tue, Dec 9, 2014 at 6:14 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

hubert depesz lubaczewski wrote:

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking

I'm not surprised in the least, TBH.

Thanks for your attention to this. Is there anything we're doing wrong that
could improve this in current 9.3 versions?

A

On 2014-12-09 09:38:33 -0800, Andrew Sackville-West wrote:

On Tue, Dec 9, 2014 at 6:14 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

hubert depesz lubaczewski wrote:

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking

I'm not surprised in the least, TBH.

Thanks for your attention to this. Is there anything we're doing wrong that
could improve this in current 9.3 versions?

I don't think anybody can seriously can give you a serious workaround
until the issue has been diagnosed further.

One thing you could try would be to create a unique index that spans all
the columns in the table... That'd prevent usage of lower locklevels
added in the above commit.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Andrew Sackville-West wrote:

Some months later (stupid business priorities...) we have something to
report on this. We have been able to produce a minimal schema to
demonstrate the problem, and successfully induced the deadlock using
pgbench (thanks to Paulo Tanimoto, cc-ed here).

Please see:

https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3

for detailed instructions on how to trigger the deadlock.

I would be thrilled to learn that we've done something wrong here,
otherwise I think this represents a regression introduced in 9.3.

To avoid having to visit an external URL that might well not exist at
all, and to simplify the test case which still has too many extraneous
details, here's a simpler version that still causes the UPDATE to
deadlock:

-- schema.sql:
create table pktab (id int primary key, data serial not null);
create table fktab (fk int references pktab);
insert into pktab values (1);

-- query.sql:
update pktab set data = default;
insert into fktab values (1);

This doesn't deadlock in 9.2, yet in 9.3 it raises an error in a few
seconds with pgbench -c 16.

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

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

On 10/12/14 06:28, Andrew Sackville-West wrote:

On Mon, Dec 8, 2014 at 9:58 PM, Mark Kirkwood

Just mucking about I changed certain things in your schema setup
(removed ON DELETE CASCADE, use CURRENT_TIMESTAMP instead of
now()...lessen the use of DEFAULT)...however still seeing deadlocks
in 9.4, so at least it is easy to reproduce!

Can you speak to what benefits those changes might yield? Or point to
relevant docs?

Not so much advantages - just seeing if I could still reproduce the
issue with a simpler test case i.e ensuring you were not doing anything
'odd' - and you are not :-)

The next step would be trying to figure out what commit introduced this
behaviour - but depesz has already beaten me to that (nice work)!

Cheers

Mark

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

On Tue, Dec 09, 2014 at 06:43:20PM +0100, Andres Freund wrote:

On 2014-12-09 09:38:33 -0800, Andrew Sackville-West wrote:

On Tue, Dec 9, 2014 at 6:14 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

hubert depesz lubaczewski wrote:

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre@alvh.no-ip.org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking

I'm not surprised in the least, TBH.

Thanks for your attention to this. Is there anything we're doing wrong that
could improve this in current 9.3 versions?

I don't think anybody can seriously can give you a serious workaround
until the issue has been diagnosed further.

agreed.

One thing you could try would be to create a unique index that spans all
the columns in the table... That'd prevent usage of lower locklevels
added in the above commit.

Thanks for this idea. I'll investigate.

thanks

A

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

Mark Kirkwood wrote:

Not so much advantages - just seeing if I could still reproduce the issue
with a simpler test case i.e ensuring you were not doing anything 'odd' -
and you are not :-)

The next step would be trying to figure out what commit introduced this
behaviour - but depesz has already beaten me to that (nice work)!

So I traced through the problem using the simplified test case I posted.
It goes like this:

* tuple on the referenced table has been updated a bunch of times
already, and keeps being updated, so there's a large update chain to
follow.

* two or more transactions (T1 and T2, say) have snapshots in which
some version of the tuple (not the last one) is visible.

* transaction T3 has a ForKeyShare lock on the latest version of the
tuple, which is grabbed because of the INSERT in the referencing
table. Note that these locks are propagated upwards in an update
chain, so even if T3 locked an old version, all future versions are
also locked by T3.

* each of T1 and T2 tries to update the version they have visible; this
fails because it's not the last version, so they need to go through
EvalPlanQual, which walks through the update chain.

At this point, T1 passes through heap_lock_tuple, gets the HW tuple
lock, marks itself as locker in the tuple's Xmax. T1 is scheduled out;
time for T2 to run. T2 goes through heap_lock_tuple, grabs HW lock;
examines Xmax, sees that T1 is the locker, goes to sleep until T1
awakes. T1 is scheduled to run again, runs heap_update. The first
thing it needs is to do LockTuple ... but T2 is holding that one, so T1
goes to sleep until T2 awakes -- now they are both sleeping on each
other.

Kaboom.

I'm not seeing a solution here. There is something wrong in having to
release the HW lock then grab it again, in that EvalPlanQual dance. It
sounds like we need to hold on to the tuple's HW lock until after
heap_update has run, but it's not at all clear how this would work --
the interface through EvalPlanQual is messy enough as it is without
entertaining the idea that a "please keep HW lock until later" flag
needs to be passed up and down all over the place.

One simple idea that occurs to me is have some global state in heapam.c;
when the EPQ code is invoked from ExecUpdate, we tell heapam to keep the
HW lock, and it's only released once heap_update is finished. That
would keep T2 away from the tuple. We'd need a PG_TRY block in
ExecUpdate to reset the global state once the update is done. (It's not
entirely clear that we need all this for deletes too. The main
difference is that after a delete completes the tuple is gone, which is
obviously not the case in an update. Also, deletes don't create update
chains.)

One thing I haven't thought too much about is why doesn't this happen in
9.2.

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

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

On 11/12/14 08:33, Alvaro Herrera wrote:

Mark Kirkwood wrote:

Not so much advantages - just seeing if I could still reproduce the issue
with a simpler test case i.e ensuring you were not doing anything 'odd' -
and you are not :-)

The next step would be trying to figure out what commit introduced this
behaviour - but depesz has already beaten me to that (nice work)!

So I traced through the problem using the simplified test case I posted.
It goes like this:

* tuple on the referenced table has been updated a bunch of times
already, and keeps being updated, so there's a large update chain to
follow.

* two or more transactions (T1 and T2, say) have snapshots in which
some version of the tuple (not the last one) is visible.

* transaction T3 has a ForKeyShare lock on the latest version of the
tuple, which is grabbed because of the INSERT in the referencing
table. Note that these locks are propagated upwards in an update
chain, so even if T3 locked an old version, all future versions are
also locked by T3.

* each of T1 and T2 tries to update the version they have visible; this
fails because it's not the last version, so they need to go through
EvalPlanQual, which walks through the update chain.

At this point, T1 passes through heap_lock_tuple, gets the HW tuple
lock, marks itself as locker in the tuple's Xmax. T1 is scheduled out;
time for T2 to run. T2 goes through heap_lock_tuple, grabs HW lock;
examines Xmax, sees that T1 is the locker, goes to sleep until T1
awakes. T1 is scheduled to run again, runs heap_update. The first
thing it needs is to do LockTuple ... but T2 is holding that one, so T1
goes to sleep until T2 awakes -- now they are both sleeping on each
other.

Kaboom.

I'm not seeing a solution here. There is something wrong in having to
release the HW lock then grab it again, in that EvalPlanQual dance. It
sounds like we need to hold on to the tuple's HW lock until after
heap_update has run, but it's not at all clear how this would work --
the interface through EvalPlanQual is messy enough as it is without
entertaining the idea that a "please keep HW lock until later" flag
needs to be passed up and down all over the place.

One simple idea that occurs to me is have some global state in heapam.c;
when the EPQ code is invoked from ExecUpdate, we tell heapam to keep the
HW lock, and it's only released once heap_update is finished. That
would keep T2 away from the tuple. We'd need a PG_TRY block in
ExecUpdate to reset the global state once the update is done. (It's not
entirely clear that we need all this for deletes too. The main
difference is that after a delete completes the tuple is gone, which is
obviously not the case in an update. Also, deletes don't create update
chains.)

One thing I haven't thought too much about is why doesn't this happen in
9.2.

Hmmm - interesting, yeah I think the why-it-doesn't-in-9.2 part is the
next thing to understand :-)

Cheers

Mark

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

On 11/12/14 10:56, Mark Kirkwood wrote:

On 11/12/14 08:33, Alvaro Herrera wrote:

Mark Kirkwood wrote:

Not so much advantages - just seeing if I could still reproduce the
issue
with a simpler test case i.e ensuring you were not doing anything
'odd' -
and you are not :-)

The next step would be trying to figure out what commit introduced this
behaviour - but depesz has already beaten me to that (nice work)!

So I traced through the problem using the simplified test case I posted.
It goes like this:

* tuple on the referenced table has been updated a bunch of times
already, and keeps being updated, so there's a large update chain to
follow.

* two or more transactions (T1 and T2, say) have snapshots in which
some version of the tuple (not the last one) is visible.

* transaction T3 has a ForKeyShare lock on the latest version of the
tuple, which is grabbed because of the INSERT in the referencing
table. Note that these locks are propagated upwards in an update
chain, so even if T3 locked an old version, all future versions are
also locked by T3.

* each of T1 and T2 tries to update the version they have visible; this
fails because it's not the last version, so they need to go through
EvalPlanQual, which walks through the update chain.

At this point, T1 passes through heap_lock_tuple, gets the HW tuple
lock, marks itself as locker in the tuple's Xmax. T1 is scheduled out;
time for T2 to run. T2 goes through heap_lock_tuple, grabs HW lock;
examines Xmax, sees that T1 is the locker, goes to sleep until T1
awakes. T1 is scheduled to run again, runs heap_update. The first
thing it needs is to do LockTuple ... but T2 is holding that one, so T1
goes to sleep until T2 awakes -- now they are both sleeping on each
other.

Kaboom.

I'm not seeing a solution here. There is something wrong in having to
release the HW lock then grab it again, in that EvalPlanQual dance. It
sounds like we need to hold on to the tuple's HW lock until after
heap_update has run, but it's not at all clear how this would work --
the interface through EvalPlanQual is messy enough as it is without
entertaining the idea that a "please keep HW lock until later" flag
needs to be passed up and down all over the place.

One simple idea that occurs to me is have some global state in heapam.c;
when the EPQ code is invoked from ExecUpdate, we tell heapam to keep the
HW lock, and it's only released once heap_update is finished. That
would keep T2 away from the tuple. We'd need a PG_TRY block in
ExecUpdate to reset the global state once the update is done. (It's not
entirely clear that we need all this for deletes too. The main
difference is that after a delete completes the tuple is gone, which is
obviously not the case in an update. Also, deletes don't create update
chains.)

One thing I haven't thought too much about is why doesn't this happen in
9.2.

Hmmm - interesting, yeah I think the why-it-doesn't-in-9.2 part is the
next thing to understand :-)

In the most glaringly obvious (and possibly not at all helpful) analysis
- this is because we are using "FOR KEY SHARE" instead of "FOR SHARE" in
the newer code. Reverting this (see attached very basic patch) gets rid
of the deadlock (tested in 9.5devel). However this is obviously a less
than desirable solution, as it loses any possible concurrency
improvement - that we worked hard to gain in the patch that added this
new lock mode! We really want to figure out how to keep "FOR KEY SHARE"
and have it *not* deadlock.

Cheers

Mark

Mark Kirkwood wrote:

In the most glaringly obvious (and possibly not at all helpful) analysis -
this is because we are using "FOR KEY SHARE" instead of "FOR SHARE" in the
newer code. Reverting this (see attached very basic patch) gets rid of the
deadlock (tested in 9.5devel). However this is obviously a less than
desirable solution, as it loses any possible concurrency improvement - that
we worked hard to gain in the patch that added this new lock mode! We really
want to figure out how to keep "FOR KEY SHARE" and have it *not* deadlock.

Yeah. The problem is that the INSERT in the fktab table takes a FOR KEY
SHARE lock in the pktab table. This puts the inserting process' Xid
into the tuple's Xmax, but allows concurrent UPDATE while the inserter
transaction is still in progress. If you use a FOR SHARE lock, the
UPDATE is disallowed until the inserter transaction is completed.

Having the inserter be live is what causes the heap_lock_tuple and
heap_update calls in the updater process to take the heavyweight tuple
lock.

I think what we should do here is NOT take the HW lock unless we're
going to sleep in heap_lock_tuple and heap_update. Right now we always
take it if HeapTupleSatisfiesUpdate returns HeapTupleBeingUpdated, even
if we end up not sleeping to wait for something else.

I'm going to experiment with that idea and see if it leads to a
solution. I tried the other idea yesterday (to keep the HW tuple lock
we acquire in heap_lock_tuple until heap_update is done) but aside from
being very complicated and bug-prone, it doesn't solve the problem
anyway.

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

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