From: Aaron Bono <postgresql@aranya.com>
To: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
Cc: Ash Grove <ash_grv7@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Sat, 17 Jun 2006 09:52:50 -0500
Message-ID: <bf05e51c0606170752qf0efea6m7bd68be8c2d194ab@mail.gmail.com> (raw)
In-Reply-To: <ee0ad3a30606162053j1533b7e2o16d05a7e0fe7d944@mail.gmail.com>
References: <ee0ad3a30606152114g122d0021ja7c603a8f36ae62c@mail.gmail.com>
<20060616140855.72700.qmail@web52513.mail.yahoo.com>
<ee0ad3a30606162053j1533b7e2o16d05a7e0fe7d944@mail.gmail.com>
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.shanmugavelu@gmail.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@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,?,?,?,?,?,?,?,?,?,?)
> >
> >
>
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: postgresql@aranya.com, sathishkumar.shanmugavelu@gmail.com, ash_grv7@yahoo.com
Subject: Re: concurrency problem
In-Reply-To: <bf05e51c0606170752qf0efea6m7bd68be8c2d194ab@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