From: | "sathish kumar shanmugavelu" <sathishkumar(dot)shanmugavelu(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | concurrency problem |
Date: | 2006-06-16 04:14:01 |
Message-ID: | ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-06-16 06:28:16 | Re: concurrency problem |
Previous Message | Phillip Smith | 2006-06-16 03:11:23 | Re: listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!) |