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 CE92E9FA5F8 for ; Sat, 17 Jun 2006 00:53:25 -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 27581-02 for ; Sat, 17 Jun 2006 00:53:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id 6ACBF9FA5D8 for ; Sat, 17 Jun 2006 00:53:19 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so1126258ugf for ; Fri, 16 Jun 2006 20:53:18 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=lxjySdIKxfhldjzuQPYpBLKByh0eHnn/ld+Mbf6d16mcFVf9/fVu5LzFOVXDNlCGklVP0PdUq7+1edaBat7H9SC//4XfeSyfcR1xUu94ITI3EwqmIundY0hw29NXyn89EOjoBRHEpQvhM0px2+n4n5TAzCrL22Rkjbt7cm/vNIs= Received: by 10.78.32.16 with SMTP id f16mr1304849huf; Fri, 16 Jun 2006 20:53:17 -0700 (PDT) Received: by 10.78.52.14 with HTTP; Fri, 16 Jun 2006 20:53:17 -0700 (PDT) Message-ID: Date: Sat, 17 Jun 2006 09:23:17 +0530 From: "sathish kumar shanmugavelu" To: "Ash Grove" Subject: Re: concurrency problem Cc: pgsql-sql@postgresql.org In-Reply-To: <20060616140855.72700.qmail@web52513.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9678_13905741.1150516397898" References: <20060616140855.72700.qmail@web52513.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/218 X-Sequence-Number: 25454 ------=_Part_9678_13905741.1150516397898 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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,?,?,?,?,?,?,?,?,?,?) > > > > > __________________________________________________ > 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 ------=_Part_9678_13905741.1150516397898 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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,?,?,?,?,?,?,?,?,?,?)




__________________________________________________
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 ------=_Part_9678_13905741.1150516397898--