Re: BUG #8434: Why does dead lock occur many times ?

Hi,

This issue occurred even on 9.3.0.
I took the following information, and I attached them.

pg_locks.txt ... pg_locks during deadlock
pg_stat_activity.txt ... pg_stat_activity during deadlock
postgresql.log ... PostgreSQL log (including LOCK_DEBUG log)

The OID of the table is 16459.

I run 3 transactions.
One transaction of them executes SELECT FOR UPDATE and UPDATE a row.
And two transactions of them execute only UPDATE the same line.

The results is that all of UPDATE is waiting.

I changed a little bit how to reproduce.
It is as follows.

- Compiling PostgreSQL
Add -DLOCK_DEBUG to CFLAGS

- Changing postgresql.conf following parameters
log_lock_waits = on
deadlock_timeout = 1min # for getting information during deadlock
debug_deadlocks = on
trace_lock_table = 16459

log_line_prefix = '%t [%p] %q(%a) '
logging_collector = on
log_filename = 'postgresql.log'
log_min_messages = info
log_error_verbosity = verbose

- Testing
1. Initializing data
Executing createdb.sh
This creates a table with 2 columns, and insert 1 row.

2. Running the transactions
Executing test.sh
This runs 3 transactions.

2 transactions of them (tx1) are
BEGIN;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;

1 transaction of them (tx2) is
BEGIN;
SELECT * FROM t WHERE col1 = 1 FOR UPDATE;
UPDATE t SET col2 = 'A' WHERE col1 = 1;
COMMIT;

regards,

2013/9/4 <katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp>

> The following bug has been logged on the website:
>
> Bug reference: 8434
> Logged by: Tomonari Katsumata
> Email address: katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp
> PostgreSQL version: 9.3rc1
> Operating system: RedHatEnterpriseLinux 6.4(x86_64)
> Description:
>
> Hi,
>
>
> I'm testing PostgreSQL 9.3rc1.
> Many times updates and selects for update become dead lock situation.
>
>
> The reproduce is:
> 1. initializing data
> createdb testdb
> psql testdb -c "create table t (col1 int, col2 int, col3 text);"
> psql testdb -c "insert into t values (1, 4, 'A');"
> psql testdb -c "insert into t values (2, 5, 'B');"
> psql testdb -c "insert into t values (3, 6, 'C');"
>
>
> 2. executing updates and selects for update
> (run below script)
> ----
> #!/bin/sh
>
>
> ./tx1 > /dev/null &
> ./tx2 > /dev/null &
> ./tx3 > /dev/null &
>
>
> wait
> ----
>
>
> tx1 is:
> ----
> #!/bin/sh
>
>
> while :
> do
> psql testdb << EOF
> BEGIN;
> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
> COMMIT;
> \q
> EOF
> done
> ----
>
>
> tx2 is:
> ----
> #!/bin/sh
>
>
> while :
> do
> psql testdb << EOF
> BEGIN;
> SELECT col1, col2, col3 FROM t WHERE col1 = 3 AND col2 = 6 FOR UPDATE;
> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
> COMMIT;
> \q
> EOF
> done
> ----
>
>
> tx3 is:
> ----
> #!/bin/sh
>
>
> while :
> do
> psql testdb << EOF
> BEGIN;
> UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
> COMMIT;
> \q
> EOF
> done
> ----
>
>
> Then, I got below messages.
> --------
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-1 659102 (pgsql, testdb,
> [local], psql) LOG: 00000: process 29630 detected deadlock while waiting
> for ShareLock on transaction 659103 after 1000.136 ms
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-2 659102 (pgsql, testdb,
> [local], psql) LOCATION: ProcSleep, proc.c:1232
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-3 659102 (pgsql, testdb,
> [local], psql) STATEMENT: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2
> =
> 6;
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-4 659102 (pgsql, testdb,
> [local], psql) ERROR: 40P01: deadlock detected
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-5 659102 (pgsql, testdb,
> [local], psql) DETAIL: Process 29630 waits for ShareLock on transaction
> 659103; blocked by process 29631.
> Process 29631 waits for ExclusiveLock on tuple (0,153) of relation
> 16385 of database 16384; blocked by process 29630.
> Process 29630: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
> Process 29631: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6;
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-6 659102 (pgsql, testdb,
> [local], psql) HINT: See server log for query details.
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-7 659102 (pgsql, testdb,
> [local], psql) LOCATION: DeadLockReport, deadlock.c:956
> 2013-09-04 15:25:25 JST 29630 5226d254.73be-8 659102 (pgsql, testdb,
> [local], psql) STATEMENT: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2
> =
> 6;
> --------
>
>
> I did not get these messages with PostgreSQL 9.2.4.
>
>
> Why did I get the dead lock situation with PostgreSQL9.3rc1?
> degrading? or I'm missing something?
>
>
> regards,
> ------------
> Tomonari Katsumata
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
OTSUKA Kenji