Transaction Processing

As discussed back in Part I and earlier in this chapter, the essence of a client/server DBMS is its capability to provide access to multiple users concurrently. This is achieved through establishing a session to the server and then in that session sending a series of commands to the server; possibly receiving back a result set, single values, or status information. When you connect to the server, the execution of your code at the server takes place inside a construct known in DBMS parlance as a transaction.

A transaction is considered a single unit of work by the DBMS. No matter what you do in the code, nested stored procedures, triggers, constraints, cascading deletes or updates, and so on, the transaction is still regarded as a single unit of work.

I know then that you would probably agree-especially if you are a DBA or a software developer-that our lives could also be considered transactions, single units of work. When you take all the activities and, logically, look at them as one unit, a life, you can see a single transaction, with each activity in it connected to the next. Life is one workload from the day we begin to the day we are committed. It is often said that when you die, time stands still and your entire life is replayed for you. We will not be able to confirm that until it happens to one of us, and we do not really know who controls our transaction logs. But DBMS transactions too have the capability to roll back. This capability to completely undo a transaction, before it too is committed, is called the transaction rollback.

The inherent rollback of a transaction thus makes it not only a logical unit of work but also a logical unit of recovery, because everything that is done within the scope of the transaction is undone. The rollback ensures that, if at any point in the middle of transaction something goes wrong, the DBMS can undo the work that has preceded the processing up the point of the rollback. This makes the transaction an all-or-nothing event. Either everything in the transaction happens, or nothing happens. This is often referred to as the atomicity of the transaction.

If a DBMS were to allow certain statements in the transaction to carry on, and the changes they caused to persist, that would lead to suspect data, or data that cannot be considered to be in a consistent or reliable state. Even if everything that was done in the transaction up to the rollback state was copacetic, we still cannot allow only parts of a transaction to complete. It is almost impossible, and taxing on a system, to obtain up-to-the-nanosecond checks that data is “safe,” because at any time in the transaction a system can fail.

The transaction management architecture in SQL Server, however, provides the next best thing to the nanosecond safety check. As long as the data persists in the write-ahead transaction log, the transaction will be committed to the database. But if the data is not in any usable form, the server will roll back the transaction to the state before the transaction began. Starting all over, so to speak, as if the transaction had not even started. This mechanism ensures the consistency of the data.

SQL Server employs a sophisticated transaction manager monitor that ensures an atomicity or “all-or-nothing” rule and a data consistency rule. The transaction’s start is signaled by the BEGIN TRANSACTION statement, which tells the transaction processor (TP) that work has begun. Transaction failure is signaled explicitly by the ROLLBACK statement, or automatically if the TP itself detects a failure. If the transaction completes successfully, the TP is given the “thumbs-up” with the COMMIT statement and the data is committed to the database. At this point, even if the system crashes, the updates or inserts to the database survive. This is known as the durability rule for a DBMS.

Note 

If you are wondering if the COMMIT can be undone, it can. You can use the transaction log to rebuild the database to the point of a disaster that was still logically sound according to the TP. For example, if you delete a bunch of data in a human error (like truncating the wrong table), transaction log recovery will heal that (see Chapter 8).

You will recall from the discussion of locking that transactions are isolated from each other (see the section “Isolation” earlier in this chapter). Following this, we can say that transactions have four important properties, which have become fondly known as the ACID properties or laws-atomicity, consistency, isolation, and durability. A transaction must adhere to the laws or it is considered to have failed the “ACID test.”