You need to complete the script for the stored procedure

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.)
Select and Place:

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.)
Select and Place:

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 atable-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.



Leave a Reply 0

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