Serialized Transaction Locking Issues - Mailing list pgsql-sql

From justin
Subject Serialized Transaction Locking Issues
Date
Msg-id 3E403A3E.5020508@intrusic.com
Whole thread Raw
Responses Re: Serialized Transaction Locking Issues
List pgsql-sql
Hello,

I'm currently in the midst of working on a serializable transaction 
which drops indexes on several tables, does a bulk copy, and rebuilds 
the indexes. Based on what I've read it seemed as though I'd be able to 
concurrently execute read only queries against these tables, returning 
results based on the table snapshot from before the serialized 
transaction began. However, this doesn't work. A simple read-only select 
query waits until the serialized transaction is finished before 
returning results.

Based on the user documentation, specifically 
(http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-locking.html#LOCKING-TABLES), 
it would seem that the only issue in PostgreSQL that could block a 
simple select call would be an ACCESS EXCLUSIVE lock, which is acquired 
only by DROP TABLE, ALTER TABLE, and VACUUM FULL, none of which I'm 
using. In fact, I've noticed this exact behavior with DROP INDEX.

Please excuse my futile attempt to outline two concurrent transactions 
here:

testdb=# \d trans_test
Table "public.trans_test"
Column | Type | Modifiers
- -------+--------+-----------
val integer
Indexes: idx_trans_test btree(val)

testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL 
SERIALIZABLE;
SET

[TRANSACTION 1] BEGIN;
BEGIN
[TRANSACTION 1] SELECT * FROM trans_test;
val
- -----
1
2

[TRANSACTION 2] SELECT * FROM trans_test;
val
- -----
1
2

[TRANSACTION 1] DROP INDEX idx_trans_test;
DROP INDEX

[TRANSACTION 2] SELECT * FROM trans_test;
... (Waiting)

[TRANSACTION 1] COMMIT;
COMMIT

(TRANSACTION 2 returns after TRANSACTION 1 COMMIT)
val
- -----
1
2

So is this a glitch or am I missing some nuance of a serializable 
transaction? In either case I'm eager to figure out whats actually going 
on.

Thanks,

-justin





pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: pg_views
Next
From: Jan Wieck
Date:
Subject: Re: iceberg queries