pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Serialized Transaction Locking Issues
3+ messages / 3 participants
[nested] [flat]

* Serialized Transaction Locking Issues
@ 2003-02-03 18:58  Jasper Tymesman <jtyme@bboy.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jasper Tymesman @ 2003-02-03 18:58 UTC (permalink / raw)
  To: 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 on 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 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 


_____________________________________________________________
Sign up for FREE email from bboy.com at http://www.bboy.com

_____________________________________________________________
Select your own custom email address for FREE! Get you@yourchoice.com w/No Ads, 6MB, POP & more! http://www.everyone.net/selectmail?campaign=tag



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Serialized Transaction Locking Issues
@ 2003-02-04 22:10  justin <jtyme@intrusic.com>
  0 siblings, 1 reply; 3+ messages in thread

From: justin @ 2003-02-04 22:10 UTC (permalink / raw)
  To: 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#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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Serialized Transaction Locking Issues
@ 2003-02-04 23:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: justin <jtyme@intrusic.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2003-02-04 23:21 UTC (permalink / raw)
  To: justin <jtyme@intrusic.com>; +Cc: pgsql-sql

justin <jtyme@intrusic.com> writes:
> 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,

Nope, because "drop index" obtains an exclusive lock on the table.
(If it didn't, some other query might try to *use* the index, which
would create a bit of a problem if the DROP committed midway through.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-02-04 23:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-03 18:58 Serialized Transaction Locking Issues Jasper Tymesman <jtyme@bboy.com>
2003-02-04 22:10 Serialized Transaction Locking Issues justin <jtyme@intrusic.com>
2003-02-04 23:21 ` Tom Lane <tgl@sss.pgh.pa.us>

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