Re: Rows with exclusive lock

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
---------------------------------------------------------