pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
possible to lock a single row in table?
3+ messages / 3 participants
[nested] [flat]

* possible to lock a single row in table?
@ 2001-06-20 14:46  Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  0 siblings, 2 replies; 3+ messages in thread

From: bboett@erm1.u-strasbg.fr @ 2001-06-20 14:46 UTC (permalink / raw)
  To: pgsql-sql

Hello!
i have a lots of threads that work on a table, making insertions,
updates removes.....

now i certain cases its important to keep data integrity, so i looked
into locks....


all i found was a lock function that locks the entire table....

now even in shared mode, if i understand it well this means that as long
as the locking procedure isn't finished, no other thread can update or
delete data, right? Thats really harsh, since there are really an awful
lot of threads....

isn't it possible to lock only a specific row?

and what happens to other insert queries whilst the lock is operational?
Are they postponed and the valling thread waits, or does the call return
with an error?

-- 
ciao bboett
==============================================================
bboett@earthling.net
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===============================================================
the total amount of intelligence on earth is constant.
human population is growing....



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: possible to lock a single row in table?
@ 2001-06-20 15:54  Peter Eisentraut <peter_e@gmx.net>
  parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  1 sibling, 0 replies; 3+ messages in thread

From: Peter Eisentraut @ 2001-06-20 15:54 UTC (permalink / raw)
  To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql

Bruno Boettcher writes:

> i have a lots of threads that work on a table, making insertions,
> updates removes.....
>
> now i certain cases its important to keep data integrity, so i looked
> into locks....

Only in certain cases??? ;-)

> all i found was a lock function that locks the entire table....

There are also row level locks, which are automatically acquired when
needed.  The best way to lock specific rows is the SELECT ... FOR UPDATE
statement.  Playing with the LOCK command is generally not necessary.

> and what happens to other insert queries whilst the lock is operational?
> Are they postponed and the valling thread waits, or does the call return
> with an error?

That depends on the transaction isolation level (which you can set).  See
the chapter on concurrency control in the User's Guide for more
information.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: possible to lock a single row in table?
@ 2001-06-20 16:43  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-06-20 16:43 UTC (permalink / raw)
  To: Bruno Boettcher <bboett@erm1.u-strasbg.fr>; +Cc: pgsql-sql


On Wed, 20 Jun 2001, Bruno Boettcher wrote:

> i have a lots of threads that work on a table, making insertions,
> updates removes.....
> 
> now i certain cases its important to keep data integrity, so i looked
> into locks....

What kind of data integrity are you trying to implement?

> all i found was a lock function that locks the entire table....
> 
> now even in shared mode, if i understand it well this means that as long
> as the locking procedure isn't finished, no other thread can update or
> delete data, right? Thats really harsh, since there are really an awful
> lot of threads....
> 
> isn't it possible to lock only a specific row?

Yes.  Look at SELECT ... FOR UPDATE.  That should lock those rows
matched such that another transaction that attempts to select for update,
update or delete those rows waits until the locking transaction finishes.

> and what happens to other insert queries whilst the lock is operational?
> Are they postponed and the valling thread waits, or does the call return
> with an error?

IIRC, postponed until the transaction that made the lock commits or
rolls back.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-06-20 16:43 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-20 14:46 possible to lock a single row in table? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2001-06-20 15:54 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-20 16:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.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