Row level locking

On Wed, Oct 12, 2005 at 04:11:00AM +0000, Carlos Benkendorf wrote:

How can I know which rows are locked?

There's no query that says "show me all rows that are locked"; even
if there was, it would be out of date by the time you got the
results. PostgreSQL 8.1 will enhance pg_locks to show tuple locks
in certain cases but it won't show all tuple locks.

Why do you want to know? What are you trying to do?

--
Michael Fuhr

We have applications that are sometimes locked by a long time application and I would like to avoid these by coding something to discover if the rows are locked before working with them.

Both applications use only inserts in a table with a primary key.

I�m sure that it is not the rigth approach, but I could not figure something better. Any suggestions?

Benkendorf

Michael Fuhr <mike@fuhr.org> escreveu:
On Wed, Oct 12, 2005 at 04:11:00AM +0000, Carlos Benkendorf wrote:

How can I know which rows are locked?

There's no query that says "show me all rows that are locked"; even
if there was, it would be out of date by the time you got the
results. PostgreSQL 8.1 will enhance pg_locks to show tuple locks
in certain cases but it won't show all tuple locks.

Why do you want to know? What are you trying to do?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

will "lock sometable nowait" help?

http://developer.postgresql.org/docs/postgres/sql-lock.html

If it fails, something is in progress.

I believe there are also statement timeouts.

Regards,
Link.

At 11:56 AM 10/12/2005 +0000, Carlos Benkendorf wrote:

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

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@fuhr.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!

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