Problem with REPEATABLE READ

Question

Problem with REPEATABLE READ

aptekman

SSC Veteran

Points: 223
More actions
November 1, 2010 at 8:22 pm

#231073

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

With the REPEATABLE READ transaction isolation level, I am seeing that the reader is blocking the writer but I am not sure why you are not seeing this behavior. Try increasing the waitfor interval from one second to an hour and re-try your test.

Test case: After creating the table and populating, open two SSMS query windows and run the corresponding SQL. "Connection 1", the repeatable read, does block "Connection 2", which has an update.

After "Connection 1" is committed, then "Connection 2"'s update is no longer blocked and does proceed.

The commits and rollbacks are commented out so that they can be run separately.

CREATE TABLE dbo.Products

(ManufactorId int NOT NULL

,ManufactorPartId int NOT NULL

,PartName nvarchar(256) NOT NULL

, CONSTRAINT Products_PK PRIMARY KEY CLUSTERED

(ManufactorId ASC , ManufactorPartId ASC)

, CONSTRAINT Products_UQ_1 UNIQUE NONCLUSTERED

( PartName ASC)

)

;

-- create 16,384 rows (128 * 128)

insert into dbo.Products

(ManufactorId, ManufactorPartId, PartName)

selectManufactor.N

,Parts.N

,'M' + CAST(Manufactor.N as varchar(8) ) + 'P' + CAST(Parts.N as varchar(8) )

fromdbo.Tally as Manufactor

cross join

dbo.Tally as Parts

WHEREManufactor.Nbetween 1 and 128

andParts.Nbetween 1 and 128

;

Connection 1: execute the "commit" command later

declare @PartName nvarchar(256) ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

select@PartName = PartName

fromdbo.Products

whereManufactorId = 10

;

select @partname

--commit

Connection 2: execute the "rollback" command later

begin tran

update dbo.Products

setPartName = 'NewPart'

whereManufactorId = 10

andManufactorPartId = 100

-- rollback

SQL = Scarcely Qualifies as a Language

aptekman SSC Veteran Points: 223 More actions · Answer 2

Thank you for your response.

I see the problem in ability to use a dirty read. What I mean is this: The stored procedure makes a read and than based on the results of that read makes a decision about how to update the record. So, in my case the thread B reads the record, than thread A reads the same record. Even if thread A is blocked when trying to update the record it will be using the stale data (becomes stale after thread B makes an update) in its logic. My understanding of the REPEATABLE READ was that the second thread would be blocked on read until the first transaction completes transaction.

Thanks

aptekman SSC Veteran Points: 223 More actions · Answer 3

Now it is working correctly, but not the way you or I were expecting. It does not block the thread. It simply throws a deadlock exception on one of the threads (A or B, never C) and rolls back a transaction. So I would need to retry on exception. Not sure what was happening before.

Thanks

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

If you are attempting to simulate a queue with a table, where multiple connections can read from the queue but each queue row can only be read by one of the connections, then try adding to the "from" some query hints.

XLOCK - take an exclusive lock on the row

ROWLOCK - insure that locks are at the row level of granularity

READPAST - skip any rows that are already locked.

Just be aware that if the SELECT affects multiple rows or multiple tablees, all will be selected and locked, so a "TOP 1" may be needed.

An alternative solution is to use Service Broker, which has these features.

SQL = Scarcely Qualifies as a Language

aptekman SSC Veteran Points: 223 More actions · Answer 5

Thanks a lot.

XLOCK is exactly what was needed in this case. It does block the thread that tries to read the row selected by other thread with XLOCK. So, in the above example my thread A is getting blocked while thread B is completing transaction. Thread C on the other hand is executing in parallel without being blocked. No deadlocks, no retries.