MS SQL Server 2012 - DBA Articles

AUTOCOMMIT Transaction Process Flow



AUTOCOMMIT mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back, when it complets. If a statement completes successfully, it is committed, if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in AUTOCOMMIT mode whenever this default mode has not been overridden by either explicit or implicit transactions.

Compile and Run-time Errors

In autocommit mode, it sometimes appears as if an instance of the Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error.

Compile Error Example

-- Example 66 --

 

USE EBusiness

GO

 

CREATE TABLE Product_Test

(

Prod_Code varchar(5) PRIMARY KEY,

Prod_Name varchar(50) NOT NULL,

)

GO

 

INSERT INTO Product_Test VALUES ('P01','Product A');

INSERT INTO Product_Test VALUES ('P02','Product B');

INSERT INTO Product_Test VALUES ('P03','Product C); -- Syntax Error

 

SELECT * FROM Product_Test – Return No Record(s)


Query Output Screen



Run-time Error Example

-- Example 67 --

 

USE EBusiness

GO

 

CREATE TABLE Product_Test

(

Prod_Code varchar(5) PRIMARY KEY,

Prod_Name varchar(50) NOT NULL,

)

GO

 

INSERT INTO Product_Test VALUES ('P01','Product A');

INSERT INTO Product_Test VALUES ('P02','Product B');

INSERT INTO Product_Test VALUES ('P01','Product C'); -- Duplicate Record

 

SELECT * FROM Product_Test -- Return P01 and P02 Records.


Query Output Screen



* * * * *


Email Your Comment To AUTHOR