Transactions Locks «Prev  Next»

SQL Transactions Locks Quiz

Each question is worth one point. Select the best answer (or answers) for each question. This quiz reflects modern SQL Server guidance, including current error-handling patterns and practical concurrency behavior.
 
1. John runs a SELECT statement that normally finishes quickly, but today it waits for a long time before returning rows. What is the MOST LIKELY reason?
Please select the best answer.
  A. A shared lock was already present (shared locks always block reads).
  B. An exclusive lock was already present on the rows or pages needed by the query.
  C. An intent lock was already present (intent locks block reads by default).
  D. A schema lock was already present (schema locks always block all queries).

2. Winston wants a concurrency approach where reads typically avoid blocking writers by reading row versions (instead of waiting on locks). Which strategy is he describing?
Please select the best answer.
  A. Exclusive locking
  B. Shared locking
  C. Optimistic (row versioning / snapshot-based reads)
  D. Pessimistic (lock-based reads)

3. Louise checks @@LOCK_TIMEOUT and finds the value is 1000. What does this indicate?
Please select the best answer.
  A. The session will wait 10 hours for a deadlock to clear before failing.
  B. The session will wait 1000 minutes to acquire a lock before failing.
  C. The session will wait 1000 seconds to acquire a lock before failing.
  D. The session will wait 1000 milliseconds (1 second) to acquire a lock before failing.

4. You start a transaction in a stored procedure and include a COMMIT TRAN, but you do not implement error handling (no TRY...CATCH, no ROLLBACK path). If a runtime error occurs, what is the PRIMARY risk?
Please select the best answer.
  A. The transaction will always commit anyway.
  B. Statements that succeeded will always be committed, and only the failed statement is rolled back.
  C. The transaction may remain open and must be rolled back explicitly, which can cause blocking/locking problems.
  D. SQL Server produces an error because it cannot decide whether to commit or roll back.

5. Which TWO statements are generally true about distributed transactions in SQL Server environments?
Please select all the correct answers.
  A. They are treated exactly the same as local transactions, with no extra operational requirements.
  B. If you rely on MS DTC-based coordination, the Distributed Transaction Coordinator service must be available and configured.
  C. They can be initiated implicitly by some APIs/providers or explicitly by application/SQL logic, depending on the architecture.
  D. They are committed with COMMIT DISTRIBUTED TRAN.

6. Victoria runs: 
RAISERROR ('Fatal Error', 1, 1);
Then she checks @@ERROR and finds it is 0. How could this happen?
Please select the best answer.
  A. She must be checking the wrong variable.
  B. Severity 1 is informational; it does not behave like an error in many contexts and may not set @@ERROR the way a higher-severity error does.
  C. The @@ERROR variable will never be set unless WITH SETERROR is specified.
  D. State 1 is too low; state must be greater than 10 to set @@ERROR.