Re: Deadlock Problem - Mailing list pgsql-general

<excerpt><fontfamily><param>Courier</param><x-tad-smaller>Matthias
Schmitt <<freak002@mmp.lu> writes:

</x-tad-smaller><excerpt><x-tad-smaller>I am in trouble with more and
more deadlock problems. We are

programming a web application with multiple users editing content at

the same time. Multiple times a day PostgreSQL runs into a deadlock,

which can only be resolved by killing some of the clients. Here is an

example from the pg_locks table:

</x-tad-smaller></excerpt><x-tad-smaller>

All of the rows you showed us have granted=t.  No blockage is evident,

let alone any deadlock.

</x-tad-smaller></fontfamily></excerpt><fontfamily><param>Courier</param><x-tad-smaller>

Hello,


we tried to reduce the possible error sources. So we logged the last
statements sent to the database and were able to reproduce our problem
with psql alone. I did the following in two psql shell environments:


shell no 1:


CREATE TABLE the_test (

id   int4 PRIMARY KEY,

name varchar(32)

);


insert into the_test values (1, 'hello world');


begin;

update the_test set name = 'still alive' where id = 1;


To keep the transaction open I did not issue any commit or rollback
command.


shell no 2:


</x-tad-smaller><x-tad-smaller>begin;

update the_test set name = 'still alive' where id = 1;

</x-tad-smaller><x-tad-smaller>

The second shell hangs now forever. The pg_locks table shows:


select * from pg_locks;

 relation | database | transaction |  pid  |       mode       |
granted

----------+----------+-------------+-------+------------------+---------

  1980976 |  1980969 |             | 16034 | AccessShareLock  | t

    16757 |        1 |             | 16100 | AccessShareLock  | t

          |          |      762472 | 16036 | ExclusiveLock    | t

          |          |      762473 | 16034 | ExclusiveLock    | t

          |          |      762472 | 16034 | ShareLock        | f

  1980974 |  1980969 |             | 16036 | AccessShareLock  | t

  1980974 |  1980969 |             | 16036 | RowExclusiveLock | t

          |          |      762478 | 16100 | ExclusiveLock    | t

  1980974 |  1980969 |             | 16034 | AccessShareLock  | t

  1980974 |  1980969 |             | 16034 | RowExclusiveLock | t

(10 rows)


In our applications it is possible that multiple records of different
tables are updated in different sequences, depending on the task to
fulfill. Shouldn't a time-out error resolve those problems?


Thank you.


Matthias Schmitt


</x-tad-smaller><x-tad-smaller>

</x-tad-smaller><x-tad-smaller>magic moving pixel s.a.    Phone: +352
54 75 75 - 0

Technoport Schlassgoart    Fax  : +352 54 75 75 - 54

66, rue de Luxembourg      URL  : http://www.mmp.lu

L-4221 Esch-sur-Alzette

</x-tad-smaller><x-tad-smaller>

</x-tad-smaller></fontfamily>
> Matthias Schmitt <freak002@mmp.lu> writes:
>> I am in trouble with more and more deadlock problems. We are
>> programming a web application with multiple users editing content at
>> the same time. Multiple times a day PostgreSQL runs into a deadlock,
>> which can only be resolved by killing some of the clients. Here is an
>> example from the pg_locks table:
>
> All of the rows you showed us have granted=t.  No blockage is evident,
> let alone any deadlock.

Hello,

we tried to reduce the possible error sources. So we logged the last
statements sent to the database and were able to reproduce our problem
with psql alone. I did the following in two psql shell environments:

shell no 1:

CREATE TABLE the_test (
id   int4 PRIMARY KEY,
name varchar(32)
);

insert into the_test values (1, 'hello world');

begin;
update the_test set name = 'still alive' where id = 1;

To keep the transaction open I did not issue any commit or rollback
command.

shell no 2:

begin;
update the_test set name = 'still alive' where id = 1;

The second shell hangs now forever. The pg_locks table shows:

select * from pg_locks;
  relation | database | transaction |  pid  |       mode       | granted
----------+----------+-------------+-------+------------------+---------
   1980976 |  1980969 |             | 16034 | AccessShareLock  | t
     16757 |        1 |             | 16100 | AccessShareLock  | t
           |          |      762472 | 16036 | ExclusiveLock    | t
           |          |      762473 | 16034 | ExclusiveLock    | t
           |          |      762472 | 16034 | ShareLock        | f
   1980974 |  1980969 |             | 16036 | AccessShareLock  | t
   1980974 |  1980969 |             | 16036 | RowExclusiveLock | t
           |          |      762478 | 16100 | ExclusiveLock    | t
   1980974 |  1980969 |             | 16034 | AccessShareLock  | t
   1980974 |  1980969 |             | 16034 | RowExclusiveLock | t
(10 rows)

In our applications it is possible that multiple records of different
tables are updated in different sequences, depending on the task to
fulfill. Shouldn't a time-out error resolve those problems?

Thank you.

Matthias Schmitt


magic moving pixel s.a.    Phone: +352 54 75 75 - 0
Technoport Schlassgoart    Fax  : +352 54 75 75 - 54
66, rue de Luxembourg      URL  : http://www.mmp.lu
L-4221 Esch-sur-Alzette