November 1, 2010 at 8:14 pm
Hello,
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
@txnId varchar(20),
@responseStatus int,
@threadName varchar(1)
AS
BEGIN
SET NOCOUNT ON;
declare @requestId int
declare @requestStatus int
declare @Message nvarchar(1500)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
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
COMMIT TRANSACTION
END
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?
November 1, 2010 at 9:38 pm
Duplicate post. Please do not respond to this thread; please respond to this thread for all responses.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply