From: Aaron Bono <postgresql@aranya.com>
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Fri, 16 Jun 2006 09:00:37 -0500
Message-ID: <bf05e51c0606160700x3165984fr32c3478f244da62b@mail.gmail.com> (raw)
In-Reply-To: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
References: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
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.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
>
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: postgresql@aranya.com, sathishkumar.shanmugavelu@gmail.com
Subject: Re: concurrency problem
In-Reply-To: <bf05e51c0606160700x3165984fr32c3478f244da62b@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