Transactions allow you to define a unit of activity that will be considered atomic–all or nothing. The data will be considered consistent at the beginning of the transaction and at the end. Locks are obtained to isolate data resources, preventing (blocking) other processes from performing incompatible activities against those resources. You can control the degree of isolation of your transaction by specifying an isolation level. I will describe isolation levels in more detail later in the chapter. The database’s transaction log guarantees that a committed transaction recorded within it is durable–that is, the change will reach the database. The aforementioned aspects of transactions are known as ACID (Atomicity, Consistency, Isolation, and Durability).

One aspect of database programming that determines whether a project rises or falls is the application’s treatment of concurrency–multiple processes interacting with the same data. You have to understand the vision of the designers of the database product in terms of concurrency to develop well-behaving applications.

For example in Oracle, writers never block readers unless the reader requests that behavior explicitly. This functionality is supported because the product is able to construct an earlier consistent (committed) version of the data. In the past, Microsoft SQL Server maintained only one image of each row. An uncommitted change applied by a process to a row rendered an inconsistent state of the row. Another process attempting to read the row could not obtain an earlier consistent version. Rather, it could either wait for the other process to commit, or it could request the inconsistent state of the row (commonly referred to as a dirty read).

I will not get into the argument of which vision is better than the other–both have pros and cons. My take on the subject comes from a pragmatic point of view–you can develop successful projects with either vision, but you have to understand it. Programmers that are used to one vision and then start developing in a new one might find the shift in mindset hard, and they don’t always make it. That’s a recipe for a project that is doomed to fail.

For example, imagine that you’re used to programming in an environment where you’re never blocked when you read data–that is, the database platform can construct an earlier consistent version of rows. And then you start developing in an environment where the database platform maintains only one image of a row. Suddenly you’re blocked when you try to read data that has been changed by an uncommitted transaction. You’re not familiar with the concept of being blocked when you read data, and you don’t know quite how to handle the situation yet. The easiest way to cope with the situation is to change all your reads to dirty reads. You might not be aware of the implications of such a request–that you might be getting data in an inconsistent state. The data might be in an intermediate state within a transaction, and it might even end up not being committed at all. In short, you need to understand the vision behind concurrency control in the database platform you are working with to develop successful projects.

SQL Server 2005 introduces new ways to handle concurrency via two new isolation levels, based on the ability to construct an earlier consistent version of a row. This ability is achieved thanks to a new row versioning technology incorporated within the engine, which serves other aspects of the product as well. Now you can choose the model you want to work with. Later in the chapter, I will describe the new isolation levels and the circumstances in which you would benefit from using them.

More Info

Concurrency, transactions, and row versioning are described in great depth in Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney (Microsoft Press, 2006). Though Kalen covers the subject in depth, I found the idea of not covering the subject in a book that focuses on T-SQL programming unthinkable. So here I’ll provide an overview of the subject, and I’ll give you the fundamentals you need to program transactions with T-SQL.