From: Aaron Bono <postgresql@aranya.com>
To: Ash Grove <ash_grv7@yahoo.com>
Cc: sathish kumar shanmugavelu <sathishkumar.shanmugavelu@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: concurrency problem
Date: Sun, 18 Jun 2006 16:24:33 -0500
Message-ID: <bf05e51c0606181424l78116c64y8934775b47aa672d@mail.gmail.com> (raw)
In-Reply-To: <20060617222425.42929.qmail@web52508.mail.yahoo.com>
References: <bf05e51c0606170752qf0efea6m7bd68be8c2d194ab@mail.gmail.com>
<20060617222425.42929.qmail@web52508.mail.yahoo.com>
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
> }
> }
> }
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, ash_grv7@yahoo.com, sathishkumar.shanmugavelu@gmail.com
Subject: Re: concurrency problem
In-Reply-To: <bf05e51c0606181424l78116c64y8934775b47aa672d@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