Thread: Understanding of LOCK and pg_sleep interaction

Hi,

In trying to setup a test for a LOCK ‘table’ algorithm I attempt to execute two transactions where the first one issues a pg_sleep(10) while ‘table’ is locked and the second one attempts LOCK ‘table’ during the time when the pg_sleep is executing.  When pg_sleep() returns in the first transaction the subsequent statement is not executed.  Meanwhile, the second transaction continues to wait for the lock.  Thus, a deadlock has occurred.  I am doing my testing within PostGreSQL Maestro running as a “script” and issuing BEGIN and COMMIT statements around the desired transaction commands.

I would expect the first transaction to finish following the 10 second sleep at which point the first transaction would be able to start.

PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit

Either script run alone works just fine – it is just when run in tandem as described is neither able to complete.

What am I doing/understanding incorrectly or is this undesirable behavior?

Thanks,

David J.

===============================

--Transaction 1

begin;

delete from locktest;

LOCK locktest;

INSERT INTO locktest (scope, value) VALUES ('TEST','1');

INSERT INTO locktest (scope, value) VALUES ('TEST','2');

select pg_sleep(10);

rollback; --or commit [This doesn’t execute if I begin transaction 2]

>>pg_stat_activity

<IDLE> in transaction

==============================

--Transaction 2

begin;

LOCK locktest; --[This never completes if executed during pg_sleep(10)]

INSERT INTO locktest (scope, value) VALUES ('TEST','3');

commit;

>>pg_stat_activity

LOCK locktest

===============================

>>Attempt at pg_lock results; executed AFTER the 10 second pg_sleep returned.

locktype               database             relation                page      tuple     virtualxid             transactionid      classid   objid      objsubid                virtualtransaction             pid          mode    granted

transactionid                                                                                      101091                                                  15/359  13752    ExclusiveLock     True

relation                623943  853698                                                                                                                  15/359  13752    RowExclusiveLock                True

relation                623943  853698                                                                                                                  15/359  13752    AccessExclusiveLock                True

relation                623943  10985                                                                                                                    18/153  13770    AccessShareLock                True

relation                623943  853696                                                                                                                  15/359  13752    AccessShareLock                True

virtualxid                                                                             18/153                                                                  18/153  13770    ExclusiveLock     True

virtualxid                                                                             15/359                                                                  15/359  13752    ExclusiveLock     True

relation                623943  853702                                                                                                                  15/359  13752    RowExclusiveLock                True

virtualxid                                                                             17/438                                                                  17/438  13754    ExclusiveLock     True

relation                623943  853698                                                                                                                  17/438  13754    AccessExclusiveLock                False