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
