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 basis.
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.
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 ends.
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 begin tran update apinpchg set batch_code = 'BATCH2432' where batch_code = 'BATCH2422' if @@rowcount = 20 -- # of rows affected commit tran else begin print 'Failed.' -- can also use raiserror rollback tran end
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 void.
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 on batchctl for update as declare @vcount int, @batch varchar(15) if @@rowcount = 0 return if update(void_flag) begin 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 begin 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 transaction end end
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.
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.
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
SET 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.