Thread: concurrency problem

Dear group,<br />    Let me explain my issue.<br />       We use<br />              Database      - postgresql-8.1<br
/>             JDBC Driver  - postgresql-8.1-407.jdbc3.jar<br />              Java             - jdk1.5<br />          
  The default transaction isolation level is - Read Committed <br />              Auto Commit is false<br />    In our
applicationwe 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. <br />    I give a insert statment like <br />             INSERT INTO
rcp_patient_visit_monitor(<br />                   entry_no, patient_id, visit_date, is_newpatient, visit_type,
is_medical,<br />                   is_review, is_labtest, is_scan, is_scopy, is_xray, weight, height) <br
/>                  VALUES (<span style="font-weight: bold;">(SELECT coalesce(max(entry_no)+1, 1)</span> FROM
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)<br/>       <br />    The point to note here is the
selectstatement which gets the max entry_no and adds one to it and save the new value. entry_no is the primary key of
theabove table. <br />    <br />   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.<br /><br />   If i
usethe 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. <br /> <br />   If i lock the table and create a
transaction- by sending the commands <br />         con.createStatement().executeUpdate("begin");<br />        
con.createStatement().executeUpdate("locktable rcp_patient_visit_monitor"); <br />         int rows =
psSave.executeUpdate();<br/>         con.createStatement().executeUpdate("commit");<br />   <br />   The form in one
systemis saved, in another system an error says - ' Deadlock detected  .....' <br />   <br />    When i test the above
saidcommands in dbvisualizer from two different systems , it works, but here it does not. why.<br />   <br />    how to
solvethis concurrency problem.<br /><br />Thanks in advance,<br />-- <br /> Sathish Kumar.S<br />SpireTEK  

sathish kumar shanmugavelu wrote:
>             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.

BEGIN;
LOCK TABLE ...
INSERT ...
COMMIT;

You'll need to handle possible errors where one client fails to get a 
lock and times out. It won't happen often, but you do need to consider 
the option.

--   Richard Huxton  Archonet Ltd

>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,?,?,?,?,?,?,?,?,?,?)

You are only working on one table so you sholdn't have
to manage a transaction or deal with explicit locking.
Just let the database handle this for you with a
sequence. Your concurrency issues will disappear.

1) create a sequence:

create sequence entry_no_sequence


2) set the new sequence's value to your table's
current entry_no value (n):

select setval('entry_no_sequence',n)


3) recreate your table so that the entry_no will get
it's value from calling nextval() on your new
sequence:

entry_no integer not null default
nextval('entry_no_sequence')


Thereafter, when an insert is made on your table, the
enry_no field will get its value from the sequence and
the sequence will be incremented. You would then drop
entro_no from your insert statement and it would
become something like:

INSERT INTO rcp_patient_visit_monitor (
patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review, 
is_labtest, is_scan, is_scopy, is_xray, weight,
height)
VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Locks are released when the containing transaction
commits. There is no explicit "release."

Instead of calling "begin" and "commit" as statements,
I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.

try {
...
conn.setAutoCommit(false);
   //do the insert on the table that generates the
primary key via a sequence   PreparedStatement pstmt =
conn.prepareStatement("my prepared statement");   pstmt.executeUpdate();                           //your prepared
statementabove should do an   //insert on a table that calls nextval().   //Calling currval() below will guarantee
that
you'll get   //the value created by the insert statement   //Check out the documentation on sequence
functions       //get the new primary key   String get_pkey = "{ ? = call currval('my_seq')
}";   CallableStatement = conn.prepareCall(get_pkey);   cstmt.registerOutParameter(1, Types.BIGINT);   cstmt.execute();
             long new_pkey = cstmt.getLong(1);       //do all of your updates/inserts on tables using
 
new_pkey as a foreign key   //I like to do this in batches   Statement stmt = conn.createStatement();
stmt.addBatch("insertinto... )   stmt.addBatch("update whatever set... )   stmt.executeBatch();
 
