From: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
To: Ash Grove <ash_grv7@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Sat, 17 Jun 2006 09:23:17 +0530
Message-ID: <ee0ad3a30606162053j1533b7e2o16d05a7e0fe7d944@mail.gmail.com> (raw)
In-Reply-To: <20060616140855.72700.qmail@web52513.mail.yahoo.com>
References: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
<20060616140855.72700.qmail@web52513.mail.yahoo.com>
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
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, ash_grv7@yahoo.com
Subject: Re: concurrency problem
In-Reply-To: <ee0ad3a30606162053j1533b7e2o16d05a7e0fe7d944@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