SQL Transactions

SQL Commit Modes

By default an ODBC driver is in auto-commit mode; that means each statement is committed immediately after it is executed. When a batch file (SQL Script) is executed in auto-commit mode, two things are possible: the entire batch file can be treated as an auto-committable unit or each statement in the batch file is treated as an autocommitable unit.

In Advanced Installer, if you supply a batch file statement separator, during installation the SQL script batch file will be automatically split into component statements using the supplied statement separator and each SQL statement will be executed individually (autocommitable unit); otherwise, the batch will be executed as a whole.

When using ODBC, you should not commit or rollback transactions by executing COMMIT or ROLLBACK statements from your SQL scripts. It is not recommended to use in your scripts T-SQL statements that manipulate the transaction mode (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION), because this could lead to unpredictable results.

Transaction Units

Advanced Installer allows you to configure transaction isolation units. You can group one or several SQL script files in a single transaction unit, hence ensuring that the query changes will be committed atomically. At the beginning of each transaction unit, the ODBC driver is put in manual commit mode, and at the end of the unit the changes are committed (if everything went OK), otherwise all changes from the isolation unit are rolled back. After exiting a transaction unit, the ODBC driver is put back in auto-commit mode, so that your following, non-isolated, batch files will execute/commit automatically.

Statements That Cannot Be Rolled Back

Some statements cannot be rolled back. In general, these include data definition language statements (DDL), such as those that create or drop databases, those that create, drop or alter tables or stored routines. You should design your transactions not to include such statements. If you issue a statement that cannot be rolled back early in a transaction, and then another statement later fails, the full effect of the transaction cannot be rolled back.

SQL Statements That Cause An Implicit Commit

When using transaction units, beware of SQL statements that cause an implicit commit. Each of the following statements implicitly end a transaction, as if you had done a COMMIT before executing the statement: ALTER TABLE, CREATE INDEX, DROP INDEX, DROP TABLE, LOCK TABLES, UNLOCK TABLES, RENAME TABLE, CREATE TABLE, CREATE DATABASE, DROP DATABASE, TRUNCATE TABLE, ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, ALTER VIEW, CREATE TRIGGER, CREATE VIEW, DROP TRIGGER, DROP VIEW, etc.