Isolation Levels 3

dbtut

6 years ago

In this article we will examine the differences between RCSI and Snapshot Isolation and the inconsistencies that may arise when using these Isolation Levels.

At two Isolation Levels, if there is not enough space in Tempdb, the updates will not fail but can not be versioned.

Therefore, select queries may fail.

Updates at the Snapshot Isolation Level may conflict. This does not happen on the RCSI.

The RCSI Isolation Level, according to the Snapshot Isolation Level, consumes less space in tempdb.

While RCSI can work with Distrubuted Transaction, Snapshot can not work.

RCSI can not be enabled in tempdb, msdb, or master databases.

To create a global temp table at the SNAPSHOT Isolation Level, SNAPSHOT must be allowed in tempdb.

At the SNAPSHOT Isolation Level, when an object is modified with a DDL Statement, the DDL Statement will fail if another transaction accesses the same object.

You can see the example below. In the first session, we run the following script.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO
WAITFOR DELAY '00:00:10.000';
COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

ALTER INDEX AK_Employee_LoginID

ON HumanResources.Employee REBUILD;

WAITFOR DELAY '00:00:10.000';

COMMIT TRANSACTION

In the second session, we run the following script.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
SELECT TOP 1000 [BusinessEntityID]
FROM [AdventureWorks2012].[HumanResources].[Employee]
where LoginID='adventure-works\rob0'
COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

SELECT TOP 1000 [BusinessEntityID]

FROM [AdventureWorks2012].[HumanResources].[Employee]

where LoginID='adventure-works\rob0'

COMMIT TRANSACTION

The first session will result in the following error.

Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction.

Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

Msg 3902, Level 16, State 1, Line 2

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Below you can see which DDL operations are restricted.

CREATE INDEX
CREATE XML INDEX
ALTER INDEX
ALTER TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
ALTER PARTITION SCHEME
DROP INDEX
Common language runtime (CLR) DDL

There is no such restriction on the RCSI.

We have already told you that there should be enough space in tempdb when using RCSI or SNAPSHOT.

To be able to version in tempdb, you can see which database uses which space as below.

select DB_NAME(database_id) AS DBName,SUM(aggregated_record_length_in_bytes)/1024 [SpaceUsed_KB] 
from sys.dm_tran_top_version_generators
GROUP by database_id
ORDER by SpaceUsed_KB DESC

select DB_NAME(database_id) AS DBName,SUM(aggregated_record_length_in_bytes)/1024 [SpaceUsed_KB]

from sys.dm_tran_top_version_generators

GROUP by database_id

ORDER by SpaceUsed_KB DESC

Let’s look at a few examples that could lead to inconsistency in data when using SNAPSHOT and RCSI.

When we repeat our examples on READ COMMITTED, RCSI and SNAPSHOT, we will see their differences.

1) First Example:

To use in our first two examples, we run the following script on any test database.

create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')

create table marbles (id int primary key, color char(5))

insert marbles values(1, 'Black')

insert marbles values(2, 'White')

Let’s try it on READ COMMITTED first. Open a new session and run the following query.

We started the transaction and performed the update, but we have not committed yet.

begin tran
update marbles set color = 'White' where color = 'Black'

1 2	begin tran update marbles set color = 'White' where color = 'Black'

In the second session, we run the following query.

If we run the query, we will see that the second query is waiting because the first query has not been committed.

begin tran
update marbles set color = 'Black' where color = 'White'
commit tran

begin tran

update marbles set color = 'Black' where color = 'White'

commit tran

Then I go back to the first session and commit as follows.

commit tran

1	commit tran

If we make a select to the table after commit, the result will be two Black.

And when we commit the first session as above, the wait in the second session will end.

In the first query we wanted to update the black ones in white, but we have not committed yet.

In the second query, we tried to update the white ones in black.

However, we had to wait because the first query did not commit.

When the first query was completed, the two records in the table became white and the second session saw these two records as white and both were updated to black.

If we repeat our example on RCSI, we will get the same results.

Because the RCSI pessimistic writes, the second session will wait as it is in the READ COMMITTED example.

Repeat our example on SNAPSHOT and see what happens.

Let’s allow SNAPSHOT on a database basis. And run the following script in the first session.

set transaction isolation level snapshot
begin tran
update marbles set color = 'White' where color = 'Black'

set transaction isolation level snapshot

begin tran

update marbles set color = 'White' where color = 'Black'

In the second session, we run the following script.

set transaction isolation level snapshot
begin tran
update marbles set color = 'Black' where color = 'White'
commit tran

set transaction isolation level snapshot

begin tran

update marbles set color = 'Black' where color = 'White'

commit tran

Unlike the other two Isolation Levels, the second session will not wait here because the Snapshot Isolation Level is optimistic.

And eventually it will give 1 rows affected message.

If we return to the first session and commit, we will see the final state as White Black.

It was allowed to update different records in the snapshot, but in a scenario like the one above, if the application was not written taking snapshot behavior into consideration, it could cause problems.

If you think of this scenario according to your applications, you can see that optimistic write has some drawbacks.

You should not confuse this scenario with updating the same record.

If you convert the above example to update the same record, you will see that the second session is waiting and the second session has an update conflict when the first session is committed.

2)Second Example:

We will use the same table to address a problem related to RCSI.

First, we will run our example on READ COMMITTED.

Let’s run the following query in the first session.

DECLARE @id INT;
BEGIN TRAN
SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Black';
UPDATE  dbo.marbles
SET     color = 'White'
WHERE   id = @id;

