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 |
