Обсуждение: deadlock
Hello. I've encountered deadlock on postgresql 8.1. Here is the simple example. create table t1 ( id1 integer primary key, val1 integer ); create table t2 ( id2 integer primary key, id1 integer references t1 on delete cascade, val1 integer, val2 integer, val3 integer ); insert into t1 select generate_series(1,10) as id1; insert into t2 select generate_series(1,100) as id2, generate_series(1,10) as id1; Then three concurrent transaction start. /*1*/ begin; /*1*/ update t2 set val1=1 where id2=50; /*1*/ update t2 set val2=2 where id2=50; /*2*/ begin; /*2*/ update t2 set val1=1 where id2=40; /*2*/ update t2 set val2=2 where id2=40; /*2*/ commit; /*3*/ begin; /*3*/ update t1 set val1=1 where id1=10; /*1*/ update t2 set val3=3 where id2=50; Here we have deadlock for transactions 1 and 3. Is it bug? And if so, will it be fixed? Or is it not a bug? What should I do then? Thank you. Sorry for bad english. -- Alexey A. Nalbat Price Express http://www.price.ru/ http://www.tyndex.ru/
Alexey Nalbat <nalbat@price.ru> writes: > I've encountered deadlock on postgresql 8.1. Here is the simple example. Your example doesn't deadlock for me ... regards, tom lane
On Mon, 2007-04-09 at 04:24, Alexey Nalbat wrote: > Hello. > > I've encountered deadlock on postgresql 8.1. Here is the simple example. > > create table t1 ( > id1 integer primary key, > val1 integer > ); > create table t2 ( > id2 integer primary key, > id1 integer references t1 on delete cascade, > val1 integer, > val2 integer, > val3 integer > ); > insert into t1 select > generate_series(1,10) as id1; > insert into t2 select > generate_series(1,100) as id2, > generate_series(1,10) as id1; > > Then three concurrent transaction start. > > /*1*/ begin; > /*1*/ update t2 set val1=1 where id2=50; > /*1*/ update t2 set val2=2 where id2=50; > /*2*/ begin; > /*2*/ update t2 set val1=1 where id2=40; > /*2*/ update t2 set val2=2 where id2=40; > /*2*/ commit; > /*3*/ begin; > /*3*/ update t1 set val1=1 where id1=10; > /*1*/ update t2 set val3=3 where id2=50; > > Here we have deadlock for transactions 1 and 3. That's not a deadlock, transaction 3 is simply waiting for transaction 1 to commit or rollback. If you run a commit or rollback on transaction 1 then transaction 3 will then be ready to commit or rollback as needed.
Alexey Nalbat <nalbat@price.ru> writes: > Tom Lane wrote: >> Your example doesn't deadlock for me ... > With default value "deadlock_timeout=1000" error raises in first transaction: Then there's a typo in your example --- please recheck it. regards, tom lane
Tom Lane wrote: > > Your example doesn't deadlock for me ... Scott Marlowe wrote: > > That's not a deadlock, transaction 3 is simply waiting for transaction 1 > to commit or rollback. > > If you run a commit or rollback on transaction 1 then transaction 3 will > then be ready to commit or rollback as needed. With default value "deadlock_timeout=1000" error raises in first transaction: ERROR: deadlock detected DETAIL: Process 31712 waits for ShareLock on tuple (0,10) of relation 451542 of database 391598; blocked by process 31786. Process 31786 waits for ShareLock on transaction 918858; blocked by process 31712. CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."t1" x WHERE "id1" = $1 FOR SHARE OF x" After setting "deadlock_timeout=3600000" we can see deadlock in pg_locks: datid | datname | procpid | usesysid | usename | current_query | query_start | backend_start | client_addr | client_port --------+---------+---------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+-------------+------------- 391598 | nalbat | 32025 | 16384 | nalbat | /*1*/ update t2 set val3=3 where id2=50; | 2007-04-13 11:14:06.966372+04| 2007-04-13 11:13:11.018896+04 | | -1 391598 | nalbat | 32029 | 16384 | nalbat | /*3*/ update t1 set val1=1 where id1=10; | 2007-04-13 11:13:58.607838+04| 2007-04-13 11:13:17.212922+04 | | -1 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+------------------+--------- tuple | 391598 | 451542 | 0 | 10 | | | | | 918867 | 32025 |ShareLock | f tuple | 391598 | 451542 | 0 | 10 | | | | | 918869 | 32029 |ExclusiveLock | t relation | 391598 | 451542 | | | | | | | 918867 | 32025 |AccessShareLock | t relation | 391598 | 451542 | | | | | | | 918867 | 32025 |RowShareLock | t relation | 391598 | 451542 | | | | | | | 918869 | 32029 |AccessShareLock | t relation | 391598 | 451542 | | | | | | | 918869 | 32029 |RowExclusiveLock | t relation | 391598 | 451544 | | | | | | | 918867 | 32025 |AccessShareLock | t relation | 391598 | 451544 | | | | | | | 918869 | 32029 |AccessShareLock | t relation | 391598 | 451544 | | | | | | | 918869 | 32029 |RowExclusiveLock | t relation | 391598 | 451546 | | | | | | | 918867 | 32025 |AccessShareLock | t relation | 391598 | 451546 | | | | | | | 918867 | 32025 |RowExclusiveLock | t relation | 391598 | 451548 | | | | | | | 918867 | 32025 |AccessShareLock | t relation | 391598 | 451548 | | | | | | | 918867 | 32025 |RowExclusiveLock | t transactionid | | | | | 918867 | | | | 918867 | 32025 |ExclusiveLock | t transactionid | | | | | 918869 | | | | 918869 | 32029 |ExclusiveLock | t transactionid | | | | | 918867 | | | | 918869 | 32029 |ShareLock | f relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid| reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys| relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl ---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+-------- t1 | 2200 | 451543 | 16384 | 0 | 451542 | 0 | 0 | 0 | 0| 0 | t | f | r | 2 | 0 | 2 | 0 | 0 | 0 | f | t | f | f | t1_pkey | 2200 | 0 | 16384 | 403 | 451544 | 0 | 1 | 0 | 0| 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | t2 | 2200 | 451547 | 16384 | 0 | 451546 | 0 | 0 | 0 | 0| 0 | t | f | r | 5 | 0 | 2 | 0 | 0 | 0 | f | t | f | f | t2_pkey | 2200 | 0 | 16384 | 403 | 451548 | 0 | 1 | 0 | 0| 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | I have installed PostgreSql 8.1.4 on SUSE Linux 10.1. version ------------------------------------------------------------------------------------- PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux) $ cat /etc/SuSE-release SUSE LINUX 10.1 (i586) VERSION = 10.1 $ uname -a Linux geryon 2.6.16.21-0.25-default #1 Tue Sep 19 07:26:15 UTC 2006 i686 athlon i386 GNU/Linux -- Alexey A. Nalbat Price Express http://www.price.ru/ http://www.tyndex.ru/
I've asked russian postgresql users to proceed my deadlock example. http://sql.ru/forum/actualthread.aspx?tid=418296 Here is the results. OS, version, build PG version, build Status (Person) SUSE Linux 10.1 8.1.4 from official SUSE distribution deadlock between /*1*/ and /*3*/ detected (Nalbat) SUSE Linux 10.1 8.2.3 self-made package /*3*/ waits for /*1*/, deadlock not detected (Nalbat) itanium2(Debian) 8.2.3 deadlock not detected (SergeyK) laptop(Slackware) 8.2.3 deadlock not detected (SergeyK) SUSE 10.0 8.1.4 self-made deadlock detected (SergeyK) Windows 2000 Pro Rus 8.0.4 /*2*/ waits, deadlock not detected (ZemA) Windows 2000 Pro Rus 8.1.3 deadlock detected (ZemA) win xp sp2 8.2.0, i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) /*3*/ waits for /*1*/, deadlock not detected (st_serg) It seems that deadlock rises for Postgresql 8.1 at any operating system. Tom Lane wrote: > Alexey Nalbat <nalbat@price.ru> writes: > > Tom Lane wrote: > >> Your example doesn't deadlock for me ... > > > With default value "deadlock_timeout=1000" error raises in first transaction: > > Then there's a typo in your example --- please recheck it. -- Alexey A. Nalbat Price Express http://www.price.ru/ http://www.tyndex.ru/