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 CDCE49FA188 for ; Sun, 18 Jun 2006 18:24:39 -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 17697-09 for ; Sun, 18 Jun 2006 18:24:34 -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 629319FA13E for ; Sun, 18 Jun 2006 18:24:34 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h30so750330wxd for ; Sun, 18 Jun 2006 14:24:33 -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=aK4mFcqBhu0etmCkyZVLkvNRwYNQKOSv+6FLLDACnv5dG3L+6/psgmQaeAJQzre9VGqUG9aQJwz1/C/JgPmtrIzTH5e3OZITsYkNMWakBJpm6SfXM1PNB+yKaIZu1mRUO9OstV7qG1nx+Er3NSP/orlquLp7J/2FmXO6dVuDTqs= Received: by 10.70.100.8 with SMTP id x8mr7399611wxb; Sun, 18 Jun 2006 14:24:33 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Sun, 18 Jun 2006 14:24:33 -0700 (PDT) Message-ID: Date: Sun, 18 Jun 2006 16:24:33 -0500 From: "Aaron Bono" To: "Ash Grove" Subject: Re: concurrency problem Cc: "sathish kumar shanmugavelu" , pgsql-sql@postgresql.org In-Reply-To: <20060617222425.42929.qmail@web52508.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_85017_22112281.1150665873135" References: <20060617222425.42929.qmail@web52508.mail.yahoo.com> X-Google-Sender-Auth: 0cbdb248cf091393 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/225 X-Sequence-Number: 25461 ------=_Part_85017_22112281.1150665873135 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Looks good but you really shoud put your stmt.close() and conn.close() in a finally block so even if there is an error everything gets cleaned up properly. That syntax is for Java but the principle is the same for any programming language - always make sure you clean up your connections no matter what errors occur. -Aaron Bono On 6/17/06, Ash Grove wrote: > > Locks are released when the containing transaction > commits. There is no explicit "release." > > Instead of calling "begin" and "commit" as statements, > I do something more like below. As Aaron mentioned, > this is JDBC, not SQL. Sorry people. > > try { > ... > conn.setAutoCommit(false); > > //do the insert on the table that generates the > primary key via a sequence > PreparedStatement pstmt = > conn.prepareStatement("my prepared statement"); > pstmt.executeUpdate(); > > //your prepared statement above should do an > //insert on a table that calls nextval(). > //Calling currval() below will guarantee that > you'll get > //the value created by the insert statement > //Check out the documentation on sequence > functions > > //get the new primary key > String get_pkey = "{ ? = call currval('my_seq') > }"; > CallableStatement = conn.prepareCall(get_pkey); > cstmt.registerOutParameter(1, Types.BIGINT); > cstmt.execute(); > > long new_pkey = cstmt.getLong(1); > > //do all of your updates/inserts on tables using > new_pkey as a foreign key > //I like to do this in batches > Statement stmt = conn.createStatement(); > stmt.addBatch("insert into... ) > stmt.addBatch("update whatever set... ) > stmt.executeBatch(); > > conn.commit(); > > stmt.close(); > conn.close(); > > } catch(SQLException e1) { > //do something with error 1 > if (conn != null) { > try { > conn.rollback(); > } catch(SQLException e2) { > //do something with error 2 > } > } > } ------=_Part_85017_22112281.1150665873135 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Looks good but you really shoud put your stmt.close() and conn.close() in a finally block so even if there is an error everything gets cleaned up properly.  That syntax is for Java but the principle is the same for any programming language - always make sure you clean up your connections no matter what errors occur.

-Aaron Bono

On 6/17/06, Ash Grove <ash_grv7@yahoo.com> wrote:
Locks are released when the containing transaction
commits. There is no explicit "release."

Instead of calling "begin" and "commit" as statements,
I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.

try {
...
conn.setAutoCommit(false);

    //do the insert on the table that generates the
primary key via a sequence
    PreparedStatement pstmt =
conn.prepareStatement ("my prepared statement");
    pstmt.executeUpdate();

    //your prepared statement above should do an
    //insert on a table that calls nextval().
    //Calling currval() below will guarantee that
you'll get
    //the value created by the insert statement
    //Check out the documentation on sequence
functions

    //get the new primary key
    String get_pkey = "{ ? = call currval('my_seq')
}";
    CallableStatement = conn.prepareCall(get_pkey);
    cstmt.registerOutParameter(1, Types.BIGINT);
    cstmt.execute();

    long new_pkey = cstmt.getLong(1);

    //do all of your updates/inserts on tables using
new_pkey as a foreign key
    //I like to do this in batches
    Statement stmt = conn.createStatement();
    stmt.addBatch("insert into... )
    stmt.addBatch("update whatever set... )
    stmt.executeBatch ();

conn.commit();

stmt.close();
conn.close();

} catch(SQLException e1) {
    //do something with error 1
    if (conn != null) {
        try {
            conn.rollback();
        } catch(SQLException e2) {
            //do something with error 2
        }
    }
}
------=_Part_85017_22112281.1150665873135--