Re: Row level locking

I agree that select for update will be the best choice but it does not have any effect with insert statements.

if you try the following sequence in two different transactions, the select for update will succeed but the insert will be blocked.

begin transaction;
select employee_id from employees where employee_id=3 for update;
insert into employees values (3,33);

create table employees
(employee_id integer primary key,
other_id integer);

Michael Fuhr <mike(at)fuhr(dot)org> escreveu:
On Wed, Oct 12, 2005 at 09:30:50PM +0800, Lincoln Yeoh wrote:
> will "lock sometable nowait" help?
>
> http://developer.postgresql.org/docs/postgres/sql-lock.html
>
> If it fails, something is in progress.

And if it succeeds then it might hurt the performance of concurrent
transactions, depending on which lock mode you use. Using SELECT
FOR UPDATE with a statement timeout might have less impact if you
only need to work with certain rows, and 8.1 will have SELECT FOR
{ UPDATE | SHARE } [ NOWAIT ].

Carlos, you said the applications were doing only inserts. Is that
right? Have you investigated what's causing the blocking? Do the
rows you're inserting have foreign key references? Let's find out
what the problem is before suggesting how to solve it.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

---------------------------------
Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e concorre a mais de 500 prêmios! Participe!