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: 9DFF90BE-2E11-4621-BF35-C531EB0953F2@it.usyd.edu.au (view raw or flat)
Thread:
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  
driver,
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.

Uwe


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)
>> DECLARE	
>>     cid INTEGER;
>> BEGIN
>>     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-2014 The PostgreSQL Global Development Group