Re: deadlock - Mailing list pgsql-general

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/