concurrency problem

Dear all,
I tried the lock table option today.
yes it works fine when saving simultaneously.
but after two or three times even when all of us close our application,
the lock exists there in the database. when we run the select query from
other db tool it hangs up.

our code looks like
Statement stmt = con.createStatement();
while(true){
try{
stmt.execute("begin");
stmt.execute("lock table rcp_patient_visit_monitor");
break;
}catch(SQLException e){
stmt.execute("commit");
}
}
psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit");

if saved then
con.commit(); is called
if not saved then
con.rollback(); is called

First we run in two system, we did not faced the problem, then we run in
three system the record is saved, after that we simulate the run once again,
we face the problem,
i could not predict the problem precisely - what might be the problem.

thanks in advance
- Show quoted text -

On 6/16/06, Richard Huxton <dev(at)archonet(dot)com> wrote:
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

--
Sathish Kumar.S
SpireTEK