From: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
To: pgsql-sql@postgresql.org
Subject: concurrency problem
Date: Fri, 16 Jun 2006 09:44:01 +0530
Message-ID: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sathishkumar.shanmugavelu@gmail.com
Subject: Re: concurrency problem
In-Reply-To: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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