pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I know if a row is Locked?
3+ messages / 3 participants
[nested] [flat]
* How can I know if a row is Locked?
@ 2007-04-13 11:27 Carlos Santos <carloscompels@yahoo.com.br>
0 siblings, 2 replies; 3+ messages in thread
From: Carlos Santos @ 2007-04-13 11:27 UTC (permalink / raw)
To: pgsql-sql
How can I know if a row is locked by another transaction.
I have in a transaction like that:
BEGIN;
SELECT * FROM compels.teste WHERE id = '1' FOR UPDATE;
PS1: where id is the primary key.
PS2: The COMMIT command is done after a long time.
In this case the row with the primary-key equals to '1' will be locked to other transactions until the COMMIT command be executed.
So, how can I detect if this row is locked?
Tks
Carlos Henrique Iazzetti Santos
Compels Informática
Santa Rita do Sapucaí - MG
www.compels.net
__________________________________________________
Fale com seus amigos de graça com o novo Yahoo! Messenger
http://br.messenger.yahoo.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can I know if a row is Locked?
@ 2007-04-13 11:48 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Carlos Santos <carloscompels@yahoo.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Marcin Stępnicki @ 2007-04-13 11:48 UTC (permalink / raw)
To: pgsql-sql
Dnia Fri, 13 Apr 2007 04:27:34 -0700, Carlos Santos napisał(a):
> How can I know if a row is locked by another transaction.
> I have in a transaction like that:
>
> BEGIN;
> SELECT * FROM compels.teste WHERE id = '1' FOR UPDATE;
>
> PS1: where id is the primary key.
> PS2: The COMMIT command is done after a long time.
I've a feeling that you're trying to do a BadThing(tm). It shouldn't be
necessary unless you've got some very specific needs.
To quote Tom Lane:
---
> Is there a recommended "postgres way" to determine if a certain row is
> locked... without blocking?
8.1 has a SELECT FOR UPDATE NOWAIT option. Alternatively, just do a
wait while having a very short statement_timeout.
> In my custom postgres client app I'd like to be able to determine if
> another user is "modifying" a given record. If so, I would present a
> dialog to the user such as "Record Locked. Sam Smith is already
> modifying this record. Try again later."
However, I think the question is moot because it's predicated on a
terrible underlying approach. You should NEVER design a DB app to hold
a lock while some user is editing a record (and answering the phone,
going out to lunch, etc). Fetch the data and then let the user edit
it while you are not in a transaction. When he clicks UPDATE, do
BEGIN;
SELECT the row FOR UPDATE;
check for any changes since you fetched the data originally
if none, UPDATE and commit
else rollback and tell user about it
If you do see conflicting changes, then you have enough info to resolve
the conflicts or abandon the update.
---
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
| So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can I know if a row is Locked?
@ 2007-04-13 11:48 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Carlos Santos <carloscompels@yahoo.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: A. Kretschmer @ 2007-04-13 11:48 UTC (permalink / raw)
To: pgsql-sql
am Fri, dem 13.04.2007, um 4:27:34 -0700 mailte Carlos Santos folgendes:
> How can I know if a row is locked by another transaction.
> I have in a transaction like that:
>
> BEGIN;
> SELECT * FROM compels.teste WHERE id = '1' FOR UPDATE;
>
> PS1: where id is the primary key.
> PS2: The COMMIT command is done after a long time.
>
> In this case the row with the primary-key equals to '1' will be locked to other
> transactions until the COMMIT command be executed.
> So, how can I detect if this row is locked?
You can call the insert with 'for update nowait' and catch the error, if
an error occurs.
Maybe there are better solutions...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-13 11:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-13 11:27 How can I know if a row is Locked? Carlos Santos <carloscompels@yahoo.com.br>
2007-04-13 11:48 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-04-13 11:48 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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