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

Hi,

Thanks to your info, otsuka-san.

It seems to be related to some changes in
the [Improve concurrency of foreign key locking] commit(*).
(*) commitid : 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Because I could not reproduce "dead lock" before the commit.
And I'm thinking that also this commit is related to BUG#8470.
If so, I hope Alvaro will find the solution for this problem.

Maybe I can't take enough time, but I'll investigate this continuously.
If someone could reproduce this, please share information.

regards,
-------
NTT Software Corporation
Tomonari Katsumata

2013/9/19 OTSUKA Kenji <otsuka(dot)knj(at)gmail(dot)com>

> 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
>
>
> --
> 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
>
>