SQL Server Concurrency Control: Locking, Blocking and Row Versioning
Kalen Delaney
4th November 2008
Prior to SQL Server 2005, SQL Server managed concurrency pessimistically, using locking and blocking to prevent multiple users from updating the same data simultaneously and to provide data consistency. In this seminar we will look at the concurrency enhancements made in SQL Server 2005, based on a technology called row-level versioning (RLV) to implement optimistic concurrency. We'll compare concurrency management with RLV to concurrency management with locking and blocking. RLV provides a new isolation level called Snapshot Isolation that allows readers of data to not be blocked by writers. The seminar will compare the new isolation level with the previous ones and provide guidelines as to how to determine which isolation level is appropriate for your needs, and what application changes might be necessary to achieve the level desired. We will also look at the concurrency enhancements added to SQL Server 2008.
Goals
- Compare the differences between Pessimistic and Optimistic Concurrency Control in SQL Server
- Understand how your application's transaction control affects concurrency
- Explore of SQL Server's locking and blocking behaviours
- Learn how to access SQL Server metadata to observe the locking, blocking and row versioning
- Differentiate between the two snapshot based isolation levels: Read Committed Snapshot and Full Snapshot
Prerequisites
A basic understanding of batches and transactions in SQL Server and some experience writing TSQL-based applications.
Course Length & Type
One day instructor led seminar.
Location
4th November 2008 Rothamsted Manor, Harpenden
Conveniently located only 4 miles from the M1 and close to Harpenden railway station with good links to London. Rothamsted Manor is part of the larger Rothamsted Research facility, but retains a secluded and tranquil feel ideal for learning.
Events are held in The Great Drawing Room at the manor house itself. For full directions see Rothamsted Manor's How to Find Us guide.
The Great Drawing Room Rothamsted Manor & Conference Centre
Rothamsted Research
Harpenden
AL5 2JQ
Kalen Delaney
Kalen Delaney has worked with SQL Server for over 20 years, starting with employment with the Sybase Corporation in 1987. She worked at Sybase in technical support and training until 1992, when she became an independent trainer and consultant. Kalen now offers the most advanced SQL Server Internals training in the world, and you can read about her and her course on her website http://www.insidesqlserver.com/.
In addition to teaching and consulting, Kalen has been writing for SQL Server Magazine since its first issue, and has written or contributed to several advanced SQL Server books. Inside SQL Server 2005: The Storage Engine was published in October 2006, and her latest book Inside SQL Server 2005: Query Tuning and Optimization was published in September 2007. She is currently working on her next book, covering the internals of SQL Server 2008.