This is how I handle auto generated numbers.
The only downside is if an insert fails for some reason - then a number will be skipped. You would have to have some really restrictive requirements for this to matter though.
-Aaron Bono
On 6/15/06, sathish kumar shanmugavelu <[EMAIL PROTECTED]
> wrote:
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
