Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

Concurrency can be defined as the ability of multiple processes to access or change shared data at the same time. The greater the number of concurrent user processes that can be active without interfering with each other, the greater the concurrency of the database system.

Concurrency is reduced when a process that is changing data prevents other processes from reading that data or when a process that is reading data prevents other processes from changing that data. I'll use the terms reading or accessing to describe the impact of using the SELECT statement on your data. Concurrency is also affected when multiple processes attempt to change the same data simultaneously and they cannot all succeed without sacrificing data consistency. I'll use the terms modifying, changing, or writing to describe the impact of using the INSERT, UPDATE, or DELETE statements on your data.

In general, database systems can take two approaches to managing concurrent data access: optimistic or pessimistic. Microsoft SQL Server 2005 supports both approaches. Pessimistic concurrency was the only concurrency model available before SQL Server 2005. In SQL Server 2005, you specify which model to use by using two database options and a set option called TRANSACTION ISOLATION LEVEL.

After I describe the basic differences between the two models, we'll look at the five possible isolation levels in SQL Server 2005, as well as the internals of how SQL Server controls concurrent access using each model. We'll look at how to control the isolation level, and we'll look at the metadata that shows you what SQL Server is doing. As you'll see, optimistic concurrency is supported by the use of a new technology called row versioning; at the end of this chapter, I'll tell you about additional SQL Server 2005 features that use row versioning.