Transactions 101 - Page 2

Transaction Modes

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

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.

If NULL had been mistyped, perhaps as NUL, the batch wouldn't compile and no rows would have been inserted. In 7.0, omitting the GO would move the CREATE TABLE into the bad batch and the table would never have been created (6.x requires a GO or other batch terminator).

Explicit

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 variable. Each 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 server.

Each 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 back.

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.

Examples

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

The 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

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 COMMIT or ROLLBACK.

Implicit transaction mode can be initiated two ways: with SET 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)
go

insert ##b values(100)
insert ##b values(200)

select @@trancount

set lock_timeout 20000
go

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
go

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

1. Use as low a level of isolation as possible.
2. Never allow user input during a transaction.
3. Commit changes as quickly as possible.
4. 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.
5. Don't use implicit transactions if you don't regularly work with them, perhaps in another DBMS.
6. Put @@TRANCOUNT in your code to clarify nesting issues and ensure that transactions are being committed or rolled back.
7. 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.
8. BOL has extensive information on optimizing transactions.

Summary

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.