Re: concurrency problem

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(at)yahoo(dot)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
> }
> }
> }