Thread: autocommit and stored procedures
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
On Wed, 2007-08-15 at 21:21 +1000, roehm@it.usyd.edu.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
roehm@it.usyd.edu.au wrote: > 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? It effectively commits after each client-side JDBC statement execution, even if you provide multiple semicolon-separated queries in a single statement. At the protocol level the driver converts a single JDBC statement into one or more groups of Parse/Bind/Execute messages, followed by a single Sync. You can see this if you look at the debugging produced with loglevel=2. Also see http://www.postgresql.org/docs/8.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY Regardless of that, there's no way I know of to get "autocommit" within a stored procedure as you seem to be wanting, regardless of what client driver you're using, because the server will always set up a transaction (one way or another) before executing the top-level query that runs your stored procedure so by the time your stored procedure is running it's too late to think about transaction demarcation. > Does anyone know what the specified behaviour is for JDBC AutoCommit? See the javadoc for Connection.setAutoCommit(): > The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statementsreturning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrievedor the ResultSet object has been closed. In advanced cases, a single statement may return multiple results as wellas output parameter values. In these cases, the commit occurs when all results and output parameter values have beenretrieved. -O
On Thu, 2007-08-16 at 02:06 +1200, Oliver Jowett wrote: > roehm@it.usyd.edu.au wrote: > > > 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? > > It effectively commits after each client-side JDBC statement execution, > even if you provide multiple semicolon-separated queries in a single > statement. At the protocol level the driver converts a single JDBC > statement into one or more groups of Parse/Bind/Execute messages, > followed by a single Sync. You can see this if you look at the debugging > produced with loglevel=2. Also see > http://www.postgresql.org/docs/8.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY Did this change with v3 protocol? I'm not positive, but I remember thinking that it used to commit each statement individually quite a while back, but that would have been in the days of the v2 protocol. If that's the case, then maybe using v2 protocol would suffice for executing multiple statements at a time with a single auto-commit? But yeah, none of this really seems to apply to the original poster's question anyway. -- Mark Lewis
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@it.usyd.edu.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
On 16 Aug 2007, at 0:06, Oliver Jowett wrote: > roehm@it.usyd.edu.au wrote: > >> 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? > > It effectively commits after each client-side JDBC statement > execution, even if you provide multiple semicolon-separated queries > in a single statement. At the protocol level the driver converts a > single JDBC statement into one or more groups of Parse/Bind/Execute > messages, followed by a single Sync. You can see this if you look > at the debugging produced with loglevel=2. Also see http:// > www.postgresql.org/docs/8.2/static/protocol-flow.html#PROTOCOL-FLOW- > EXT-QUERY > Ah - I wasn't aware of that debug mode. Thanks! > Regardless of that, there's no way I know of to get "autocommit" > within a stored procedure as you seem to be wanting, regardless of > what client driver you're using, because the server will always set > up a transaction (one way or another) before executing the top- > level query that runs your stored procedure so by the time your > stored procedure is running it's too late to think about > transaction demarcation. > I did not say anything about 'what I want' ;-) As a matter of fact, postgres' behaviour is what we wanted and expected, i.e. the call to execute a stored procedure gets committed, meaning that all the statements within are running within the same transaction context. I came up with this question when we were doing some tests regarding snapshot isolation on different database engines, but with some unexpected results from the very same client code. Apparantly SQL Server does a 'transitive' autocommit which promotes inside stored procedures, committing each individual statement within! >> Does anyone know what the specified behaviour is for JDBC AutoCommit? > > See the javadoc for Connection.setAutoCommit(): > That's your documentation and not necessarily the JDBC specification ;-) I wonder whether this is a hole in that spec or a bug... Cf. also this part of Sybase's SQL Anywhere documentation: http://www.ianywhere.com/developer/product_manuals/sqlanywhere/ 1000/en/html/dbpgen10/pg-autocommit-implementation-sqlapp.html According to this, the autocommit is controlled at the server side (for Sybase Anywhere SQL). Now which definition of the behaviour is correct? Again: Our test client and the transactions coded as stored procedures behave different depending on whether we use postgres or SQL Server as backend... (if we set Autocommit(true)) >> The commit occurs when the statement comp >> letes or the next execute occurs, whichever comes first. In the >> case of statements returning a ResultSet object, the statement >> completes when the last row of the ResultSet object has been >> retrieved or the ResultSet object has been closed. In advanced >> cases, a single statement may return multiple results as well as >> output parameter values. In these cases, the commit occurs when >> all results and output parameter values have been retrieved. > > -O Great - thanks. Uwe
roehm@it.usyd.edu.au wrote: >> See the javadoc for Connection.setAutoCommit(): >> > That's your documentation and not necessarily the JDBC specification ;-) Err I am talking about: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#getAutoCommit() which certainly is part of the JDBC specification. -O
On 16 Aug 2007, at 17:44, Oliver Jowett wrote: > roehm@it.usyd.edu.au wrote: > >>> See the javadoc for Connection.setAutoCommit(): >>> >> That's your documentation and not necessarily the JDBC >> specification ;-) > > Err I am talking about: > > http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ > Connection.html#setAutoCommit() > > which certainly is part of the JDBC specification. > > -O Thanks, that's better - the documentation of Sun's reference implementation. Btw: The actual JDBC specification can be downlaoded from here: http://java.sun.com/products/jdbc/download.html#corespec30 In Chapter 10.1, it seems to define the autocommit behaviour as discussed so far (commit outside the stored procedure after all of its results have been received). Uwe
roehm@it.usyd.edu.au wrote:
> 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?
A function in PostgreSQL is always executed within one single
transaction, so you cannot commit only part of the SQL-statements
within a function.
The JDBC 4.0 specification says:
The Connection attribute auto-commit specifies when to end transactions. Enabling
auto-commit causes a transaction commit after each individual SQL statement as
soon as that statement is complete. The point at which a statement is considered to
be "complete" depends on the type of SQL statement as well as what the application
does after executing it:
■ For Data Manipulation Language (DML) statements such as Insert, Update,
Delete, and DDL statements, the statement is complete as soon as it has finished
executing.
■ For Select statements, the statement is complete when the associated result set
is closed.
■ For CallableStatement objects or for statements that return multiple results,
the statement is complete when all of the associated result sets have been closed,
and all update counts and output parameters have been retrieved.
To me that seems to mean that if I do
executeUpdate("SELECT myfunction()")
the autocommit should occur after the select statement is finished, i.e.
after the complete function has been executed.
But I'll admit that this is disputable...
Yours,
Laurenz Albe
On 16-Aug-07, at 5:46 AM, Albe Laurenz wrote:
> roehm@it.usyd.edu.au wrote:
>> 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?
>
> A function in PostgreSQL is always executed within one single
> transaction, so you cannot commit only part of the SQL-statements
> within a function.
>
> The JDBC 4.0 specification says:
>
> The Connection attribute auto-commit specifies when to end
> transactions. Enabling
> auto-commit causes a transaction commit after each individual SQL
> statement as
> soon as that statement is complete. The point at which a statement
> is considered to
> be "complete" depends on the type of SQL statement as well as what
> the application
> does after executing it:
>
> ■ For Data Manipulation Language (DML) statements such as Insert,
> Update,
> Delete, and DDL statements, the statement is complete as soon as
> it has finished
> executing.
> ■ For Select statements, the statement is complete when the
> associated result set
> is closed.
> ■ For CallableStatement objects or for statements that return
> multiple results,
> the statement is complete when all of the associated result sets
> have been closed,
> and all update counts and output parameters have been retrieved.
Isn't that interesting I wonder who's database has the above select
and CallableStatement semantics.
Dave
>
> To me that seems to mean that if I do
>
> executeUpdate("SELECT myfunction()")
>
> the autocommit should occur after the select statement is finished,
> i.e.
> after the complete function has been executed.
>
> But I'll admit that this is disputable...
>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match