DRAG DROP
You have a table named Customers that has a clustered index defined on the ID column.
You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the
number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location in the answer area. (Answer
choices may be used once, more than once, or not at all.)

Answer:

Explanation:

Note:
* Optimized bulk load operations on heaps block queries that are running under the following
isolation levels:
SNAPSHOT
READ UNCOMMITTED
READ COMMITTED using row versioning
* READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by
other transactions. This prevents dirty reads. Data can be changed by other transactions
between individual statements within the current transaction, resulting in nonrepeatable
reads or phantom data. This option is the SQL Server default.
* SERIALIZABLE (more locks)
Specifies the following:
Statements cannot read data that has been modified but not yet committed by other
transactions.
No other transactions can modify data that has been read by the current transaction until the
current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of
keys read by any statements in the current transaction until the current transaction
completes.
* UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes.
UPDLOCK takes update locks for read operations only at the row-level or page-level. If
UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason,
an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK
isolation level hints are ignored. For example, if the isolation level of the session is set to

SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the
READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE
isolation level.
* XLOCK
Specifies that exclusive locks are to be taken and held until the transaction completes. If
specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the
appropriate level of granularity.
Table Hints (Transact-SQL)