Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with REPEATABLE READ


Problem with REPEATABLE READ

Author
Message
aptekman
aptekman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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?
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6255 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search