Re: concurrency problem

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

On 6/15/06, sathish kumar shanmugavelu <sathishkumar(dot)shanmugavelu(at)gmail(dot)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
>