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 466279FA5FB for ; Fri, 16 Jun 2006 10:39:07 -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 36232-04 for ; Fri, 16 Jun 2006 10:39:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 47FF39FA42B for ; Fri, 16 Jun 2006 10:39:01 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so794282ugf for ; Fri, 16 Jun 2006 06:39:00 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=UHLz4PrTSjYrgVshdGBTKuRd4LACCF7lv5AZ3CNw3Y0VEBWBAXdnLAaJAw6p+0ot1wkZXDiPYll7n9H4R/yqm1YRDJqRYS5pg1znSor1lgz0gZ9+3UJf6suMmTLvJT8jLCzF9yU1Ww/UxGwtkGqc4x+WEwGyxb9UCfkCJsFrQ2o= Received: by 10.78.47.9 with SMTP id u9mr1002251huu; Fri, 16 Jun 2006 06:39:00 -0700 (PDT) Received: by 10.78.52.14 with HTTP; Fri, 16 Jun 2006 06:39:00 -0700 (PDT) Message-ID: Date: Fri, 16 Jun 2006 19:09:00 +0530 From: "sathish kumar shanmugavelu" To: pgsql-sql@postgresql.org Subject: concurrency problem MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27528_8673662.1150465140201" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/205 X-Sequence-Number: 25441 ------=_Part_27528_8673662.1150465140201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 First we run in two system, we did not faced the problem, then we run in three system the record is saved, after that we simulate the run once again, we face the problem, i could not predict the problem precisely - what might be the problem. thanks in advance - Show quoted text - On 6/16/06, Richard Huxton wrote: sathish kumar shanmugavelu wrote: > INSERT INTO rcp_patient_visit_monitor ( > entry_no, patient_id, visit_date, is_newpatient, > visit_type, is_medical, > is_review, is_labtest, is_scan, is_scopy, is_xray, > weight, height) > VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM > rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?) > > The point to note here is the select statement which gets the max > entry_no and adds one to it and save the new value. entry_no is the primary > key of the above table. > > Now i run the same program (different instance) from two systems, save > the form simultaneously, only one entry is saved, in the other system the > error says - duplicate key violates. BEGIN; LOCK TABLE ... INSERT ... COMMIT; You'll need to handle possible errors where one client fails to get a lock and times out. It won't happen often, but you do need to consider the option. -- Richard Huxton Archonet Ltd -- Sathish Kumar.S SpireTEK ------=_Part_27528_8673662.1150465140201 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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
  
   First we run in two system, we did not faced the problem, then we run in three system the record is saved, after that we simulate the run once again, we face the problem,
   i could not predict the problem precisely - what might be the problem.
 
thanks in advance
- Show quoted text -

 
On 6/16/06, Richard Huxton <dev@archonet.com> wrote:
sathish kumar shanmugavelu wrote:
>             INSERT INTO rcp_patient_visit_monitor (
>                   entry_no, patient_id, visit_date, is_newpatient,
> visit_type, is_medical,
>                   is_review, is_labtest, is_scan, is_scopy, is_xray,
> weight, height)
>                   VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM
> rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
>
>    The point to note here is the select statement which gets the max
> entry_no and adds one to it and save the new value. entry_no is the primary
> key of the above table.
>
>   Now i run the same program (different instance) from two systems, save
> the form simultaneously, only one entry is saved, in the other system the
> error says - duplicate key violates.

BEGIN;
LOCK TABLE ...
INSERT ...
COMMIT;

You'll need to handle possible errors where one client fails to get a
lock and times out. It won't happen often, but you do need to consider
the option.

--
  Richard Huxton
  Archonet Ltd

--
Sathish Kumar.S
SpireTEK

------=_Part_27528_8673662.1150465140201--