0

For testing, I am trying to simulate a condition in which a query from our web application to our SQL Server backend would timeout. The web application is configured so this happens if the query runs longer than 30 seconds. I felt the easiest way to do this would be to take and hold an exclusive lock on the the table that the web application wants to query. As I understand it, an exclusive lock should prevent any additional locks (even the shared locks taken by a SELECT statement).

I used the following methodology:

CREATE A LONG-HELD LOCK

Open a first query window in SSMS and run

BEGIN TRAN; 
    SELECT * FROM MyTable WITH (TABLOCKX); 
    WAITFOR DELAY '00:02:00';
ROLLBACK;

(see https://stackoverflow.com/a/25274225/2824445 )

CONFIRM THE LOCK

I can EXEC sp_lock and see results with ObjId matching MyTable, Type of TAB, Mode of X

TRY TO GET BLOCKED BY THE LOCK

Open a second query window in SSMS and run SELECT * FROM MyTable

I would expect this to sit and wait, not returning any results until after the lock is released by the first query. Instead, the second query returns with full results immediately.

STUFF I TRIED

  • In the second query window, if I SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, then the second query waits until the first completes as expected. However, the point is to simulate a timeout in our web application, and I do not have any easy way to alter the transaction isolation level of the web application's connections away from the default of READ COMMITTED.
  • In the first window, I tried modifying the table's values inside the transaction. In this case, when the second query returns immediately, the values it shows are the unmodified values.
Dale K
  • 25,246
  • 15
  • 42
  • 71
Robert N
  • 1,156
  • 2
  • 14
  • 32

1 Answers1

0

Figured it out. We had READ_COMMITTED_SNAPSHOT turned on, which is how the second query was able to return the previous, unmodified values in part 2 of "Stuff I tried". I was able to determine this with SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'MyDatabase'. Once it was turned off with ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT OFF, I began to see the expected behavior in which the second query would wait for the first to complete.

Robert N
  • 1,156
  • 2
  • 14
  • 32