Re: concurrency problem

When in this situation I:

1. Wait until I have enough data to do a complete commit before even
bothering to save any data to the database. I want the life of my
transactions to last no more than milliseconds if possible.
2. Use a BIGSERIAL for the primary keys so the IDs are assigned
automatically through triggers and sequence IDs.
3. Do a "SELECT currval('my_sequence') AS seq_number;" to determine what ID
was assigned so I can use it on child tables.

-Aaron Bono

On 6/16/06, sathish kumar shanmugavelu <sathishkumar(dot)shanmugavelu(at)gmail(dot)com>
wrote:
>
> 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,?,?,?,?,?,?,?,?,?,?)
> >
> >
>