SQL Server supports three transaction modes: autocommit, explicit, and
implicit. It also supports distributed transactions, which can be transactions
that span multiple servers or multiple databases on one server.
The transaction mode, like isolation, is controlled on a per-connection
Autocommit is the default mode for SQL Server and its APIs. Each transaction
is automatically committed if successful, or rolled back if not. The server
remains in autocommit until an explicit or implicit transaction is requested.
Click here for code example.
This creates a table and inserts the values 5 and 10. The third insert fails,
but it doesn't affect the preceding two.
NULL had been mistyped, perhaps as
batch wouldn't compile and no rows would have been inserted. In 7.0, omitting
GO would move the
CREATE TABLE into the bad batch
and the table would never have been created (6.x requires a
other batch terminator).
With explicit transactions, the developer defines the beginning and end of
the transaction, after which the server returns to whatever transaction mode it
was in before the explicit transaction.
There are four statements available for explicit transactions, but
ultimately, you only need to tell the server where the transaction starts and
BEGIN TRAN[SACTION] [Name]
Starts a local transaction, which can be given a name if desired.
Additional transactions can be nested within the initial transaction but the
server will ignore any names you give them.
The server keeps track of transactions, nested and otherwise, through the @@TRANCOUNT
BEGIN TRAN increments @@TRANCOUNT by 1.
The server will convert a local transaction into a distributed transaction
under some circumstances.
COMMIT TRAN[SACTION] [NAME]/COMMIT [WORK]
Commit marks the end of a transaction; however, it doesn't write
the transaction to the database unless it's the end of the outermost
transaction. Names can be used for readability but they are ignored by the
Commit decrements @@TRANCOUNT by 1. The server won't
permanently commit changes or free locks until @@TRANCOUNT reaches 0. Once it
does, changes can't be undone.
SAVE TRAN[SACTION] NAME
Save is a marker within a transaction. Its purpose is to allow
the server to roll back part of a transaction if necessary. The same name can be
used more than once in a transaction but the server will only roll back to the
most recent use of the name.
Save does not preclude the eventual
need to commit or roll back the entire transaction.
ROLLBACK TRAN[SACTION] [NAME]/ROLLBACK [WORK]
Rollback can do one of two things: roll back to a savepoint, or
roll back the entire transaction. If the latter, all changes are discarded, @@TRANCOUNT
is set to 0, and locks are freed. If rollback is issued within a nested
transaction, everything up to and including the outermost transaction is rolled
If a transaction name is specified, it must match the name of the outermost
transaction or the rollback will be ignored. It's best not to use names if
you're not rolling back to a savepoint.
I frequently wrap my ad hoc DML in explicit transactions to avoid "haste
makes waste" and other issues. Specifically, I check the number of rows
changed. It's not foolproof, but it's good insurance. You can test anything you
can express in T-SQL, and roll it back if it didn't work.
set rowcount 0
-- avoid arbitrary limit
set batch_code = 'BATCH2432'
where batch_code = 'BATCH2422'
if @@rowcount = 20 -- # of rows affected
print 'Failed.' -- can also use raiserror
Rollback figures prominently in triggers (perhaps another article). A trigger
fires each time a user modifies data covered by the trigger. For instance, a
glitch in our Accounts Payable application occasionally marks voucher batches as
After getting tired of clerks asking me where their work went, I put a
trigger on the table involved. The trigger looks for a change in the void_flag
column and rolls it back if the change was inappropriate.
create trigger CannotVoidBatchesWithVouchers
declare @vcount int,
if @@rowcount = 0
select @vcount = count(a.batch_code)
from apinpchg a, inserted i
where a.batch_code = i.batch_ctrl_num and
i.void_flag = 1
if(@vcount) > 0
select @batch = batch_ctrl_num from inserted
raiserror('System attempted to void %s, which
has at least one unposted voucher/DM.', 16,1,@batch)
ROLLBACK rolls back all modifications done by the
transaction up to that point, including any done by the trigger. BOL provides
additional information and explicit transaction examples.
Implicit transactions start automatically like autocommit but need to be
committed or rolled back like explicit. Specifically, if there isn't an existing
transaction, and the server executes any of the usual DML/DDL statements, the
server begins a new transaction and doesn't end it until it encounters either a
Implicit transaction mode can be initiated two ways: with
IMPLICIT_TRANSACTIONS ON or
SET ANSI_DEFAULTS ON. The latter
statement includes implicit transactions among the options it sets.
Start two connections. Execute the code in Connection 1 first, then Connect
2. Connection 2 will time out after twenty seconds, assuming you don't touch
Connection 1 in the meantime.
set implicit_transactions on
create table ##b(i int not null)
insert ##b values(100)
insert ##b values(200)
set lock_timeout 20000
select * from ##b
Connection 2 times out because the transaction in Connection 1 is still
running--it has never been committed or rolled back--and Table ##b is
locked. Notice that @@TRANCOUNT is 1.
LOCK_TIMEOUT, which is measured in milliseconds, is a useful
statement because it prevents having a connection wait forever for locks to be
freed. The alternative is to have the operator break the connection. You can
reset the timeout to infinite by using -1.
Keep the same two connections, but this time run the code in Connection 2
first. Then, run Connection 1. Switch back to Connection 2.
set lock_timeout -1
select * from ##b
You should get the message, "Invalid object name '##b'." The
transaction in Connection 1 ends, locks are freed, and the transaction in
Connection 2 is looking for a table that to it never existed.
Transaction Performance Considerations
- Use as low a level of isolation as possible.
- Never allow user input during a transaction.
- Commit changes as quickly as possible.
- See if large transactions can be broken up into smaller pieces that can be
committed more often. Also, determine if it's possible to use
ROWCOUNT, in conjunction with loops, to break a transaction that
modifies many rows into several transactions that modify fewer rows.
- Don't use implicit transactions if you don't regularly work with them,
perhaps in another DBMS.
- Put @@TRANCOUNT in your code to clarify nesting issues and ensure that
transactions are being committed or rolled back.
- Use the server's tools to identify issues caused by open transactions.
These tools include the Enterprise Manager, Profiler, sp_lock, DBCC OPENTRAN,
and NT Performance Monitor. There are numerous scripts and stored
procedures, some from Microsoft, that augment the data these tools provide.
- BOL has extensive information on optimizing transactions.
Transactions are what SQL Server is all
about. The server does an immense amount of work behind the scenes to support
multiple users, but ultimate responsibility for keeping everyone running smoothly
lies with the developer.