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, Second User 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
First User begins a transaction and executes an UPDATE statement, but does not commit the changes. Then, Second User begins a transaction, but First User currently has locks on the table that prevents First User's actions from being performed. Second User's session hangs waiting for First User'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 First User's updates are applied first and then Second User's DML operations are performed is incorrect. First User does not commit the changes. Therefore, the changes are pending and Second User's operations are not performed. The option that states First User's updates are not performed but Second User's DML operations complete successfully is incorrect. In this scenario, First User's updates have not been committed and will remain pending, blocking Second User's operations from executing. The option that states Second User's session immediately returns an error is incorrect. Second User's session will wait to obtain the necessary locks to perform the required operations.
No comments:
Post a Comment