Your Prod database resides on a SQL Server 2008 instance. You have a PurchaseOrderHeader table defined as follows in the Prod database:
You open a session and execute the following Transact-SQL:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION SELECT * FROM PurchaseOrderHeader;
You open another session.
Which statement will successfully execute in the second session with no blocking?
A. DELETE FROM PurchaseOrderHeader
WHERE PurchaseOrderID = 1;
B. UPDATE PurchaseOrderHeader
SET ShipMethodID = 2
WHERE PurchaseOrderID = 1;
C. ALTER TABLE PurchaseOrderHeader
ADD ProjectID int;
D. INSERT INTO PurchaseOrderHeader(Status, VendorID, ShipMethodID,OrderDate, ShipDate, SubTotal, TaxAmt,
Freight, ModifiedDate)VALUES(0,0,0,’02-13-2009′,NULL, 10, 10, 10, GETDATE());
Correct Answer: D
Explanation/Reference:
By default, SQL Server automatically handles locking and attempts to use the least restrictive locking applicable for the operation being performed. When a database operation is performed, SQL Server determines the resource that needs to be locked, and to what extent it should be locked. Based on what action is being performed, SQL Server can lock the row, key, page, extent, table, or the entire database. Each lock has a lock mode identifying how the lock will actually behave. These lock modes include Shared(S), Exclusive (X), Update (U), Intent, and Schema.
In this scenario, the transaction you started in the first session has a transaction isolation level of REPEATABLE READ. This causes SQL Server to acquire shared locks and hold them until the transaction ends. The INSERT statement you issued in the second session requires a shared lock. Therefore, it executes with no blocking.
All of the other options are incorrect because all of these statements will attempt to acquire an exclusive lock and will hang.