Basic Database Terms
A session is a single connection to SQL Server, identified by a
unique SessionID value. It is initiated through an application
when the open method is used on a connection object or through a
tool like SSMS when the File | Connect menu item is selected.
Even though multiple sessions may originate from the same application (and many
query windows opened by the same user using the same SSMS instance), as far as
SQL Server is concerned, these are all completely separate SQL Server sessions.
Locking occurs when a SQL Server session takes "ownership" of a
resource by acquiring a lock, prior to performing a particular action on that
resource, such as reading or updating. Locking will stay in effect until SQL
Server releases the locks. Note that locking itself is not a problem; it has
very little measurable impact on any aspect of our systems, including
performance, except when it results in blocking or deadlocking, or when we are
performing excessive monitoring of our system locks.
Blocking occurs when at least two sessions desire concurrent access to the same
resource. One session acquires a lock on the resource, in order to perform some
action, and so renders that resource temporarily unavailable to other sessions.
As a result, other sessions requiring the same resource are temporarily
blocked. Typically, the blocked sessions will gain control of the resource
after the blocking session releases the locks, so that access to the resource
is serialized. Note that not all concurrent access will cause blocking; it is
dependent on the operations being performed by the sessions, which determines the
type of locks that are acquired.
A deadlock occurs when two sessions mutually block each other. Neither one can
release the resources it holds until it acquires a lock on the resource the
other session holds. A deadlock can also involve more than two sessions,
trapped in a circular chain of dependencies. For example, session A may hold a
resource that session B wants, and in turn session A is waiting for session C
to release a resource. Session B may also hold a resource that session C wants.
So session A is blocking B and is blocked by C, session B is blocking C and is
blocked by A, and session C is blocking A and is blocked by B. None of the
three sessions can proceed.
• Pressure is a term used to indicate a state where
competition for access to a certain resource is causing performance issues. In
a database with well-designed tables and queries, SQL Server acquires and
releases locks quickly and any blocking is fleeting, and undetectable by the
end-user. However, in certain circumstances, such as when long-running
transactions hold locks on a resource for a long time, or where a very high
number of sessions all require access to the same shared resource, blocking
issues can escalate to the point where one session that is blocked, in turn
blocks other sessions, which in turn block others. As the "queue" of
blocked sessions grows longer, so the load on the system increases and more and
more users start to experience unacceptable delays. In such cases, then we say
that the resource is experiencing pressure.
Transactions
The simplest definition of a transaction is that it is a
single unit of work; a task or set of tasks that together form an
"all-or-nothing" operation. If some event interrupts a transaction in
the middle, so that not all of it was completed, the system should treat
the transaction as if it never occurred at all. Transactions can apply to other
kinds of systems besides databases, but since this is a database-specific book,
we'll be concerned only with database transactions. A transaction can be short,
like changing the price of one book in the inventory, or long, like updating
the quantity sold of every inventory item at the beginning of an accounting
period.
