Thread: concurrency problem

<p>Dear all,<br />   I tried the lock table option today.<br />   yes it works fine when saving simultaneously.<br />  
butafter two or three times even when all of us close our application, the lock exists there in the database. when we
runthe select query from other db tool it hangs up. <br /> <br />   our code looks like<br />       Statement stmt =
con.createStatement();<br/>       while(true){<br />           try{<br />               stmt.execute("begin");<br
/>              stmt.execute("lock table rcp_patient_visit_monitor"); <br />               break;<br />          
}catch(SQLExceptione){<br />               stmt.execute("commit");  <br />           }<br />       }<br />      
psSave.executeUpdate();//psSave is a prepared statement<br />       stmt.execute("commit");<br /> <br />       if saved
then<br />             con.commit(); is called<br />       if not saved then <br />             con.rollback(); is
called<br/>   <br />   First we run in two system, we did not faced the problem, then we run in three system the record
issaved, after that we simulate the run once again, we face the problem, <br />   i could not predict the problem
precisely- what might be the problem.<br /> <br />thanks in advance<br />- Show quoted text -<p> <br />On 6/16/06,
RichardHuxton <<a href="mailto:dev@archonet.com">dev@archonet.com</a>> wrote: <br />sathish kumar shanmugavelu
wrote:<br/>>             INSERT INTO rcp_patient_visit_monitor (<br />>                   entry_no, patient_id,
visit_date,is_newpatient, <br />> visit_type, is_medical,<br />>                   is_review, is_labtest,
is_scan,is_scopy, is_xray,<br />> weight, height)<br />>                   VALUES ((SELECT
coalesce(max(entry_no)+1,1) FROM<br />> rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?) <br
/>><br/>>    The point to note here is the select statement which gets the max<br />> entry_no and adds one to
itand save the new value. entry_no is the primary<br />> key of the above table.<br />><br />>   Now i run the
sameprogram (different instance) from two systems, save <br />> the form simultaneously, only one entry is saved, in
theother system the<br />> error says - duplicate key violates.<p>BEGIN;<br />LOCK TABLE ...<br />INSERT ...<br
/>COMMIT;<p>You'llneed to handle possible errors where one client fails to get a <br />lock and times out. It won't
happenoften, but you do need to consider<br />the option.<p>--<br />  Richard Huxton<br />  Archonet Ltd<p>-- <br
/>SathishKumar.S<br />SpireTEK  

I know this is a Java issue but I would recommend something more like:

    Statement stmt = con.createStatement();
    try {
       stmt.execute("begin");
       stmt.execute("lock table rcp_patient_visit_monitor");
       psSave.executeUpdate(); //psSave is a prepared statement
       stmt.execute("commit");
       con.commit(); is called
    } catch (Exception e) {
       con.rollback(); is called
    } finally {
       stmt.close();
    }