Switch to RCSI the focus is often on the server, can tempdb handle the rowversion load. But is the application also suitable for RSCI? Advise: always keep the update in one phase.
The code is in the end.
So here are three demo’s.
a) Read Committed and where is the lock
Execute the first part, not the commit.
b) Read Committed Snapshot and where is the lock (fine, but is our data still correct?)
Now the update gets blocked, the data was read from the RowVersion.
c) Read Committed Snapshot and where is the lock (WITH(READCOMMITTEDLOCK) data correct)
/* Thank you Erik Darling Data
https://www.youtube.com/watch?v=tR_4yzccsvQ
Put on RCSI
a) can tempdb handle it? Yes nice .. less (dead)locking?
b) can the application handle it? Testing!, Testing!!
*/
use StackOverflow2013
go
-- Off
alter database StackOverflow2013
set read_committed_snapshot OFF
with rollback immediate;
/*
READ_COMMITTED_SNAPSHOT is *not* isolation level, it is a database's option permitting
to change the behavior of ReadCommitted isolation level database-wide.
Snapshot is RowVersioning.
*/
SELECT CASE
WHEN transaction_isolation_level = 1
THEN 'READ UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED'
WHEN transaction_isolation_level = 3
THEN 'REPEATABLE READ'
WHEN transaction_isolation_level = 4
THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5
THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
/*
TRANSACTION_ISOLATION_LEVEL
-> READ COMMITTED
*/
/* On */
alter database StackOverflow2013
set read_committed_snapshot ON
with rollback immediate;
SELECT CASE
WHEN transaction_isolation_level = 1
THEN 'READ UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED'
WHEN transaction_isolation_level = 3
THEN 'REPEATABLE READ'
WHEN transaction_isolation_level = 4
THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5
THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
/*
TRANSACTION_ISOLATION_LEVEL
-> READ COMMITTED SNAPSHOT
*/
/* OUT */
drop table if exists dbo.DinnerPlans;
create table dbo.DinnerPlans (
id bigint not null primary key clustered identity
, name nvarchar(40) NULL
, seat_number tinyint null
, is_free bit not null default 'false'
);
/* IN */
insert into dbo.DinnerPlans with (tablockx)
(name, seat_number, is_free)
select top(6)
name = case m.message_id
when 21 then null
when 101 then N'Lucky fan #1'
when 102 then N'Lucky fan #2'
when 103 then N'Lucky fan #3'
when 104 then N'Lucky fan #4'
when 105 then N'Lucky fan #5'
end
, seat_number = row_number() over (order by 1/0)
, case m.severity when 20 then 1 else 0 end
from sys.messages as m
where m.language_id = 1033
order by m.message_id;
select *
from dbo.DinnerPlans;
/* #1 is empty seat
id name seat_number is_free
1 NULL 1 1
2 Lucky fan #1 2 0
3 Lucky fan #2 3 0
4 Lucky fan #3 4 0
5 Lucky fan #4 5 0
6 Lucky fan #5 6 0
*/
/* Need this in a new window te reset from snapshot */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION
DECLARE @name nvarchar(40) = N'ronald';
with FirstFreeTable as (
select top(1) dp.id
from dbo.DinnerPlans as dp
where dp.is_free = 1
)
update dp
set dp.[name] = @name
, dp.is_free = 0
output inserted.*
from dbo.DinnerPlans as dp
JOIN FirstFreeTable as fft
on fft.id = dp.id;
commit transaction;
/* Need this in a new OTHER window te reset from snapshot */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION
DECLARE @name nvarchar(40) = REVERSE(N'ronald');
with FirstFreeTable as (
select top(1) dp.id
from dbo.DinnerPlans as dp
where dp.is_free = 1
)
update dp
set dp.[name] = @name
, dp.is_free = 0
output inserted.*
from dbo.DinnerPlans as dp
JOIN FirstFreeTable as fft
on fft.id = dp.id;
commit transaction;