Thread: Rows with exclusive lock
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 ---------------------------------------------------------
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.
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 ---------------------------------------------------------
* 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.
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
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 ---------------------------------------------------------
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