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 872779FA5C8 for ; Fri, 16 Jun 2006 01:14:22 -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 12745-04 for ; Fri, 16 Jun 2006 01:14:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id DDF509F9CAA for ; Fri, 16 Jun 2006 01:14:02 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so585404ugf for ; Thu, 15 Jun 2006 21:14:01 -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=VA+7Ghh5ElzvJ7vPD+OdpFVjxBBTkEB+QIDGtBg/vuSd8nvu2ISQM1ccswJr1IQm+qip2IegQo3HKlaw1PGsC5Ef1CR4vMXwlW25E0JR5orDbnYd+t9eGpFXdisq5CIVAP6h/g8OeC4lh6w+GFFGKNqxEWvSKjE39ALnt0nJkUA= Received: by 10.78.58.11 with SMTP id g11mr880268hua; Thu, 15 Jun 2006 21:14:01 -0700 (PDT) Received: by 10.78.52.14 with HTTP; Thu, 15 Jun 2006 21:14:01 -0700 (PDT) Message-ID: Date: Fri, 16 Jun 2006 09:44:01 +0530 From: "sathish kumar shanmugavelu" To: pgsql-sql@postgresql.org Subject: concurrency problem MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_21040_18246144.1150431241502" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/199 X-Sequence-Number: 25435 ------=_Part_21040_18246144.1150431241502 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Dear group, Let me explain my issue. We use Database - postgresql-8.1 JDBC Driver - postgresql-8.1-407.jdbc3.jar Java - jdk1.5 The default transaction isolation level is - Read Committed Auto Commit is false In our application we used a single connection object. We open the connection in the MDI form and close it only when the MDI closes , simply when the application closes. I give a insert statment like 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. If i use the transaction level - Serializable - again one entry is saved. Only on closing this application (closing the connection) the application running in other system is getting saved. If i lock the table and create a transaction - by sending the commands con.createStatement().executeUpdate("begin"); con.createStatement().executeUpdate("lock table rcp_patient_visit_monitor"); int rows = psSave.executeUpdate(); con.createStatement().executeUpdate("commit"); The form in one system is saved, in another system an error says - ' Deadlock detected .....' When i test the above said commands in dbvisualizer from two different systems , it works, but here it does not. why. how to solve this concurrency problem. Thanks in advance, -- Sathish Kumar.S SpireTEK ------=_Part_21040_18246144.1150431241502 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Dear group,
    Let me explain my issue.
       We use
              Database      - postgresql-8.1
              JDBC Driver  - postgresql-8.1-407.jdbc3.jar
              Java             - jdk1.5
              The default transaction isolation level is - Read Committed
              Auto Commit is false
    In our application we used a single connection object. We open the connection in the MDI form and close it only when the MDI closes , simply when the application closes.
    I give a insert statment like
             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.

   If i use the transaction level - Serializable - again one entry is saved. Only on closing this application (closing the connection) the application running in other system is getting saved.
 
   If i lock the table and create a transaction - by sending the commands
         con.createStatement().executeUpdate("begin");
         con.createStatement().executeUpdate("lock table rcp_patient_visit_monitor");
         int rows = psSave.executeUpdate();
         con.createStatement().executeUpdate("commit");
  
   The form in one system is saved, in another system an error says - ' Deadlock detected  .....'
  
    When i test the above said commands in dbvisualizer from two different systems , it works, but here it does not. why.
  
    how to solve this concurrency problem.

Thanks in advance,
--
Sathish Kumar.S
SpireTEK ------=_Part_21040_18246144.1150431241502--