Archive | August, 2015

Read Committed Snapshot – Another Optimistic Flavour

26 Aug

In a previous article (here) I described how the isolation level read committed works, with some examples. There is also an optimistic version of this – read committed snapshot, which uses row versions.
The behaviour of read committed and read committed snapshot is similar and repeating the examples used previously will show the similarities and the differences.

First of all, the database needs to be configured for snapshot isolation.

ALTER DATABASE IsolationLevels
SET READ_COMMITTED_SNAPSHOT ON;

As with the article on read committed, there are 5,000,000 rows with the ‘ID’ column starting from 10 and a value of 1 in ‘Test_Value’ for each row.

Read_Committed_snapshot_01

This code is executed again and as soon as it starts the row with ‘ID’ of 10 is moved to the end of the index, in another tab.

UPDATE dbo.Test2
SET id = id + 6000000
WHERE id = 10;

This time the result is slightly different. The result shows that move of the row with ‘ID’ 10 to 6000010 has not affected the first SELECT. This is because it used a snapshot of the data, taken when the statement started (not when the transaction started), so the move had no impact upon this version of the data.

Read_Committed_snapshot_02

Similarly, moving the last row in the index to the start of the index does not have the impact it did with read committed isolation, for the same reason. Having reset the data and executed the appropriate UPDATE does not have the same affect, because it processed a snapshot of the data.

UPDATE dbo.Test2
SET id = 9
WHERE id = 5000009;

Read_Committed_snapshot_03

For the same reason, dirty reads cannot happen with read committed snapshot, because it is using as snapshot of the data taken when the statement started.

A smaller demonstration
In the read committed isolation level article, an UPDATE was executed to cause a lock, thereby blocking a subsequent SELECT.
Trying the same demonstration here results in different behaviour.

1000 rows, with an ‘ID’ column and ‘Test_Value’ column matching in values.

Read_Committed_snapshot_07

Executing an UPDATE in a separate window without a COMMIT/ROLLBACK will lock the selected row.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET Test_Value = 1000
WHERE id = 50;

And rerunning the query with the two SELECT statements would wait for the release of the lock from the UPDATE, if this were read committed isolation level.

Read_Committed_snapshot_06

However, with read committed snapshot the row with the ‘ID’ of 50 is unchanged, and the SELECT was not blocked, because a snapshot was used by the transaction.

A different demonstration, to show that each SELECT within a transaction uses a different snapshot.
Two identical SELECT queries, separated by a 10 second delay.

Read_Committed_snapshot_04

Before the WAITFOR command completes an UPDATE is executed that will move a row from the start of the index to the end.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET ID = 1001
WHERE ID = 10;

COMMIT TRANSACTION

Looking at completed initial transaction, the moved row is shown in the second SELECT but not in the first, so the second SELECT read a new snapshot, showing the moved data.

Read_Committed_snapshot_05

So, as with read committed isolation, multiple queries within the same transaction may return different results, even when the criteria are identical.

Advertisements

Snapshot Isolation – Let’s Get Optimistic

18 Aug

The previous articles on isolation levels were using pessimistic locking. Two further isolation levels exist that use optimistic locking and are row-versioning based.

Snapshot Isolation
With snapshot isolation level no locks are placed upon the data when it is read and transactions that write data don’t block snapshot isolation transactions from reading data. Instead, when the first statement within a transaction is started, a version of the data it requires is stored in tempdb along with a sequence number. It uses this sequence number to control access to the consistent version of the data for that transaction.
Snapshot isolation, like the serializable isolation level, does not allow dirty reads, lost updates, phantom rows or nonrepeatable reads. However, snapshot isolation does not use locks to achieve this. Instead it has a consistent version of the data, taken at the start of the transaction and if an update to this data clashes with an update performed elsewhere then the transaction is rolled back.
To set snapshot isolation requires setting a database level property.

ALTER DATABASE IsolationLevels SET ALLOW_SNAPSHOT_ISOLATION ON;

This enables the row versioning mechanism within the database. Versioned rows will be stored even if the isolation level specified for the query is pessimistic. However, you still need to specify the snapshot isolation level for the query in order to use versioned data.

