X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 3862E9FA5FB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 11:34:11 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 42200-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 11:34:01 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.192])
	by postgresql.org (Postfix) with ESMTP id 5A0339FA5C3
	for <pgsql-sql@postgresql.org>; Fri, 16 Jun 2006 11:34:01 -0300 (ADT)
Received: by wx-out-0102.google.com with SMTP id h30so454099wxd
	for <pgsql-sql@postgresql.org>; Fri, 16 Jun 2006 07:33:59 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=kMMTwOd4iVbsvu5zuD3pj/5fNwW14/LvW5X6rF9I+8q7v7B4OICW41N/Uchlr76uA+mdKg6xCv6ueHxAuKGCisX3jvsdCyICH0HM7MYBXwVB4j+QgcIXZ00fIZaTLrcOmxshJfiMfnl44Xo2NPnQhznujwaN5Wow0/OCdzqX8nI=
Received: by 10.70.73.4 with SMTP id v4mr4052654wxa;
	Fri, 16 Jun 2006 07:33:59 -0700 (PDT)
Received: by 10.70.66.15 with HTTP; Fri, 16 Jun 2006 07:33:59 -0700 (PDT)
Message-ID: <bf05e51c0606160733s7190a76dq3e4081b82af9d1d@mail.gmail.com>
Date: Fri, 16 Jun 2006 09:33:59 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "sathish kumar shanmugavelu" <sathishkumar.shanmugavelu@gmail.com>
Subject: Re: concurrency problem
Cc: pgsql-sql@postgresql.org
In-Reply-To: <ee0ad3a30606160639k7e82465i9bee4a2e17c80fdf@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_61755_9826353.1150468439501"
References: <ee0ad3a30606160639k7e82465i9bee4a2e17c80fdf@mail.gmail.com>
X-Google-Sender-Auth: 2a33d7cf5709ca5b
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/211
X-Sequence-Number: 25447

------=_Part_61755_9826353.1150468439501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I know this is a Java issue but I would recommend something more like:

    Statement stmt = con.createStatement();
    try {
       stmt.execute("begin");
       stmt.execute("lock table rcp_patient_visit_monitor");
       psSave.executeUpdate(); //psSave is a prepared statement
       stmt.execute("commit");
       con.commit(); is called
    } catch (Exception e) {
       con.rollback(); is called
    } finally {
       stmt.close();
    }

On 6/16/06, sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
wrote:
>
> Dear all,
>    I tried the lock table option today.
>    yes it works fine when saving simultaneously.
>    but after two or three times even when all of us close our application,
> the lock exists there in the database. when we run the select query from
> other db tool it hangs up.
>
>    our code looks like
>        Statement stmt = con.createStatement();
>        while(true){
>            try{
>                stmt.execute("begin");
>                stmt.execute("lock table rcp_patient_visit_monitor");
>                break;
>            }catch(SQLException e){
>                stmt.execute("commit");
>            }
>        }
>        psSave.executeUpdate(); //psSave is a prepared statement
>        stmt.execute("commit");
>
>        if saved then
>              con.commit(); is called
>        if not saved then
>              con.rollback(); is called
>

------=_Part_61755_9826353.1150468439501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I know this is a Java issue but I would recommend something more like:<br><br>
&nbsp;&nbsp;&nbsp; Statement stmt = con.createStatement();<br>&nbsp;&nbsp;&nbsp; try {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.execute(&quot;begin&quot;);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.execute(&quot;lock table rcp_patient_visit_monitor&quot;); <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; psSave.executeUpdate(); //psSave is a prepared statement
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
stmt.execute(&quot;commit&quot;);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.commit(); is called<br>&nbsp;&nbsp;&nbsp; } catch (Exception e) {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.rollback(); is called<br>&nbsp;&nbsp;&nbsp; } finally {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.close();<br>&nbsp;&nbsp;&nbsp; }<br><br><div><span class="gmail_quote">
On 6/16/06, <b class="gmail_sendername">sathish kumar shanmugavelu</b> &lt;<a href="mailto:sathishkumar.shanmugavelu@gmail.com">sathishkumar.shanmugavelu@gmail.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><p>Dear all,<br>&nbsp;&nbsp; I tried the lock table option today.<br>&nbsp;&nbsp; yes it works fine when saving simultaneously.<br>&nbsp;&nbsp; but after two or three times even when all of us close our application, the lock exists there in the database. when we run the select query from other db tool it hangs up.
<br>&nbsp;<br>&nbsp;&nbsp; our code looks like<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Statement stmt = con.createStatement();<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; while(true){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try{<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.execute(&quot;begin&quot;);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.execute(&quot;lock table rcp_patient_visit_monitor&quot;);
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; break;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }catch(SQLException e){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.execute(&quot;commit&quot;);&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; psSave.executeUpdate(); //psSave is a prepared statement<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
stmt.execute(&quot;commit&quot;);<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if saved then <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.commit(); is called<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if not saved then <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.rollback(); is called</p></div></blockquote></div>

------=_Part_61755_9826353.1150468439501--