Re: Non-Blocking Locks (i.e. Oracle NOWAIT) - Mailing list pgsql-sql

There is no such thing, as far as I know :-(


Here is the poor man solution  I used to emulate this 'nowait' behaviour:

create table master_lock
(  projectid text primary key,  locker int
);


Now, the application first acquires an exclusive lock on the table,
then, while the table is locked it looks at the projectid row and tests 
the 'locker' column. If it is null, the app sets it to its connection id 
(the pid of the backend),
and COMMITS (to release the table lock), then starts another transaction 
and goes about its business, after it is done, it updates the 
master_lock, and sets the locker back to null.
If the locker column is not null, I use pg_stat_activity to test if the 
connection with this pid still exists (in case the app that locked this 
project had crashed before releasing the lock)... if the pid isn't 
there, it considers the project unlock, and does what's described above. 
Otherwise, it releases the lock on the table, and generates an error 
message, reporting that the project is locked.

Also see the earlier reply to your message, about STATEMENT_TIMEOUT as 
an alternative... I am using 7.2, so this wasn't an option for me when I 
came up with this work around....
I doubt, I'd use that timeout thing if I was on 7.3 anyway though - one 
problem is, you'd have to parse the actual error message to figure out 
if the cause of the error is really a timeout, or just something bad 
happenning in the database... Another problem is that the timeout number 
is arbitrary - if it is too small, you risk to get a situation when the 
row is not locked, but the query still gets canceled, because the 
database is slow at the moment, if it is too large, you'll have to wait 
for a long time before getting the response (and even then, you can't  
be 100% sure it really happened because of the lock)

I hope, it helps..

Dima


Jan Bernhardt wrote:

>Hi there,
>
>though this question has been asked several times before (but never really
>answered), I have to give it another try.
>
>I have a multi-user application which synchronizes access to certain datasets
>via the database itself. If a user has a project in that application open no
>other user should be able to work on it too. When developing the application I
>considered the database to be a place to handle the synchronization since
>transactions are (normally) an integral part of a database system. When a user
>opens a project the application firstly locks a corresponding row. But since I
>don't want the application to block if that row is already locked I use the
>Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive
>the lock on the specified row and if that row is already locked it returns
>with an error without blocking and I can tell the user that the project is
>already in use.
>
>Now that the application is to be ported to PG I need a similar functionality.
>Is there that an animal? If not, what would you recommend?
>
>TIA,
>
>- Jan.
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>  
>