Re: Deadlock Problem

> Matthias Schmitt <freak002(at)mmp(dot)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