Postgres Pro
Downloads
Home   >   mailing lists

Thread: concurrency problem

concurrency problem

From
"sathish"
Date:
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.

Thanks in advance,
--
Sathish Kumar.S
SpireTEK

Re: concurrency problem

From
Dave Cramer
Date:
Don't use max()

Use a serial, and use currval

Dave
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.
>
> 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
>

Re: concurrency problem

From
Dave Cramer
Date:
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
>