Re: autocommit and stored procedures

[roehm(at)it(dot)usyd(dot)edu(dot)au]

On 16 Aug 2007, at 0:06, Oliver Jowett wrote:

> roehm(at)it(dot)usyd(dot)edu(dot)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