Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB.
The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced.
The downside is that the resources required for data modifications are increased, because the data requires a version creating before modification and pointers need to be generated to ensure the correct versions are in use. Retrieving the correct version also incurs an additional overhead, because the version store is a linked list and therefore a number of rows may need to be traversed before finding the data required.
Because TempDB contains the row versions for all databases on an instance it needs to have enough space to accommodate these. Should TempDB run out of space then an attempt will be made to shrink the version store. This can result in error 3967, where a transaction that has yet to generate a version has been rolled back, or error 3966 where a transaction cannot read the row version. Write operations will continue, but error 3959 will be written to the error log because the write could not generate a row version.
So it really pays to ensure there is enough space for TempDB.
There are several ways to see the impact of using a version store – Performance Monitor and DMVs.
In this example I’m using three perfmon counters, all located under the instance of the appropriate SQL server –
Transactions\Version Generation rate (KB/s),
Transactions\Version cleanup rate (KB/s) and
Transactions\Version store size (KB).
On the instance used in this example the MSDB database has row-versioning enabled because it has snapshot isolation set. Therefore there is always some background activity in the version store.
Approximately every minute an automatic job executes which cleans out entries from the version store that are no longer required and is monitored under ‘Version cleanup rate (KB/s)’.
Similarly, the two DMVs ‘sys.dm_tran_top_version_generators’ and ‘sys.dm_tran_version_store’ show a small amount of activity within the version store.
Because select queries don’t generate row versions, executing a simple update against a test table is all that is required to demonstrate the potential impact upon the TempDB.
USE IsolationLevels; SET ROWCOUNT 100000; UPDATE dbo.test4 SET Test_Value = 1;
A spike can be seen in the version generation and an increase in the size of the version store.
In addition, there are a number of entries reported by the DMVs, filtered on the database in question.
The cleanup process (the green line in the following screenshot) then removes any entries from the version store that are too old to be of use.
And in this example, the DMVs return no results after the cleanup.
Now I’m going to loop the update and run a select query, which also loops. This will increase the number of versions created and acquired, showing the impact via Perfom and the DMVs.
WHILE 1 =1 BEGIN UPDATE dbo.test4 SET Test_Value = Test_Value + 1 WHERE Index_Column IN ('A', 'B') AND ID < 100000; WAITFOR DELAY '00:00:01' END
Will run until terminated, generating a large number of versions.
The select will run constantly too, showing the data it is reading.
WHILE 1 = 1 BEGIN SELECT * FROM dbo.Test4 WHERE Index_Column IN ('A', 'B') AND ID < 100000 ORDER BY id ASC; END
Perfmon shows a gradual increase in the version store size, although the rate of creation is low (due to the WAITFOR DELAY).
When the cleanup executes it does not remove all of the versions, because the SELECT statement is using some of those versions.
In addition, the DMVs return a larger number of rows and takes longer to do so. This extra time can be excessive where the version store has become large.
Optimistic locking can reduce the overhead of locking and blocking, but it incurs its own overheads which need to be considered.
Understanding Row Versioning-Based Isolation Levels
Row Versioning Resource Usage
Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)
Transaction Related Dynamic Management Views and Functions