Transactions 101 - Page 2
May 30, 2001
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.
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
The server will convert a local transaction into a distributed transaction under some circumstances.
COMMIT TRAN[SACTION] [NAME]/COMMIT [WORK]
SAVE TRAN[SACTION] NAME
ROLLBACK TRAN[SACTION] [NAME]/ROLLBACK [WORK]
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.
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.
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
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.
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
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.