Re: concurrency problem

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: sathish <sathishkumar(dot)shanmugavelu(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: concurrency problem
Date: 2006-06-16 12:14:50
Message-ID: 3B48603C-44EB-40AE-A74F-2BD9220F33A5@fastcrypt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc


On 16-Jun-06, at 2:15 AM, sathish 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.
> Also i want to know if a table is locked from jdbc.
The table is not locked
>
> Thanks in advance,
> --
> Sathish Kumar.S
> SpireTEK
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2006-06-18 06:03:00 Re: Meta Information about columns of a domain datatype ?
Previous Message Dave Cramer 2006-06-16 11:37:16 Re: concurrency problem