Morning, on this first day of the third Carolean era, how's it for all you in "the colonies"? May QEII be successfully migrated to that great blob storage in the Cloud, and may the new installation go smoothly without too much downtime.
for that post specifically, snapshot isolation is different from read committed snapshot isolation. rcsi is not available for modifications, so you don't have that for concurrent modifications. you can still hit it with concurrency reads and modifications.
there are some modification patterns like a read and then a write that would hit it, but local locking and isolation hints can get you around that.
yeah, it's not the only post out there https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/#cheat-sheet-for-snapshot-and-rcsi
yeah, that's the other thing (i'm writing a post about it), read committed is a garbage isolation level. but most people i talk to have nolock hints on every query (including modifications) so ???
> If you try this same experiment with serializable isolation, one transaction will wait for the other to complete and, depending on the order, both marbles will end up either white or black.
At serializable isolation the result has to be consistent with one transaction running to completion as though it had the whole machine to itself and then the other running.
Is the RCSI race condition risk any different than if you were to read and save results into a temp table and then do an update? Between the reading and updating the data could have been changed.
create table a ( x int );
create table b ( x int );
-- Session 1 at serializable isolation
insert into a select count(*) from b
-- Session 2 at serializable isolation, concurrently
insert into b select count(*) from a
I like that example of write skew. Both tables can end up containing zero under snapshot isolation (SI).
I don't worry so much about the tempdb impact of RCSI these days, but triggers used to enforce any kind of RI do need careful review, and probably READCOMMITTEDLOCK hints.
And yes, code written to assume blocking occurs, when it no longer will.
Well, once I've found a transaction that has been opened for 14 hours and the version store grew to 300GB (without SI/RCSI) probably from triggers only
btw: I don't expect change from an Azure Feedback item but on the off-chance https://feedback.azure.com/d365community/idea/68a73199-0a30-ed11-a81b-000d3a04ded5
@ypercubeįµį“¹ SQL Server uses strict two-phase locking with key-range (predicate) locks to enforce serializable. If you trust that arrangement, the answer is yes.
@PaulWhite so you are saying if there is 1 row with White and 1 row with Black and in between a million other rows with Red, it might take non-overlapping key range locks and not work as expected?
@ypercubeįµį“¹ No, I'm not saying that at all. I'm saying if you trust the long-established scheme of S2PL and range locks to deliver serializability, so does SQL Server.
In the example, reading white marbles locks the entire range white marbles would fall in, so no other concurrent process can insert a white marble until the first transaction has ended.
