Still not using RCSI?

If you still leave your databases at default settings, namely default isolation (READ COMMITTED – does it suck?), you are risking three things:

increased blockage and deadlocks
NOLOCK hints or READ UNCOMMITTED transaction isolation in the code, exposing users to dirty read issues
wrong results!

The third one – wrong results, is the most serious one. We all want to believe our databases are reliable source of correct data. But if you are still at default database settings (not using RCSI), this demo will show you the risk of getting wrong result from SELECT. Plain wrong, like 2+2=5. Imagine unreliable financial reports – can you afford NOT having RCSI on your database? I think RCSI should be default and enabled on every database, no exception.

Video:

IF OBJECT_ID('dbo.PersonAmounts') IS NOT NULL DROP TABLE dbo.PersonAmounts
GO
SELECT TOP 10000000 -- 10M
	Country = CONVERT(CHAR(1), SUBSTRING('ABCD', ABS(CHECKSUM(NEWID()))%4+1, 1)),
	StreetId = ABS(CHECKSUM(NEWID()))%100, -- 0-99
	OtherData = CONVERT(CHAR(100), ''),
	Amount = $0.1
INTO dbo.PersonAmounts
FROM sys.all_columns, sys.all_columns c
GO
CREATE CLUSTERED INDEX IX_PersonAmounts_CL ON dbo.PersonAmounts(StreetId, Country)
GO
-- SUM = 1M
SELECT SUM(Amount) FROM PersonAmounts

-- Can SQL make a mistake in a simple SUM() function?
DECLARE @sum BIGINT;
WHILE 1=1 BEGIN
	SELECT @sum = SUM(Amount) FROM PersonAmounts
	-- Print info:
	RAISERROR('Sum is %I64d', 0,1, @sum) WITH NOWAIT
	WAITFOR DELAY "00:00:01"
END
------------

-- In other thread: update street address (unrelated column, we do not change amount!)
SET NOCOUNT ON;
WHILE 1=1 BEGIN
	UPDATE TOP(10) t SET t.StreetId += 100
	FROM dbo.PersonAmounts t
END

----------

-- Update street address - other direction
SET NOCOUNT ON;
WHILE 1=1 BEGIN
	UPDATE t SET t.StreetId -= 100 FROM
	(	SELECT TOP(10) * FROM dbo.PersonAmounts t ORDER BY StreetId DESC
	) t
END

IF OBJECT_ID('dbo.PersonAmounts') IS NOT NULL DROP TABLE dbo.PersonAmounts

SELECT TOP 10000000 -- 10M

Country = CONVERT(CHAR(1), SUBSTRING('ABCD', ABS(CHECKSUM(NEWID()))%4+1, 1)),

StreetId = ABS(CHECKSUM(NEWID()))%100, -- 0-99

OtherData = CONVERT(CHAR(100), ''),

Amount = $0.1

INTO dbo.PersonAmounts

FROM sys.all_columns, sys.all_columns c

CREATE CLUSTERED INDEX IX_PersonAmounts_CL ON dbo.PersonAmounts(StreetId, Country)

-- SUM = 1M

SELECT SUM(Amount) FROM PersonAmounts

-- Can SQL make a mistake in a simple SUM() function?

DECLARE @sum BIGINT;

WHILE 1=1 BEGIN

SELECT @sum = SUM(Amount) FROM PersonAmounts

-- Print info:

RAISERROR('Sum is %I64d', 0,1, @sum) WITH NOWAIT

WAITFOR DELAY "00:00:01"

END

------------

-- In other thread: update street address (unrelated column, we do not change amount!)

SET NOCOUNT ON;

WHILE 1=1 BEGIN

UPDATE TOP(10) t SET t.StreetId += 100

FROM dbo.PersonAmounts t

END

----------

-- Update street address - other direction

SET NOCOUNT ON;

WHILE 1=1 BEGIN

UPDATE t SET t.StreetId -= 100 FROM

( SELECT TOP(10) * FROM dbo.PersonAmounts t ORDER BY StreetId DESC

) t

END

Still not using RCSI?

Leave a Reply Cancel reply