SQL Server Solution to Concurrency
In my previous post (Isolation Levels 101: Concurrency Issues), I discussed Concurrency Problems that can sometimes happen during execution of statements. In Part 2, I am going to discuss how SQL Server uses locks to help prevent concurrency issues and keeps your data integrity intact.
SQL Server’s best method of preventing concurrency errors are to Isolate the transaction and LOCK the database objects. SQL uses many different types of locks depending on the operation, transaction and type of SQL statement. The most common locks used are “Shared Locks”, “Exclusive Locks” and “Update Locks”.
Understanding Locks
The SQL engine will by default lock as few resources as necessary to complete its task. Each lock allows SQL to perform differently.
Shared Locks
Shared locks are used for read operations (SELECT statements) that do not change or update data. This type of lock allows other read operations to be executed simultaneously. But it prevents other transactions from altering the data that is being read.
Exclusive Locks
The “Exclusive Lock” is used for data-modification operations (INSERT, UPDATE, or DELETE). Exclusive locks purpose is to ensure multiple operations cannot change the same data at the same time.
Update Locks
Update locks are a little trickier, their intent is to prevent a common form of “deadlocks. A transaction reads some data using a shared lock then it attempts to update the same data. This in turn changes the shared lock to an Update Lock.
Intent Locks
To maintain integrity, SQL Server will place an “intent lock” on a higher-level object if a lower level object is currently locked by another type of lock. This type of locks prevents you from changing the schema of a table if that table is currently being updated by an application.
Types of Isolation
Isolation Levels are methods SQL Server uses to manipulate and control data integrity. In my previous post (Isolation Levels, Part 101), I presented to you the major concurrency problems. Concurrency problems can be resolved or controlled by which Isolation Level you use.
The ISO standard has 4 different definitions of isolation level for the manipulation of data, which SQL Server gladly supports.
Read Uncommitted
This level of isolation is truthfully a lack of isolation, meaning no Shared Locks are implemented so data read into a result set can be altered while the SELECT statement is being executed. For example, if you are trying to select 5,000 rows from a table which may take some time, a 2nd transaction could UPDATE row number 4,999 before the SELECT statement can complete the data set requested. This is what we call a “dirty read”
What some T-SQL Developers do not realize is the query hint “WITH (NOLOCK)” is the equivalent of Read Uncommitted Isolation level.
Read Committed
This is the default Isolation Level for SQL Server and its sole purpose is to prevent “dirty reads”. It does this by placing a SHARED LOCK on the table when it is read. This lock will allow other SELECT transactions to read the same data but will not allow INSERT, UPDATE or DELETE transactions. These subsequent transactions will be BLOCKED until the first SELECT transaction completes.
Repeatable Read
Imagine if you will a SELECT statement at the beginning of a transaction. Then some data manipulation occurs on another table based on the results of SELECT #1, then a 2nd SELECT statement tries to reload the same dataset from the first part of the transaction. Between the two SELECT statements a 2nd transaction UPDATES the table and you get two separate result sets from SELECT 1 and SELECT 2. This occurs because SHARED LOCKS are “statement aware” not transactional aware.
The Repeatable Read Isolation level prevents this from happening. If the two SELECT statements are in the same transaction, then the first result set will have the same data as the 2nd result set.
Serializable
This is the highest level of isolation available to the SQL Server engine. This will isolate the table in serial order until the transaction completes. No other transaction can INSERT, UPDATE, DELETE or even SELECT from the tables. This is precisely how building indexes occur, one row at a time.
Looking Ahead
Next time, I will put these two principles together and using some fancy T-SQL will demonstrate the behavior of Isolation Levels.