Postgres Pro
Downloads
Home   >   mailing lists

Thread: deadlock

deadlock

From
Alexey Nalbat
Date:
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/

Re: deadlock

From
Tom Lane
Date:
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

Re: deadlock

From
Scott Marlowe
Date:
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.

Re: deadlock

From
Tom Lane
Date:
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

Re: deadlock

From
Alexey Nalbat
Date:
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/

Re: deadlock

From
Alexey Nalbat
Date:
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/