Re: concurrency problem

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(at)yahoo(dot)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