pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRows with exclusive lock
7+ messages / 4 participants
[nested] [flat]
* Rows with exclusive lock
@ 2006-07-22 14:38 Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Marques @ 2006-07-22 14:38 UTC (permalink / raw)
To: pgsql-sql
Is it posible to get an exclusive (read/write) lock on certain rows? I
don't want to block the whole table, only certain rows, but I want it to
be a read/write lock.
AFAIK SELECT FOR UPDATE doesn't help with this.
Do I have to go for another aproche?
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-22 14:42 Alvaro Herrera <alvherre@commandprompt.com>
parent: Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2006-07-22 14:42 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
Martin Marques escribió:
> Is it posible to get an exclusive (read/write) lock on certain rows? I
> don't want to block the whole table, only certain rows, but I want it to
> be a read/write lock.
That's what SELECT FOR UPDATE does.
> AFAIK SELECT FOR UPDATE doesn't help with this.
Why?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-22 14:55 Martin Marques <martin@bugs.unl.edu.ar>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 7+ messages in thread
From: Martin Marques @ 2006-07-22 14:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
On Sat, 22 Jul 2006, Alvaro Herrera wrote:
> Martin Marques escribió:
>> Is it posible to get an exclusive (read/write) lock on certain rows? I
>> don't want to block the whole table, only certain rows, but I want it to
>> be a read/write lock.
>
> That's what SELECT FOR UPDATE does.
Hi Alvaro,
After the SELECT FOR UPDATE other transactions can still see the locked
rows. I want a read/write lock, so no one can access does rows.
>
>> AFAIK SELECT FOR UPDATE doesn't help with this.
>
> Why?
trans1:
prueba2=> BEGIN;
BEGIN
prueba2=> SELECT * FROM personas FOR UPDATE;
codigo | nombre | apellido | tipodoc | docnum
--------+--------+----------+---------+----------
3 | Martin | Marques | 1 | 23622139
(1 row)
Meanwhile, at this moment trans2:
prueba2=> BEGIN;
BEGIN
prueba2=> SELECT * FROM personas;
codigo | nombre | apellido | tipodoc | docnum
--------+--------+----------+---------+----------
3 | Martin | Marques | 1 | 23622139
(1 row)
pg_locks shows the the lock is RowShareLock, so there is no read lock on
those rows, which is what I want.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-23 15:28 Florian Weimer <fw@deneb.enyo.de>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 0 replies; 7+ messages in thread
From: Florian Weimer @ 2006-07-23 15:28 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-sql
* Martin Marques:
>> That's what SELECT FOR UPDATE does.
>
> Hi Alvaro,
>
> After the SELECT FOR UPDATE other transactions can still see the
> locked rows. I want a read/write lock, so no one can access does rows.
You should probably run the other transactions at SERIALIZABLE level.
I suppose this will make them wait for the completion of the update.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-23 16:10 Alvaro Herrera <alvherre@commandprompt.com>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2006-07-23 16:10 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
Martin Marques escribió:
> On Sat, 22 Jul 2006, Alvaro Herrera wrote:
>
> >Martin Marques escribió:
> >>Is it posible to get an exclusive (read/write) lock on certain rows? I
> >>don't want to block the whole table, only certain rows, but I want it to
> >>be a read/write lock.
> >
> >That's what SELECT FOR UPDATE does.
>
> Hi Alvaro,
>
> After the SELECT FOR UPDATE other transactions can still see the locked
> rows. I want a read/write lock, so no one can access does rows.
SELECT FOR UPDATE acquires an exclusive lock, but other transactions
must try to acquire a lock on the rows as well, or they won't be locked.
You can try using SELECT FOR SHARE (new as of 8.1) if you want some
transactions to hold shared (read) locks.
IOW, SELECT FOR UPDATE blocks other SELECTs FOR UPDATE and SELECTs FOR
SHARE, but it does not block plain SELECT.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-24 11:28 Martin Marques <martin@bugs.unl.edu.ar>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Marques @ 2006-07-24 11:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
On Sun, 23 Jul 2006, Alvaro Herrera wrote:
> Martin Marques escribió:
>>
>> After the SELECT FOR UPDATE other transactions can still see the locked
>> rows. I want a read/write lock, so no one can access does rows.
>
> SELECT FOR UPDATE acquires an exclusive lock, but other transactions
> must try to acquire a lock on the rows as well, or they won't be locked.
> You can try using SELECT FOR SHARE (new as of 8.1) if you want some
> transactions to hold shared (read) locks.
Sorry for not getting it clear the first time.
What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but
at row level.
> IOW, SELECT FOR UPDATE blocks other SELECTs FOR UPDATE and SELECTs FOR
> SHARE, but it does not block plain SELECT.
So, this is not posible. :-(
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rows with exclusive lock
@ 2006-07-26 19:25 Andrew Hammond <andrew.george.hammond@gmail.com>
parent: Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Hammond @ 2006-07-26 19:25 UTC (permalink / raw)
To: pgsql-sql
Martin Marques wrote:
> On Sun, 23 Jul 2006, Alvaro Herrera wrote:
>
> > Martin Marques escribió:
> >>
> >> After the SELECT FOR UPDATE other transactions can still see the locked
> >> rows. I want a read/write lock, so no one can access does rows.
> >
> > SELECT FOR UPDATE acquires an exclusive lock, but other transactions
> > must try to acquire a lock on the rows as well, or they won't be locked.
> > You can try using SELECT FOR SHARE (new as of 8.1) if you want some
> > transactions to hold shared (read) locks.
>
> Sorry for not getting it clear the first time.
>
> What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but
> at row level.
Well... you could change your other selects to use FOR UPDATE as well,
even if they're not going to be updating (but this block concurrent
reads). You're probably better to take Alvaro's suggestion above and
use SELECT FOR SHARE.
Drew
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-07-26 19:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-22 14:38 Rows with exclusive lock Martin Marques <martin@bugs.unl.edu.ar>
2006-07-22 14:42 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-22 14:55 ` Martin Marques <martin@bugs.unl.edu.ar>
2006-07-23 15:28 ` Florian Weimer <fw@deneb.enyo.de>
2006-07-23 16:10 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-07-24 11:28 ` Martin Marques <martin@bugs.unl.edu.ar>
2006-07-26 19:25 ` Andrew Hammond <andrew.george.hammond@gmail.com>
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