pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimulating a SELECT..FOR UPDATE to LOCK and SELECT statement
2+ messages / 2 participants
[nested] [flat]
* Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
@ 2002-10-24 04:10 Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ludwig Lim @ 2002-10-24 04:10 UTC (permalink / raw)
To: pgsql-sql
Hi:
Is there a way to emulate a SELECT..FOR UPDATE to
series of LOCK/SELECT statement.
I tried the following statements using 2 psql
terminals.
T1 | T2
1) BEGIN; |
2) SELECT x | BEGIN;
FROM y |
WHERE y=1 |
FOR UPDATE; |
3) | SELECT x
| FROM y
| WHERE y=1
| FOR UPDATE;
4) COMMIT; |
5) | COMMIT;
At point #3 T2 will wait, however changing the WHERE
clause to other clause such as "WHERE y=2" will allow
T2 to proceed.
- I tried changing the SELECT..FOR UPDATE into LOCK
SHARE MODE followed by a SELECT (but w/o FOR UPDATE)
but it T2 is allowed to proceed even for the clause
"where y=1".
I am surprised because according to the docs
(version 7.2), it says:
ROW SHARE MODE
Note: Automatically acquired by SELECT ... FOR
UPDATE.
I'm assuming that the SELECT..FOR UPDATE performs
a lock in ROW SHARE MODE before the SELECT.
I also tried changing the lock mode into SHARE ROW
EXCLUSIVE MODE / EXCLUSIVE MODE but still T2 won't be
allowed to passed even the for the clause "where y=2".
Is there any to do it? I'm asking becuase our db
libaries (using libpq) always a cursor when generating
a SELECT statement thus I'm encourtering the following
error message when I use SELECT..FOR UPDATE:
Cursor must be READ ONLY.
DECLARE/UPDATE is not supported.
Another alternative would be studying libpq and
removing the cursors in a SELECT statement.
By the way is there any side effect / disadavtages
when I remove the "DELCARE CURSOR" statement and
change it to plain SELECT statememt in C++ codes?
Thank you in advance,
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
@ 2002-10-24 13:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-10-24 13:42 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Ludwig Lim <lud_nowhere_man@yahoo.com> writes:
> Is there a way to emulate a SELECT..FOR UPDATE to
> series of LOCK/SELECT statement.
No. LOCK can only acquire table-level locks; the point of SELECT FOR
UPDATE is to acquire row-level locks.
> I am surprised because according to the docs
> (version 7.2), it says:
> ROW SHARE MODE
> Note: Automatically acquired by SELECT ... FOR
> UPDATE.
SELECT FOR UPDATE gets a table-level lock just to ensure that the table
doesn't disappear from under it while it's doing its scan. The ROW
SHARE lock is pretty weak though, and doesn't block anything less
drastic than DROP/ALTER TABLE or VACUUM FULL.
The 7.3 development docs are perhaps clearer about this; try
http://developer.postgresql.org/docs/postgres/explicit-locking.html
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-24 13:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-24 04:10 Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-10-24 13:42 ` Re: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement 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