Export (0) Print
Expand All
7 out of 7 rated this helpful - Rate this topic

Autocommit Transactions

SQL Server 2000

Autocommit mode is the default transaction management mode of Microsoft® SQL Server™. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, SQL Server returns to autocommit mode.

Compile and Run-time Errors

In autocommit mode, it sometimes appears as if SQL Server has rolled back an entire batch instead of just one SQL statement. This happens only if the error encountered is a compile error, not a run-time error. A compile error prevents SQL Server from building an execution plan, so nothing in the batch is executed. Although it appears that all the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. In this example, none of the INSERT statements in the third batch are executed because of a compile error. It appears that the first two INSERT statements are rolled back when they are never executed.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc')  /* Syntax error */
GO
SELECT * FROM TestBatch   /* Returns no rows */
GO

In this example, the third INSERT statement generates a run-time duplicate primary key error. The first two INSERT statements are successful and committed, so they remain after the run-time error.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUES (1, 'ccc')  /* Duplicate key error */
GO
SELECT * FROM TestBatch   /* Returns rows 1 and 2 */
GO

SQL Server uses delayed name resolution, in which object names are not resolved until execution time. In this example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

USE pubs
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBch VALUES (3, 'ccc')  /* Table name error */
GO
SELECT * FROM TestBatch   /* Returns rows 1 and 2 */
GO

See Also

Transactions

Transactions in ODBC

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.