Re: concurrency problem - Mailing list pgsql-sql
| From | Aaron Bono |
|---|---|
| Subject | Re: concurrency problem |
| Date | |
| Msg-id | bf05e51c0606160700x3165984fr32c3478f244da62b@mail.gmail.com Whole thread Raw |
| In response to | concurrency problem ("sathish kumar shanmugavelu" <sathishkumar.shanmugavelu@gmail.com>) |
| List | pgsql-sql |
I would use a BIGSERIAL for the ID. It simplifies your inserts, you don't have to mess with any locking and the sequence is maintained for you outside your transaction so two transactions can do inserts without stepping on each other.
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
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 <sathishkumar.shanmugavelu@gmail.com > 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