FAQ on Lock in SQL Server

FAQ on Lock in SQL Server

74. What are locks?
Microsoft® SQL Server™ 2000 uses locking to ensure transactional
integrity and database consistency. Locking prevents users from

reading data being changed by other users, and prevents multiple users
from changing the same data at the same time. If locking is not used,
data within the database may become logically incorrect, and queries
executed against that data may produce unexpected results.

75. What are the different types of locks?
SQL Server uses these resource lock modes.
Lock mode Description
Shared (S) Used for operations that do not change or update data
(read-only operations), such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common
form of deadlock that occurs when multiple sessions are reading,
locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks
are: intent shared (IS), intent exclusive (IX), and shared with intent
exclusive (SIX).
Schema Used when an operation dependent on the schema of a table is
executing. The types of schema locks are: schema modification (Sch-M)
and schema stability (Sch-S).
Bulk Update (BU) Used when bulk-copying data into a table and the
TABLOCK hint is specified.

76. What is a dead lock? Give a practical sample? How you can minimize
the deadlock situation? What is a deadlock and what is a live lock?
How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one
piece of data, attempt to acquire a lock on the other's piece. Each
process would wait indefinitely for the other to release the lock,
unless one of the user processes is terminated. SQL Server detects
deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is
repeatedly denied because a series of overlapping shared locks keeps
interfering. SQL Server detects the situation after four denials and

refuses further shared locks. A livelock also occurs when read
transactions monopolize a table or page, forcing a write transaction
to wait indefinitely.

77. nolock?
Locking Hints A range of table-level locking hints can be specified
using the SELECT, INSERT, UPDATE, and DELETE statements to direct
Microsoft® SQL Server™ 2000 to the type of locks to be used.
Table-level locking hints can be used when a finer control of the
types of locks acquired on an object is required. These locking hints
override the current transaction isolation level for the session.
Note The SQL Server query optimizer automatically makes the correct
determination. It is recommended that table-level locking hints be
used to change the default locking behavior only when necessary.
Disallowing a locking level can affect concurrency adversely.

Locking hint Description
HOLDLOCK Hold a shared lock until completion of the transaction
instead of releasing the lock as soon as the required table, row, or
data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK Do not issue shared locks and do not honor exclusive locks.
When this option is in effect, it is possible to read an uncommitted
transaction or a set of pages that are rolled back in the middle of a
read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK Use page locks where a single table lock would usually be taken.
READCOMMITTED Perform a scan with the same locking semantics as a
transaction running at the READ COMMITTED isolation level. By default,
SQL Server 2000 operates at this isolation level.
READPAST Skip locked rows. This option causes a transaction to skip
rows locked by other transactions that would ordinarily appear in the
result set, rather than block the transaction waiting for the other
transactions to release their locks on these rows. The READPAST lock
hint applies only to transactions operating at READ COMMITTED

isolation and will read only past row-level locks. Applies only to the
SELECT statement.
READUNCOMMITTED Equivalent to NOLOCK.
REPEATABLEREAD Perform a scan with the same locking semantics as a
transaction running at the REPEATABLE READ isolation level.
ROWLOCK Use row-level locks instead of the coarser-grained page- and
table-level locks.
SERIALIZABLE Perform a scan with the same locking semantics as a
transaction running at the SERIALIZABLE isolation level. Equivalent to
HOLDLOCK.
TABLOCK Use a table lock instead of the finer-grained row- or
page-level locks. SQL Server holds this lock until the end of the
statement. However, if you also specify HOLDLOCK, the lock is held
until the end of the transaction.
TABLOCKX Use an exclusive lock on a table. This lock prevents others
from reading or updating the table and is held until the end of the
statement or transaction.
UPDLOCK Use update locks instead of shared locks while reading a
table, and hold locks until the end of the statement or transaction.
UPDLOCK has the advantage of allowing you to read data (without
blocking other readers) and update it later with the assurance that
the data has not changed since you last read it.
XLOCK Use an exclusive lock that will be held until the end of the
transaction on all data processed by the statement. This lock can be
specified with either PAGLOCK or TABLOCK, in which case the exclusive
lock applies to the appropriate level of granularity.

78. For example, if the transaction isolation level is set to
SERIALIZABLE, and the table-level locking hint NOLOCK is used with the
SELECT statement, key-range locks typically used to maintain
serializable transactions are not taken.
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO

79. What is escalation of locks?
Lock escalation is the process of converting a lot of low level locks

(like row locks, page locks) into higher level locks (like table
locks). Every lock is a memory structure too many locks would mean,
more memory being occupied by locks. To prevent this from happening,
SQL Server escalates the many fine-grain locks to fewer coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it's dynamically managed by SQL Server.


For More SQL SERVER Frequently Asked Interview Questions