Skip site navigation (1) Skip section navigation (2)

Re: autocommit and stored procedures

From: roehm(at)it(dot)usyd(dot)edu(dot)au
To: "Mark Lewis" <mark(dot)lewis(at)mir3(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: autocommit and stored procedures
Date: 2007-08-16 07:21:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
Thanks for the prompt answer.
I had a look into the source code in the meanwhile and it seems as  
you say:
Autocommit(true) means no explicit BEGIN transaction from the JDBC  
which at the server side means an implicit transaction around the  
command sent.
If that is a stored procedure call, the whole stored procedure runs  
as one transaction.


On 15 Aug 2007, at 23:50, Mark Lewis wrote:

> On Wed, 2007-08-15 at 21:21 +1000, roehm(at)it(dot)usyd(dot)edu(dot)au wrote:
>> Hi,
>> Which command granularity does the JDBC driver's autocommit have?
>> Does it commit after each client-side JDBC statement, or does it  
>> commit
>> each individual SQL statement on the server-side?
>> In particular, does an JDBC autocommit around the call to a stored
>> procedure
>> commit each statement within that stored procedure, or does it commit
>> the
>> procedure as a whole?
>> Example:
>> Stored Procedure
>> ----------------
>> CREATE PROCEDURE Test (n varying character,val REAL)
>>     cid INTEGER;
>>     SELECT custid INTO cid
>>       FROM account
>>      WHERE name=n;
>>     UPDATE checking
>>        SET balance=balance-val
>>      WHERE custid=cid;
>> END;
>> JDBC Code
>> ---------
>> Connection conn;
>> CallableStatement cstmt;
>> conn.setAutoCommit(true);
>> cstmt = conn.prepareCall("{call Test(?,?)}");
>> cstmt.setString(1, name);
>> cstmt.setString(2, value);
>> cstmt.execute();
>> Does PostgreSQL execute one commit after the execution of Test(),
>> i.e. do select and update run in one transaction?
>> Or will PostgreSQL commit after the select and then again after the
>> update
>> inside the Test() procedure?
>> Does anyone know what the specified behaviour is for JDBC AutoCommit?
>> Many thanks
>> Uwe
> The JDBC driver manages the autocommit flag by not beginning a new
> transaction at all, since in PostgreSQL all statements execute in  
> their
> own private transactions unless an explicit transaction is started  
> with
> the BEGIN statement.
> Therefore, you would expect each individual statement (as  
> recognized by
> the PG back-end, not by your Java code) to execute and commit
> individually.
> I do not know whether or not JDBC specifies a behavior for this case,
> but given the intentional vagueness of the spec in several areas, I
> would be very surprised if the the spec weren't either silent or
> answered, "Executing multiple SQL commands in a single JDBC command is
> unsupported and may result in unspecified, driver-specific behavior".
> -- Mark

Dr. Uwe Roehm
School of Information Technologies
University of Sydney, NSW 2006, Australia

In response to

pgsql-jdbc by date

Next:From: roehmDate: 2007-08-16 07:33:24
Subject: Re: autocommit and stored procedures
Previous:From: Mark LewisDate: 2007-08-15 14:25:44
Subject: Re: autocommit and stored procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group