This post is just a quick reference post that outlines the three different types of transaction in Microsoft SQL Server.
AutoCommit
The default. Each statement in a batch works as though it had been individually wrapped in BEGIN TRAN / COMMIT so the following code block:
UPDATE dbo.MyTable SET MyCol = 'Hello' WHERE Id = 1;
UPDATE dbo.MyTable SET MyCol = 'World' WHERE Id = 2;
is effectively the same as:
BEGIN TRAN;
UPDATE dbo.MyTable SET MyCol = 'Hello' WHERE Id = 1;
COMMIT;
BEGIN TRAN;
UPDATE dbo.MyTable SET MyCol = 'World' WHERE Id = 2;
COMMIT;
This moves us nicely on to…
Explicit Transactions
This is the classic transaction. As hinted above, explicit transactions give the user control over when a transaction begins and ends, this is usually used to do multiple modifications where we want all the statements to succeed or all to fail – nothing in between.
We’d couple this with a TRY / CATCH construct to catch any errors* and roll back the transaction if an error occurs:
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.MyTable SET MyCol = 'World' WHERE Id = 2;
UPDATE dbo.YourTable SET YourCol = 'Dual Core DBA' WHERE Id = 1;
COMMIT;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK;
THROW;
END
END CATCH
* There are a number of caveats around what errors TRY / CATCH will catch, but this is outside of the scope of this post.
Implicit Transactions
This is a hybrid of the two above – when you issue certain statements – including SELECT, DML (INSERT, UPDATE, DELETE), and various DDL statements. SQL Server will automatically begin a transaction until you issue a COMMIT or ROLLBACK command.
This behaviour needs to be enabled by issuing a SET IMPLICIT_TRANSACTIONS ON command, though similar behaviour is seen when JDBC-based applications set autoCommit=false, or in frameworks that wrap JDBC and default to explicit transaction management. Implicit transactions are generally used for backwards compatibility with other DBMS code:
SET IMPLICIT_TRANSACTIONS ON;
UPDATE dbo.MyTable SET MyCol = 'World' WHERE Id = 2;
COMMIT;
There is a danger that it may not be obvious to the user that a transaction has been left open, which can cause some blocking issues. This setting is a connection-level setting, however, meaning that in a T-SQL script the SET IMPLICIT_TRANSACTIONS ON statement will at least be visible to anyone reading the code – unlike in application code, where the JDBC driver or framework may enable equivalent behaviour silently.
References / Further Reading
Brent Ozar – Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT