Database Reference
In-Depth Information
a part in deadlocks, but these are three very distinct concepts. Please understand the differences between these terms
and use them correctly. It will help in your understanding of the system, your ability to troubleshoot, and your ability
to communicate with other database administrators and developers.
In SQL Server, a database connection is identified by a session ID. Connections may be from one or many
applications and one or many users on those applications; as far as SQL Server is concerned, every connection is
treated as a separate session. Blocking between two sessions accessing the same piece of data at the same time is a
natural phenomenon in SQL Server. Whenever two sessions try to access a common database resource in conflicting
ways, the lock manager ensures that the second session waits until the first session completes its work in conjunction
with the management of transactions within the system. For example, a session might be modifying a table record
while another session tries to delete the record. Since these two data access requests are incompatible, the second
session will be blocked until the first session completes its task.
On the other hand, if the two sessions try to read a table concurrently, both requests are allowed to execute
without blocking, since these data access requests are compatible with each other.
Usually, the effect of blocking on a session is quite small and doesn't affect its performance noticeably. At
times, however, because of poor query and/or transaction design (or maybe bad luck), blocking can affect query
performance significantly. In a database application, every effort should be made to minimize blocking and thereby
increase the number of concurrent users who can use the database.
With the introduction of in-memory tables in SQL Server 2014, locking, at least for these tables, takes on whole
new dimensions. I'll cover their behavior separately in Chapter 23.
Understanding Blocking
In SQL Server, a database query can execute as a logical unit of work in itself, or it can participate in a bigger logical
unit of work. A bigger logical unit of work can be defined using the
BEGIN TRANSACTION
statement along with
COMMIT
and/or
ROLLBACK
statements. Every logical unit of work must conform to a set of four properties called
ACID
properties:
•
Atomicity
•
Consistency
•
Isolation
•
Durability
I cover these properties in the sections that follow because understanding how transactions work is fundamental
to understanding blocking.
Atomicity
A logical unit of work must be
atomic.
That is, either all the actions of the logical unit of work are completed or no
effect is retained. To understand the atomicity of a logical unit of work, consider the following example:
USE AdventureWorks2012;
GO
IF (SELECT OBJECT_ID('dbo.ProductTest')
) IS NOT NULL
DROP TABLE dbo.ProductTest;
GO
CREATE TABLE dbo.ProductTest (
ProductID INT CONSTRAINT ValueEqualsOne CHECK (ProductID = 1));
GO
