Re: concurrency problem - Mailing list pgsql-sql

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,?,?,?,?,?,?,?,?,?,?)