Example 1 – updating a row
Using the same table used in the serializable example, transaction 1 will use read committed isolation to read and update the value with one row.
At the same time a snapshot isolation level transaction will select the same row and attempt an update.
In transaction 1

Snapshot_02

Note the absence of a COMMIT or ROLLBACK at this point, locking this row. It will show the updated value of 2, for the column ‘Test_Value’.
In transaction 2

Snapshot_03

Using snapshot isolation, this shows the value of 1 for ‘Test_Value’, because at the time this transaction started the update in transaction 1 had not been committed.
If we now COMMIT transaction one and then re-run the SELECT query within transaction 2 the value is unchanged.

Snapshot_04

This transaction is still reading the date from the version store, to keep the view of this transaction consistent.
Because the row with ‘ID’ of 9 has now been updated elsewhere, an attempt by transaction 2 to update this row will result in an error message and a rollback of the transaction.

Snapshot_05

The entire error message is
Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Test4’ directly or indirectly in database ‘IsolationLevels’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Of course, re-running transaction 2 now will show the updated value and an update will be allowed because nothing else will be in the process of updating it.

Snapshot_06

Example 2 – inserting a row
Transaction 1 selects the first few rows, transaction 2 inserts a row within the same range that transaction 1 is using and transaction 3 then selects the first few rows.
Transaction one selects all rows where the ‘ID’ column has a value less than 10, and retrieves 6 rows.

Snapshot_08

Transaction 2 inserts a new row, with an ‘ID’ value of 1, which should make it within the range of the transaction 1 query.

Snapshot_09

Re-run the SELECT query within transaction 1 and the results are unchanged – it is using the data from the version store.

Snapshot_10

Repeat this query in transaction 3 and the extra row is also selected, as it is using the latest version of the data.

Snapshot_11

Unlike the serializable isolation level it has been possible to insert a row within the range that transaction 1 had selected, but transaction 1 will never show this new row. It will keep a consistent version of the data until the transaction has completed or rolled back.

Example 3 – deleting a row
As expected, the behaviour is the same as with the insertion – transaction 1 will read a range of data.

Snapshot_12

Transaction 2 will delete one of the rows that transaction 1 had selected.

Snapshot_13

Repeating the select within transaction 1 will give the same results, even though transaction 2 has since removed a row.

Snapshot_14

Executing transaction 3 after the deletion will show the latest data.

Snapshot_15

The Overhead
Setting the database to ALLOW_SNAPSHOT_ISOLATION starts the generation of row versions, even for transactions that do not use snapshot isolation. This command will not complete until all currently executing transactions have completed, so may take a while to return (https://msdn.microsoft.com/en-us/library/bb522682.aspx) .
Versioning adds 14 bytes to every row of the database as they are accessed, so it is essential to take this additional storage into account.
The versioned data is stored in tempdb, so this can grow by a large amount for databases that are heavily used. In addition, this store is a linked list of related versions, so transactions may have to traverse a long linked list if there are transactions that have been open for a long time. This of course adds to the processing time of a transaction. There is a clean-up process that runs frequently, to clear this list but it may not be able to remove all entries, depending upon how heavily the database is being used.
A range of DMVs and functions are available to monitor the behaviour of versioning within a database – https://msdn.microsoft.com/en-us/library/ms178621.aspx but if the version store is large these can also take a while to process.

Conclusion
Snapshot isolation is not suitable for databases where data updates are frequent, as it will have the potential to raise a large number of conflicts. It is more suited to databases that are mainly read, with data inserted or deleted but few updates.

Serializable – Not One For Sharing

12 Aug

In the previous article it was shown that phantom reads and skipped rows were possible with the repeatable read isolation level.
Serializable is the highest isolation level and will not allow phantom reads or skipped rows.

Continue reading

Repeatable Read – still no guarantees

7 Aug

In my previous article I showed how read committed would allow rows to be updated that it had already processed in a query, because it releases locks as soon as it has processed that row or page (depending upon the type of lock taken). A second query using the same criteria would then show different results from the first.
Repeatable read isolation level doesn’t allow this type of action to occur, because it retains the locks until the transaction has completed.
Continue reading