Re: BUG #8434: Why does dead lock occur many times ? - Mailing list pgsql-bugs

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.knj@gmail.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.tomonari@po.ntts.co.jp>
>
> The following bug has been logged on the website:
>>
>> Bug reference:      8434
>> Logged by:          Tomonari Katsumata
>> Email address:      katsumata.tomonari@po.ntts.co.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>
>
> --
> OTSUKA Kenji
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>