Three different Transactions Modes

Transactions are nested when one transaction is contained within another. Creating transactions and nested transactions are covered in a lesson later in this module.

There are three different modes within which Microsoft SQL Server operates. They are:

1. Auto-commit: Each Transact-SQL statement is contained within its own transaction. This mode is selected by issuing the SET IMPLICIT_TRANSACTIONS OFF Transact-SQL statement.
2. Implicit: Transactions are automatically started at the end of a prior transaction. These types of transactions must be explicitly committed or rolled back. This mode is selected by issuing the SET IMPLICIT_TRANSACTIONS ON Transact-SQL statement.
3. Explicit: Transactions are defined by using the BEGIN, COMMIT, or ROLLBACK statements. This mode is set by using one of these statements.

Transaction information is stored in the database’s transaction log. The transaction log is used to store all of the changes in data to a database. Transaction logs are discussed more thoroughly in an earlier lesson in this module.
The next lesson will show you how isolation levels affect transactions.

Transactions and locks tend to be two of the most misunderstood areas in the database world. As such, this concept is going to make you start to look like a real pro. Transactions are all about atomicity. Atomicity is the concept that something should act as a unit. From a database standpoint, it is about the smallest grouping of one or more statements that should be considered to be all or nothing. Often, when dealing with data, you want to make sure that if one thing happens, another thing happens, or that neither of them does. Indeed, this can be carried out to the degree where 20 things (or more) all have to happen together or nothing happens. Let us look at a classic example. Imagine that you are a banker and Glenn comes in and wants to transfer $1,000 from checking to savings. You are, of course, happy to oblige, so you process her request. Behind the scenes, something like this is happening:

UPDATE checking
SET Balance = Balance - 1000
WHERE Account = 'Glenn'
UPDATE savings
SET Balance = Balance + 1000
WHERE Account = 'Glenn'

This is an over-simplification of what is going on, but it captures the main thrust of things: You need to issue two statements, 1) one for each account. Now what if the first statement executes and the second one does not? Glenn would be out a thousand dollars and that might, for a short time, seem okay from your perspective, but not for long. By that afternoon you would have a steady stream of customers leaving your bank.

[1]nested: A nested transaction occurs when a new transaction is started by an instruction that is already inside an existing transaction.