From: justin <jtyme@intrusic.com>
To: pgsql-sql@postgresql.org
Subject: Serialized Transaction Locking Issues
Date: Tue, 04 Feb 2003 17:10:06 -0500
Message-ID: <3E403A3E.5020508@intrusic.com> (raw)
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#LOCKIN...),
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jtyme@intrusic.com
Subject: Re: Serialized Transaction Locking Issues
In-Reply-To: <3E403A3E.5020508@intrusic.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox