Re: regression, deadlock in high frequency single-row UPDATE

Mark Kirkwood wrote:

> FWIW - I was having a look at the isolationtester spec. Playing with the
> pgbench setup to see the minimal number of session I needed to provoke the
> deadlock (unpatched 9.5 code) seemed to converge to about 4. I managed to
> still see 1 deadlock with each session only doing 1 transaction - i.e:
>
> $ pgbench -c 4 -C -n -t 1 -f query.sql deadlock
>
> ...which resulted in 1 deadlock. So we may be able to get a reasonable test
> case that shows this up! I'll take a look at setting up a test case later -
> but don't let that stop you doing it 1st :-)

Okay, I created a reproducer ... but it doesn't work. I mean, the
commands are fine: if I execute them manually in four sessions in an
unpatched server I get a deadlock. But isolationtester doesn't know to
handle more than one blocked session, so it just hangs when the third
session blocks after the second one is already blocked. :-(

Here it is anyway. If we get around to improving isolationtester to
handle more blocked sessions, it would be good to add this file.

setup {
DROP TABLE IF EXISTS pktab;
CREATE TABLE pktab (id int PRIMARY KEY, data SERIAL NOT NULL);
INSERT INTO pktab VALUES (1, DEFAULT);
}

teardown {
DROP TABLE pktab;
}

session "s1"
step "s1_advlock" { SELECT pg_advisory_lock(142857); }
step "s1_chain" { UPDATE pktab SET data = DEFAULT; }
step "s1_begin" { BEGIN; }
step "s1_grablock" { SELECT * FROM pktab FOR KEY SHARE; }
step "s1_advunlock" { SELECT pg_advisory_unlock(142857); }
step "s1_commit" { COMMIT; }

session "s2"
step "s2_update" { UPDATE pktab SET data = DEFAULT WHERE pg_advisory_lock_shared(142857) IS NOT NULL; }

session "s3"
step "s3_update" { UPDATE pktab SET data = DEFAULT WHERE pg_advisory_lock_shared(142857) IS NOT NULL; }

session "s4"
step "s4_update" { UPDATE pktab SET data = DEFAULT WHERE pg_advisory_lock_shared(142857) IS NOT NULL; }

permutation "s1_advlock" "s2_update" "s3_update" "s4_update" "s1_chain" "s1_grablock" "s1_advunlock" "s1_commit"

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