I am having a problem with setting isolation level in the Stored Procedure. The following stored procedure opens a transaction with REPEATABLE READ isolation level and is being called from 3 parallel threads. Threads A and B work with the same record. Thread C works on a different record.
CREATE PROCEDURE RepeatableReadTest
SET NOCOUNT ON;
declare @requestId int
declare @requestStatus int
declare @Message nvarchar(1500)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
select @requestId = requestId from TRANSMIT_REQUESTS where transactionid = @txnId order by requestInitiated desc
set @Message = 'Got the requestID='+ cast( @requestId as varchar(20))+ ', thread='+@threadName
exec asl_WriteLog @Message
WAITFOR DELAY '00:00:01'
UPDATE TRANSMIT_REQUESTS set requestStatus = @responseStatus where requestId=@requestId
set @Message = 'Updated requestId='+ cast( @requestId as varchar(20))+ ', thread='+@threadName
exec WriteLog @Message
Here are the log records sorted by time stamp:
Got the requestID=5679, thread=B
Got the requestID=5679, thread=A
Got the requestID=5668, thread=C
Updated requestId=5679, thread=B
Updated requestId=5679, thread=A
Updated requestId=5668, thread=C
My expectation was that thread A would be blocked until thread B completes the transaction, but thread C would be able to complete its transaction without being blocked. Instead it looks like all threads are completing transaction without being blocked.
What am I missing?