This means that any query where we are filtering on LastName should use this index to retrieve the data. The Person.Person table has a non-clustered index IX_Person_LastName_FirstName_MiddleName with the LastName column as the first column in the index key. The following example using the Person.Person table in the AdventureWorks2012 database will demonstrate how these key-range locks are used. The key-range locks are used to prevent phantom data from being added and they are placed on the keys of the index that is used to retrieve the data.
If the first transaction performs the same read it will now return the new data resulting in phantom data appearing in the second read within the same transaction. using a query with a WHERE clause, and another transaction adds data that will satisfy the conditions of the WHERE clause. A phantom read is where one transaction performs a read on a range of data, e.g.
Key-range locks are only used in the SERIALIZABLE isolation level and their purpose is to stop phantom reads which are possible in the other three pessimistic concurrency isolation levels, namely read uncommitted, read committed and repeatable read.
#Read committed isolation level sql server 2012 how to
In this post I’m going to go into more detail about the key-range locks used in the SERIALIZABLE isolation level and also demonstrate to use the sys.dm_tran_locks DMV to view existing locks and also how to work out what resources the locks are placed on. In a previous post I talked about Locking, Blocking and Isolation Levels.
0 kommentar(er)