conn.commit();
stmt.close();
conn.close();

} catch(SQLException e1) {   //do something with error 1   if (conn != null) {       try {           conn.rollback();
   } catch(SQLException e2) {           //do something with error 2       }   }
 
}     

--- Aaron Bono <postgresql@aranya.com> wrote:

> When in this situation I:
> 
> 1. Wait until I have enough data to do a complete
> commit before even
> bothering to save any data to the database.  I want
> the life of my
> transactions to last no more than milliseconds if
> possible.
> 2. Use a BIGSERIAL for the primary keys so the IDs
> are assigned
> automatically through triggers and sequence IDs.
> 3. Do a "SELECT currval('my_sequence') AS
> seq_number;" to determine what ID
> was assigned so I can use it on child tables.
> 
> -Aaron Bono
> 
> On 6/16/06, sathish kumar shanmugavelu
> <sathishkumar.shanmugavelu@gmail.com>
> wrote:
> >
> > Dear group
> >    Its my mistake that i did not reveal the whole
> scenario.
> >    Actually  within that  begin  and  commit, i
> insert in 10 tables. The
> > above said table is the key table.
> >    I fetch the consultatioin_no and add one to it,
> i should know this
> > consultation_no to save the other 10 tables.
> because i use this number as
> > foreign key in other tables. Also in my program,
> the data for that 10 tables
> > are collected in different java classes and save
> coding is also there. I
> > initiate this save coding for all the 10 forms in
> the one form (some main
> > form).
> >     so if any error occurs i have to roll back the
> whole transaction.
> >
> >     Is there any method to release the lock
> explicitly, where postgres
> > store this locking information.
> >     Is both
> >          stmt.execute ("commit");
> >          con.commit();
> >     are both same. should i have to call
> con.commit() method after
> > stmt.execute("commit")
> >
> >     Now Iam also thinking to use sequence. but
> please clear the above
> > doubts.
> >
> > --
> > Sathish Kumar.S
> > SpireTEK
> >
> >
> > On 6/16/06, Ash Grove <ash_grv7@yahoo.com> wrote:
> > >
> > >
> > >
> > > >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,?,?,?,?,?,?,?,?,?,?)
> > >
> > > You are only working on one table so you
> sholdn't have
> > > to manage a transaction or deal with explicit
> locking.
> > > Just let the database handle this for you with a
> > > sequence. Your concurrency issues will
> disappear.
> > >
> > > 1) create a sequence:
> > >
> > > create sequence entry_no_sequence
> > >
> > >
> > > 2) set the new sequence's value to your table's
> > > current entry_no value (n):
> > >
> > > select setval('entry_no_sequence',n)
> > >
> > >
> > > 3) recreate your table so that the entry_no will
> get
> > > it's value from calling nextval() on your new
> > > sequence:
> > >
> > > entry_no integer not null default
> > > nextval('entry_no_sequence')
> > >
> > >
> > > Thereafter, when an insert is made on your
> table, the
> > > enry_no field will get its value from the
> sequence and
> > > the sequence will be incremented. You would then
> drop
> > > entro_no from your insert statement and it would
> > > become something like:
> > >
> > > INSERT INTO rcp_patient_visit_monitor (
> > > patient_id, visit_date, is_newpatient,
> visit_type,
> > > is_medical, is_review,
> > > is_labtest, is_scan, is_scopy, is_xray, weight,
> > > height)
> > > VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
> > >
> > >
> >
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

On Sat, Jun 17, 2006 at 09:23:17AM +0530, sathish kumar shanmugavelu wrote:
>   I fetch the consultatioin_no and add one to it, i should know this
> consultation_no to save the other 10 tables. because i use this number as

Don't do that.  Fetch the number from a sequence first: select
nextval().  Then you can insert it as currval() for all your other
INSERTs.  No, there is no race condition or concurrency problem: see
the docs on these functions.  No locks.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes