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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy