From carloscompels@yahoo.com.br Fri Apr  13 11:27:41 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 0AA2B9FB405
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:27:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 88280-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:27:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from web57110.mail.re3.yahoo.com (web57110.mail.re3.yahoo.com
	[216.252.111.123])
	by postgresql.org (Postfix) with SMTP id 372BF9FB232
	for <pgsql-sql@postgresql.org>; Fri, 13 Apr 2007 08:27:36 -0300 (ADT)
Received: (qmail 97637 invoked by uid 60001); 13 Apr 2007 11:27:34 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=Sbu6gIAFMnN0D6cyNgpGElHcKw6LYb2JF5nmOjoReODZAFlb8jCjHt5SzopvIo1XnL7anxmRS81TjDEhRgBVg4qJeSFg2CGI+Zqt0WvSsiWa5NGsBpXXebkuO2jyEqfrmXwPlB7dlcww4nS2ch0bOz1esHdaer3RmYUklTtc/JA=;
X-YMail-OSG: 
 e0asH6kVM1kKM_0omCVE7rmdDifLviJGhFD8xi1tOrIzJdSGyla6c3Pl3KjxmG4Uig--
Received: from [201.58.99.64] by web57110.mail.re3.yahoo.com via HTTP;
	Fri, 13 Apr 2007 04:27:34 PDT
X-Mailer: YahooMailRC/478 YahooMailWebService/0.7.41.10
Date: Fri, 13 Apr 2007 04:27:34 -0700 (PDT)
From: Carlos Santos <carloscompels@yahoo.com.br>
Reply-To: Carlos Santos <carlos@compels.net>
Subject: How can I know if a row is Locked?
To: Lista PostgreSQL SQL <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-714722056-1176463654=:97452"
Message-ID: <651844.97452.qm@web57110.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/140
X-Sequence-Number: 28140

--0-714722056-1176463654=:97452
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

How can I know if a row is locked by another transaction.=0AI have in a tra=
nsaction like that:=0A=0ABEGIN;=0ASELECT * FROM compels.teste WHERE id =3D =
'1' FOR UPDATE;=0A=0APS1: where id is the primary key.=0APS2: The COMMIT co=
mmand is done after a long time.=0A=0AIn this case the row with the primary=
-key equals to '1' will be locked to other transactions until the COMMIT co=
mmand be executed.=0ASo, how can I detect if this row is locked?=0A=0ATks=
=0A =0ACarlos Henrique Iazzetti Santos =0ACompels Inform=E1tica =0A Santa R=
ita do Sapuca=ED - MG=0Awww.compels.net=0A=0A=0A=0A=0A_____________________=
_____________________________=0AFale com seus amigos  de gra=E7a com o novo=
 Yahoo! Messenger =0Ahttp://br.messenger.yahoo.com/ 
--0-714722056-1176463654=:97452
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:times new roman,new york,times,serif;fon=
t-size:12pt">How can I know if a row is locked by another transaction.<br>I=
 have in a transaction like that:<br><br style=3D"color: rgb(0, 128, 255);"=
><span style=3D"color: rgb(0, 128, 255);">BEGIN;</span><br style=3D"color: =
rgb(0, 128, 255);"><span style=3D"color: rgb(0, 128, 255);">SELECT * FROM c=
ompels.teste WHERE id =3D '1' FOR UPDATE;</span><br><br style=3D"font-style=
: italic;"><span style=3D"font-style: italic;">PS1: where id is the primary=
 key.</span><br style=3D"font-style: italic;"><span style=3D"font-style: it=
alic;">PS2: The COMMIT command is done after a long time.</span><br><br>In =
this case the row with the primary-key equals to '1' will be locked to othe=
r transactions until the COMMIT command be executed.<br>So, how can I detec=
t if this row is locked?<br><br>Tks<br><div>&nbsp;</div><span style=3D"colo=
r: rgb(0, 96, 191);">Carlos Henrique
 Iazzetti Santos </span><br style=3D"color: rgb(0, 96, 191);"><span style=
=3D"color: rgb(0, 96, 191);">Compels Inform=E1tica </span><br style=3D"colo=
r: rgb(0, 96, 191);"><span style=3D"color: rgb(0, 96, 191);"> Santa Rita do=
 Sapuca=ED - MG</span><br><a rel=3D"nofollow" style=3D"color: rgb(0, 96, 19=
1);" target=3D"_blank" href=3D"http://www.compels.net">www.compels.net</a><=
br><div><br></div></div><br>_______________________________________________=
___<br>Fale com seus amigos  de gra=E7a com o novo Yahoo! Messenger <br>htt=
p://br.messenger.yahoo.com/ </body></html>
--0-714722056-1176463654=:97452--



From mstepnicki@gmail.com Fri Apr  13 11:49:53 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 291D09FB502
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:49:53 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 85118-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:49:43 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by postgresql.org (Postfix) with ESMTP id 7FA889FB311
	for <pgsql-sql@postgresql.org>; Fri, 13 Apr 2007 08:49:47 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1HcKHM-0008A3-Ij
	for pgsql-sql@postgresql.org; Fri, 13 Apr 2007 13:49:24 +0200
Received: from pc035-72.amg.gda.pl ([153.19.72.35])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Fri, 13 Apr 2007 13:49:24 +0200
Received: from mstepnicki by pc035-72.amg.gda.pl with local (Gmexim 0.1
	(Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Fri, 13 Apr 2007 13:49:24 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: =?iso-8859-2?q?Marcin_St=EApnicki?= <mstepnicki@gmail.com>
Subject: Re: How can I know if a row is Locked?
Date: Fri, 13 Apr 2007 13:48:52 +0200
Organization: Linux.PL
Lines: 49
Message-ID: <pan.2007.04.13.11.48.51.897436@gmail.com>
References: <651844.97452.qm@web57110.mail.re3.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@sea.gmane.org
X-Gmane-NNTP-Posting-Host: pc035-72.amg.gda.pl
User-Agent: Pan/0.14.2.91 (As She Crawled Across the Table (Debian GNU/Linux))
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/143
X-Sequence-Number: 28143

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 





From andreas.kretschmer@schollglas.com Fri Apr  13 11:49:05 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 9A6CE9FB311
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:49:05 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 90001-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 13 Apr 2007 08:48:53 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by postgresql.org (Postfix) with ESMTP id EAFCE9FB43A
	for <pgsql-sql@postgresql.org>; Fri, 13 Apr 2007 08:48:57 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu6) with ESMTP (Nemesis),
	id 0ML29c-1HcKGt0cP2-0003Th; Fri, 13 Apr 2007 13:48:55 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HcKGs-00010N-00
	for pgsql-sql@postgresql.org; Fri, 13 Apr 2007 13:48:54 +0200
Date: Fri, 13 Apr 2007 13:48:54 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: How can I know if a row is Locked?
Message-ID: <20070413114854.GM24558@a-kretschmer.de>
References: <651844.97452.qm@web57110.mail.re3.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <651844.97452.qm@web57110.mail.re3.yahoo.com>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1/1zSNDCrL5cj6yel0fNdD6ErRDTWpxsa8YwLv
	h8yehujDtyKB9j3RxrUMs0JvUK8sHhD76Lca/56Z4hP4M6/mMm
	2gdS7QvXXHIXZfDAZOzxMmercPM8kWB
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/141
X-Sequence-Number: 28141

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