Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What resource does a key lock actually lock?

I know a key lock locks a key in an index. However, what does "key" actually mean?

For example, if I have a non-clustered index on a surname column and attempt an update where surname = "Jones", will I have effectively locked every row in the table where the surname is "Jones"? Or will the index be locked at a higher level, preventing access of rows with surnames other than "Jones"?

The reason I ask is this note in Books Online about Lock Granularity and Hierarchies:

KEY: A row lock within an index used to protect key ranges in serializable transactions.

This suggests a range of keys will be locked, not just one.

like image 348
Simon Tewsi Avatar asked May 16 '11 03:05

Simon Tewsi


People also ask

What is a key lock SQL?

Key Locks. SQL Server 7.0 supports two kinds of key locks, depending on the isolation level of the current transaction. If the isolation level is Read Committed or Repeatable Read, SQL Server attempts to lock the index keys it accesses while processing the query.

How does a database lock work?

A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time.

How does locking work in SQL Server?

Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.

What is the data locking in database?

A database lock is a mechanism created to lock a database object (like row, range of rows, or tables) to preserve data integrity. There are several types of database locks available, like the read/write and shared/exclusive locks.


1 Answers

A keylock affects all rows that match the given predicate (kind of) - in your example all rows with surname = 'Jones' will be affected.

The word "range" is used because depending on the predicate a range of rows may be affected, for example if the predicate was age > 18 then all rows where the age is greater than 18 would be affected.


It is also important to understand that a key lock does not simply individually lock each matching row in the index - your example keylock not only affects all existing rows in the index with surname "Jones", it also affects any attempt to modify an existing row or insert a new row with surname "Jones".

It may help to think about locks in a slightly different way - locks only have an effect when SQL Server attempts to obtain another lock which may be incompatible (i.e. it is not legal to have both locks at the same time). For example if you have an exclusive key-lock on rows with age > 18 and attempt to insert or modify a row with age = 42 then SQL server will first attempt to obtain the relevant locks - seeing that there is an existing key lock for rows with age > 18 SQL Server determines that the locks are incompatible and takes action accordingly.

like image 82
Justin Avatar answered Oct 19 '22 23:10

Justin