Ok have a question about 'AutoCommit'. Running Sql Anywhere vers 22.214.171.12460 Server OS = Windows2003 build 3790 Service Pack 2
Powerbuilder 11.5, Toad 2.0.20.01
isolation level 0 autocommit=false
This came up as I was evaluating the TOAD software and I reported it as an issue. Another developer pointed out that PowerBuilder's database painter did exactly the same thing that TOAD did.
Currently when I do an update statement in ISQL (just a naked update no begin or end block) I can do a rollback on that statement and return to the original values with no issues.
However if I do the same statement in either the PB Database Painter or a Third party tool (TOAD), I can only do a rollback if the statement is in a complex sql block (wrapped in begin and end) and the rollback has to be contained within the sql block.
The parameter on the ODBC connection to turn autocommit on is blank. All three tools are using the same ODBC connection. Both of the third party tools (PB and TOAD) have their auto commit properties set to false.
Powerbuilder applications with the sqlca.autocommit = false seem to work as expected and rollback when required.
Is this just the way the PB database painter and TOAD handle these transactions ? They seem to interpet any kind of single update statement into a logical unit of work and commit it without being asked to. Or am I missing something in the setup ?
Generations of developers have been baffled and bamboozled by the Evil That Is Auto-Commit. Here's an overview...
The behavior known as "auto-commit" may be implemented (and turned on and off) at BOTH the client and server side.
With SQL Anywhere, server-side auto-commit is controlled by the CHAINED option which defaults to ON which means a transaction is implicitly started before any data retrieval or modification statement which in turn requires an explicit COMMIT or ROLLBACK (no auto-commit behavior).
Client-side auto-commit is controlled by the client software, which includes ODBC and other interfaces. ODBC auto-commit behavior defaults to ON. Client-side auto-commit generally requires the software to send a COMMIT to SQL Anywhere after each INSERT, UPDATE and DELETE.
Other client-side software, like PowerBuilder, can control client-side auto-commit behavior (by telling ODBC to turn off auto-commit for example).
ISQL also has it's own AUTO_COMMIT option which defaults to OFF.
Client-side software can also control server-side auto-commit behavior. For example, if you use Open Client or jConnect, and don't override the sp_tsql_environment stored procedure, CHAINED will be set OFF.
At this point, you are probably even more confused than ever. Here's how to simplify your life:
First, don't try to understand multiple different configurations at one time, just study the one single configuration that you are using, and determine how auto-commit behavior is controlled... on the one single path from application to database that you are using.
Second, TURN OFF AUTO-COMMIT BEHAVIOR... take control of transaction design by taking control of when transactions are committed and rolled back.
If you change the configuration (e.g., use ISQL instead of PB, or JDBC instead of ODBC) then go back to the first step and understand how THAT configuration works. Then repeat step two.
answered 25 Sep '13, 13:40