Locking and Blocking, Family Management.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

For any parents out there of multiple children, you are acutely aware of locking and blocking in the real world.  Who gets to use the bathroom first?  Hearing the phrase “I saw that first”.  I want that.  Fighting over a toy.  Each parent has their own methods of how to resolve these disputes.

Just as in a home with siblings, locking and blocking are absolutely a part of life in SQL Server and most RDBMS. Understanding how locking and blocking occurs and then minimizing the affects in accessing our precious information is a high priority.

Database Engines treat the data as children in this respect.  Keeping the data as orderly as possible, without too much fighting.

First let’s take a look at some basic principles.

ACID

ACID is an acronym that stands for Atomicity Consistency Isolation Durability. This is a principle that Database professionals will look to when evaluating basics of a database. This basically means that the data can be trusted.

  • Atomicity – A transaction is an entire unit of work. All or NONE of the transaction is committed.
  • Consistency – All of the data is in a state of equilibrium. Indexes, materialized views and anything else affected by the transaction is consistent and in a zen state.
  • Isolation – The idea that the data modifications are left alone until they are finished. Transactions are kept away from each other. Like two children being sent to their rooms to clean.
  • Durability – The RDBMS keeps track of these changes and that after the transaction is complete, that data is recorded in the database permanently and can be recovered in the event of a system failure.

 

Isolation Levels

In SQL Server, and other RDBMS, the engine implements the configured isolation level which will affect the ACIDity when reading data. The isolation level will isolate the process and protect it from other transactions. To do this, locking is required. The locking is determined by the isolation level. Low levels of isolation will give high levels of concurrency and shared use of the data, but can result in lower levels of consistency with dirty or phantom reads.

SQL Server will use isolation levels to command the read behavior of the database/transaction.

  • Read Uncommited
    • Read data from other transactions yet completed
    • Dirty Reads
    • Uncommitted updates
  • Read Committed
    • SQL Server Default
    • Prevents reading Data from uncommitted transactions
    • Can result in Phantom Reads/Repeatable Reads
  • Repeatable Read
    • This will ensure that if data is reread within a transaction, that the data does not change
    • No transaction can modify the data until the transaction reading the data is complete
    • This comes at the cost that all shared locks in the read will hold until the transaction is completed
    • This will eliminate Dirty and Non-Repeatable reads, but Phantom reads may still occur.
  • Serializable
    • This is putting the transactions in a single line.
    • This is the most consistent and best way to ensure for ACIDity.
    • A read/write lock will be issued for all of the records affected by the transaction
    • This includes Repeatable Read isolation and will also eliminate Phantom reads.
  • Snapshot
    • This will read the data as it is at the beginning of the transaction.
    • This will come at a high cost to tempdb as the data for the snapshot is stored in tempdb.
    • This will eliminate Dirty, Phantom, Non-Repeatable Read and Lost updates

Lower isolation levels offer greater concurrency, meaning more connections may view the data, but the following concurrency affects may occur:

  • Lost Updates – Transaction A changes a value, then commits. Transaction B then also changes that value and commits. Transaction A then reads the data after Transaction B commits. Transaction A is expecting the first value, but obtains the second
  • Dirty Reads – Reading data when another process is changing the data and the original process is reading uncommitted data.
  • Non-repeatable Reads – This occurs when a process reads the same data multiple times within a session. Between reads of the data, another process may change the data and therefore different values are returned.
  • Phantom Reads – This is similar to Non-Repeatable reads, but instead of changing(updating) the data, another process adds or removes records between the reads of the same data during a process.

Locking Explained

Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.  Locks are managed internally by a part of the Database Engine called the lock manager.

Each transaction will lock levels of resources such as rows, pages or tables for which the transaction is dependent.  The transaction will release the lock when it either commits or rolls back.  The amount of time the lock is held is dependent on the isolation level.

The two basic types of locks are read locks and write locks.

  • Read Locks
    • Shared –While a shared lock is being held other transactions can read but cannot modify locked data.  The lock is released after the locked data has been read unless the isolation level is at or higher than Repeatable Read or a locking hint such as READCOMMITED or READCOMMITTEDLOCK is used.
    • Concurrent – This is when you read data using read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level.
  • Write Locks
    • Update – Update locks are a mix of shared and exclusive locks. When an update statement is executed, SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. The update lock itself can’t modify the underlying data, when the data is modified, it is transferred to an exclusive lock.
    • Exclusive – Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions.  A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.

 

Lock Granularity/Escalation

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock granularity consists of DATABASE, TABLE, PAGE and ROW locks.  As soon as you connect to a SQL Server database, a shared lock is issued on the database level itself. This prevents another user from performing an action such as “DROP DATABASE” while there is a connection to it.

SQL Server will perform it’s lock granularity from top down. First, it will check if a table lock is necessary, then page then row. An Intent(Shared or Exclusive), dependent if the operation is read or write, is issued on the table and page and a shared lock on the row. If the amount of data necessary is more than a row and on a page. Once SQL Server needs more than 5,000 row locks, it will escalate(by default) to a table lock.

You can alter these defaults by either using query hints such as ROWLOCK, PAGLOCK or TABLOCK. You can also alter the lock escalation of each table by using the following:


This is generally not recommended as it is best to allow the Database Engine to escalate locks accordingly. Lower level locks increase concurrency, but consume more memory.

 

Fun Scripts

So, we now know that locking and blocking are part of the natural occurrence of processing transactions.  When it comes to blocking the objective is to lessen the affects and hold the blocks as little time as possible.  Below, you will find two scripts.  The first will list was locking is occurring within your database.  The second will list your current processes and list the ones blocking first.  These may help in ascertaining what issues need to be addressed regarding blocking.

Current processes, showing blocking first

 

 Locks currently held within the database

 

Coming up: Deadlocks – Who gets the toy?

References:

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server#sthash.ujDqLUPK.dpuf

http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *