From: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
To: pgsql-sql@postgresql.org
Subject: concurrency problem
Date: Fri, 16 Jun 2006 19:09:00 +0530
Message-ID: <ee0ad3a30606160639k7e82465i9bee4a2e17c80fdf@mail.gmail.com> (raw)
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@archonet.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sathishkumar.shanmugavelu@gmail.com
Subject: Re: concurrency problem
In-Reply-To: <ee0ad3a30606160639k7e82465i9bee4a2e17c80fdf@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox