X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id F2A389FA5E9 for ; Sat, 17 Jun 2006 11:53:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 97707-09 for ; Sat, 17 Jun 2006 11:52:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.193]) by postgresql.org (Postfix) with ESMTP id A5BFA9FA430 for ; Sat, 17 Jun 2006 11:52:52 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id i28so590360wxd for ; Sat, 17 Jun 2006 07:52:51 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=QgU7fIPBvC/hsGwemSHL/la+Rs4FFnU4lHYVIYShsgNEtJAbxyG3/xRVMsVUiDTDFGBNjJgNm8ECugqavftXwZhb+MrJezsnTbqEIWDfZrOlY9+UqokjXpI1zsz9KkTSNmiPfQAFP935Pn9ftWpY3L0arWW9iCWSmKrq/CCWRGE= Received: by 10.70.83.20 with SMTP id g20mr5678330wxb; Sat, 17 Jun 2006 07:52:51 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Sat, 17 Jun 2006 07:52:50 -0700 (PDT) Message-ID: Date: Sat, 17 Jun 2006 09:52:50 -0500 From: "Aaron Bono" To: "sathish kumar shanmugavelu" Subject: Re: concurrency problem Cc: "Ash Grove" , pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_74195_2967482.1150555970693" References: <20060616140855.72700.qmail@web52513.mail.yahoo.com> X-Google-Sender-Auth: 2ee7e0700dd1afce X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/220 X-Sequence-Number: 25456 ------=_Part_74195_2967482.1150555970693 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 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,?,?,?,?,?,?,?,?,?,?) > > > > > ------=_Part_74195_2967482.1150555970693 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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,?,?,?,?,?,?,?,?,?,?)


------=_Part_74195_2967482.1150555970693--