DECLARE @id INT;

BEGIN TRAN

SELECT @id = MIN(id)

FROM dbo.marbles

WHERE color = 'Black';

UPDATE dbo.marbles

SET color = 'White'

WHERE id = @id;

With the above query, we assign the id value of the smallest id to the local @ id variable from the black ones.

Then we convert the records whose id value is the same as our @id variable to White.

Actually what we do is turn the black ones into white.

But we do not do this directly with the update. first we pull it with select and we assign it to our local variable.

Without committing the first session, we run the following query in the second session.

DECLARE @id INT;
BEGIN TRAN
SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Black';
UPDATE  dbo.marbles
SET     color = 'Yellow'
WHERE   id = @id;
COMMIT TRAN
GO

DECLARE @id INT;

BEGIN TRAN

SELECT @id = MIN(id)

FROM dbo.marbles

WHERE color = 'Black';

UPDATE dbo.marbles

SET color = 'Yellow'

WHERE id = @id;

COMMIT TRAN

In the second query, we assign the id value of the smallest id from the black ones to the @id variable.

We then update the id value to the color of the line that is equal to our variable @id, in yellow, but we will see that the query is waiting.

When we go back to the first session and commit, the second session will return 0 rows affected.

When we look at the last of the data, we will see it as White and White.

The second session was not able to read the updated but not committed data until the first session was over.

When the first session was committed, the second session could read the committed data.

But could not do any updates because it could not find a line with black color.

When we execute the same example using RCSI, we see that the second session is waiting for the first session to be committed again.

However, unlike READ COMMITTED, since the select query on the RCSI can access the last commit of the uncommitted data via tempdb, the first select query in the second session could pass the smallest id of the black ones to the @id variable.

But the update continued to wait. When we commit the first session, the second session completes and returns 1 rows affected.

Even though the first session returned Black to white, the second session took the id value and performed the update over the id.

When we look at the last of the data we will see it as Yellow White.

When we execute the same example using SNAPSHOT, the second session will wait for the first session again and second session will give the update conflict error when the first session is committed.

As a result, the final version will be White White.

3)Finally,

I will point out an example of a different problem that may occur in the RCSI and SNAPSHOT Isolation Levels.

We will perform our example again on READ COMMITTED first. Run the following query in any database.

CREATE TABLE Tickets(TicketId INT NOT NULL,
  AssignedTo INT NOT NULL,
  Priority VARCHAR(10),
  CONSTRAINT PK_Tickets PRIMARY KEY(TicketId),
)
GO
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(1, 1, 'High')
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(2, 8, 'High')
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(3, 10, 'High')
go

CREATE TABLE Tickets(TicketId INT NOT NULL,

AssignedTo INT NOT NULL,

Priority VARCHAR(10),

CONSTRAINT PK_Tickets PRIMARY KEY(TicketId),

)

INSERT INTO Tickets(TicketId, AssignedTo, Priority)

VALUES(1, 1, 'High')

INSERT INTO Tickets(TicketId, AssignedTo, Priority)

VALUES(2, 8, 'High')

INSERT INTO Tickets(TicketId, AssignedTo, Priority)

VALUES(3, 10, 'High')

Open a new query page and run the following script.

In this case, if there is no row with the AssignedTo value of 6 and the Priority value of High in the Ticket table, we set the AssignedTo value to 6 for the line with TicketId of 1.

BEGIN TRANSACTION
UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1
AND NOT EXISTS(SELECT 1 FROM Tickets 
WHERE AssignedTo = 6 AND Priority='High')

BEGIN TRANSACTION

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1

AND NOT EXISTS(SELECT 1 FROM Tickets

WHERE AssignedTo = 6 AND Priority='High')

Without committing, go to the second session and run the script below.

In this case, our first condition is that there is not a row with the AssignedTo value 6 and the Priority value High in the Ticket table again.

If these conditions are met, this time we set the AssignedTo value to 6 for the line with TicketId of 2.

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

1 2	UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2 AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

In the second session we will see that we are waiting for the above query.

When we go back to the first session and commit, the wait on the second session will end and 0 rows affected.

Because, when the first query is completed, the second query does not provide the condition that the AssignedTo value 6 in the Ticket table and the Priority value is High.

Now, the line with TicketId of 1 appears to have an AssignedTo value of 6.

When we execute the same example using RCSI or SNAPSHOT, we will see that the second session will return 1 rows affected even if there is no commit in the first session.

In READ COMMITTED, because the select query can not read the uncommitted data, In the second session, the select query that made the condition that the AssignedTo value 6 and the row with the Priority value High not exist in the Ticket table could not perform the read operation.

But on the RCSI or SNAPSHOT this select query could read the most recently committed data, so the select operation was performed and the update could be done after that.

When we return to the first session and perform the commit operation, we can see that it has been completed on it and that our table has two records with AssignedTo value 6 and Prioritry value High.

In the above example, we actually created two records with RCSI or SNAPSHOT, which we intended to have a single row with AssignedTo value 6 and Priority value High in our table.

Of course, it is possible to make the same operations using RCSI or SNAPSHOT with changes to be made in the query.

But what I want to show here is;

If we use RCSI or SNAPSHOT, there may be inconsistent data in our application.

We need to warn application developers to these issues and make the necessary changes if the Isolation Level change is decided.

Below you can see which concurrency problems can occur at which isolation level, and the concurrency control approaches of isolation levels in a table.