pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNon-Blocking Locks (i.e. Oracle NOWAIT)
5+ messages / 5 participants
[nested] [flat]
* Non-Blocking Locks (i.e. Oracle NOWAIT)
@ 2003-07-15 12:44 Jan Bernhardt <jb@baltic-online.de>
2003-07-15 15:16 ` Re: Non-Blocking Locks (i.e. Oracle NOWAIT) Dmitry Tkach <dmitry@openratings.com>
2003-07-15 15:17 ` Re: Non-Blocking Locks (i.e. Oracle NOWAIT) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Jan Bernhardt @ 2003-07-15 12:44 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-Blocking Locks (i.e. Oracle NOWAIT)
2003-07-15 12:44 Non-Blocking Locks (i.e. Oracle NOWAIT) Jan Bernhardt <jb@baltic-online.de>
@ 2003-07-15 15:16 ` Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dmitry Tkach @ 2003-07-15 15:16 UTC (permalink / raw)
To: Jan Bernhardt <jb@baltic-online.de>; +Cc: 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-Blocking Locks (i.e. Oracle NOWAIT)
2003-07-15 12:44 Non-Blocking Locks (i.e. Oracle NOWAIT) Jan Bernhardt <jb@baltic-online.de>
@ 2003-07-15 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-07-15 15:17 UTC (permalink / raw)
To: Jan Bernhardt <jb@baltic-online.de>; +Cc: pgsql-sql
Jan Bernhardt <jb@baltic-online.de> writes:
> 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.
You might be able to use the contrib/userlock module for this.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-Blocking Locks (i.e. Oracle NOWAIT)
@ 2003-07-15 14:54 Christoph Haller <ch@rodos.fzk.de>
2003-07-16 03:17 ` Re: Non-Blocking Locks (i.e. Oracle NOWAIT) Ang Chin Han <angch@bytecraft.com.my>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Haller @ 2003-07-15 14:54 UTC (permalink / raw)
To: pgsql-sql; jb@baltic-online.de
>
> 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?
>
PostgreSQL 7.3.2
T1: begin; select * from foo for update;
T2: set STATEMENT_TIMEOUT = 1000; -- milliseconds
T2: select * from foo for update;
T2: ERROR: Query was cancelled.
HTH, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Non-Blocking Locks (i.e. Oracle NOWAIT)
2003-07-15 14:54 Re: Non-Blocking Locks (i.e. Oracle NOWAIT) Christoph Haller <ch@rodos.fzk.de>
@ 2003-07-16 03:17 ` Ang Chin Han <angch@bytecraft.com.my>
0 siblings, 0 replies; 5+ messages in thread
From: Ang Chin Han @ 2003-07-16 03:17 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; jb@baltic-online.de
Christoph Haller wrote:
> PostgreSQL 7.3.2
> T1: begin; select * from foo for update;
> T2: set STATEMENT_TIMEOUT = 1000; -- milliseconds
Seems like setting it to 1 (ms) emulates the NOWAIT condition better.
Can't set it to 0, though.
> T2: select * from foo for update;
> T2: ERROR: Query was cancelled.
How picky are we in borrowing syntax and idiocyncracies (?) from other
DBs? Since we can closely emulate Oracle's behaviour on SELECT ... FOR
UPDATE NOWAIT, should we just add NOWAIT to the next (non-feature frozen
version) of postgres?
--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
10:30am up 202 days, 1:35, 6 users, load average: 5.40, 5.14, 5.05
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-16 03:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-15 12:44 Non-Blocking Locks (i.e. Oracle NOWAIT) Jan Bernhardt <jb@baltic-online.de>
2003-07-15 15:16 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-15 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 14:54 Re: Non-Blocking Locks (i.e. Oracle NOWAIT) Christoph Haller <ch@rodos.fzk.de>
2003-07-16 03:17 ` Ang Chin Han <angch@bytecraft.com.my>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox