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