You manage a database on an instance of SQL Server 2008 at a large educational institution. You have ClassMaster and ClassDetail tables as shown:
(Click exhibit to view table design)
The User1 user starts a session and executes the following Transact-SQL:
BEGIN TRANSACTION
UPDATE ClassDetail WITH(TABLOCK)
SET ClassDetail.Details = ClassDetail.Details + ‘. This is a Freshman-level class.’
FROM ClassMaster INNER JOIN
ClassDetail ON ClassMaster.ClassID = ClassDetail.ClassID
WHERE ClassMaster.Level = ‘Freshman’;
Then, User2 starts a session and executes the following Transact-SQL:
BEGIN TRANSACTION
INSERT INTO ClassMaster(Description, Seats, Level)
VALUES (‘Calculus I’, 25, ‘Sophomore’),
(‘Accounting III’, 80, ‘Senior’),
(‘World History’, 30, ‘Freshman’);
DELETE FROM dbo.ClassDetail WHERE CDID = 2;
COMMIT TRANSACTION
What is the result?
Exhibit:
A. User1’s updates are applied first, and then User2’s DML operations are performed.
B. User2’s session hangs waiting for User1’s update to complete.
C. User1’s updates are not performed, but User2’s DML operations complete successfully.
D. User2’s session immediately returns an error.
Correct Answer: B
Explanation/Reference:
In this scenario, User1 begins a transaction and executes an UPDATE statement, but does not commit the changes. Then, User2 begins a transaction, but User1 currently has locks on the table that prevents User2’s actions from being performed. User2’s session hangs waiting for User1’s session to release the locks. SQL Server uses locking to control which transactions have access to which resources at any given time. This prevents one transaction from overwriting another transaction’s changes. SQL Server determines the locks that will be acquired based on the type of action being performed. However, you can override default locking behavior if necessary using table hints.
The option that states User1’s updates are applied first and then User2’s DML operations are performed is incorrect. User1 does not commit the changes.
Therefore, the changes are pending and User2’s operations are not performed.
The option that states User1’s updates are not performed but User2’s DML operations complete successfully is incorrect. In this scenario, User1’s updates have not been committed and will remain pending, blocking User2’s operations from executing.
The option that states User2’s session immediately returns an error is incorrect. User2’s session will wait to obtain the necessary locks to perform the required operations.
