Thread: possible to lock a single row in table?

possible to lock a single row in table?

From
bboett@erm1.u-strasbg.fr (Bruno Boettcher)
Date:
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....


Re: possible to lock a single row in table?

From
Peter Eisentraut
Date:
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



Re: possible to lock a single row in table?

From
Stephan Szabo
Date:
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.