Re: Row level locking

On Thu, Oct 13, 2005 at 08:32:28AM +0000, Carlos Benkendorf wrote:
> 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);

In this example it seems that you want to know in advance whether
a row that might not be visible to you or that might not even exist
yet will cause some subsequent operation to block. Such a check
is subject to a race condition: the situation could change after
the check but before you act on the result.

If you simply don't want to block for very long then you could use
a statement timeout. If you're using 8.0 or later then you could
use a savepoint to roll back a timed-out operation without aborting
the entire transaction.

If that doesn't help then perhaps you could give us a higher-level
description of the problem you're trying to solve.

--
Michael Fuhr