Description of various locks in sqlserver

 

The locking prompt indicates that holdlock retains the shared lock until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page no longer needs it. Holdlock is equivalent to serializable. Do not issue a shared lock or provide an exclusive lock. When this option is enabled, it may read uncommitted transactions or a group of pages that are rolled back during reading. Dirty reads may occur. Applies only to select statements. Paglock usually uses the page lock where a single table lock is used. Readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level. Readpast skips the lock row. This option causes the transaction to skip the rows locked by other transactions (these rows are usually displayed in the result set), rather than blocking the transaction, wait for other transactions to release the locks on these rows. The readpast lock prompt is only applicable to transactions that run at the committed read isolation level and read only after the row-Level Lock. Only applicable to select statements. Readuncommitted is equivalent to nolock. Repeatableread performs scanning with the same lock semantics as transactions running at the repeatableread isolation level. Rowlock uses row-level locks instead of coarse-grained page-level locks and table-level locks. Serializable performs scanning with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock. Tablock uses table locks instead of finer-grained row-level locks or page-level locks. Before the statement ends, SQL Server keeps holding the lock. However, if both holdlock is specified, the lock will be held until the transaction ends. Tablockx uses the table's exclusive lock. This lock prevents other transactions from reading or updating tables and holding them until the statement or transaction ends. Updlock uses the update lock when reading the table, instead of the shared lock, and keeps the lock until the statement or transaction ends. Updlock allows you to read data (without blocking other transactions) and update data later. It also ensures that the data has not been changed since the last read. Xlock uses the exclusive lock and remains until the end of the transaction on all data processed by the statement. You can use paglock or tablock to specify the lock. In this case, the exclusive lock applies to the appropriate level of granularity.


Deadlock
When multiple sessions access some database resources at the same time, a deadlock may occur when each session requires resources in use by other sessions. Deadlocks may occur in multi-threaded systems, not only in relational database management systems.
Lock type
A database system may lock data items in many cases. The possibilities include:
 
 

  
  
Rows-a whole row in the database table
  
  
Pages-a set of rows (usually several KB)
  
  
Extents-usually a collection of several pages
  
  
Table-entire database table
  
  
Database-entire locked database table
 
 
Unless otherwise stated, the database selects the best locking method as needed. However, SQL Server provides a way to avoid default behaviors. This is done by the lock prompt.
Lock prompt
Tansact-SQL provides a series of lock prompts at different levels. You can use them in select, insert, update, and delete to tell SQL Server how to reset the lock. Possible prompts include:
Fastfirstrow-select and optimize the first row in the result set
Holdlock-holds a shared lock until the transaction is completed
Nolock-shared or exclusive locks are not allowed. This may result in data rewriting or returning without confirmation; therefore, dirty data may be used. This prompt can only be used in select.
Paglock-lock table
Readcommitted-read only the data confirmed by the transaction. This is the default behavior of SQL Server.
Readpast-Skip the row locked by other processes, so the returned data may ignore the row content. This can only be used in select.
Readuncommitted-equivalent to nolock.
Repeatableread-use a lock on all data in the query statement. This prevents other users from updating data. However, new rows may be inserted to the data by other users and read by the users who have recently accessed the data.
Rowlock-lock data at the row level. SQL Server usually locks pages or tables to modify rows. Therefore, when developers use a single row, they usually need to reset this setting.
Serializable-equivalent to holdlock.
Tablock-Lock Based on the table level. You may need to use this prompt when running multiple table-level data operations.
Updlock-when reading a table, use the update lock instead of the shared lock, and keep the lock until the transaction ends. Its advantage is that it allows you to read data without a lock and update data as quickly as possible.
Xlock-exclusive lock on all resources until the transaction ends. Microsoft divides the prompts into two categories: granularity and isolation-level. Granularity prompts include paglock, nolock, rowlock, and tablock. The isolation-level prompts include holdlock, nolock, readcommitted, repeatableread, and serializable.
 
You can use these prompts in the Transact-SQL statement. They are placed in the declared from part, after. The with statement is optional in SQL Server 2000, but Microsoft strongly requires that it be included. This makes many people think that this statement may be included in the SQL Server Release in the future. The following is an example of applying the prompt to the from clause: [from {<table_source> }[,... n] <table_source >:: = table_name [[as] table_alias] [With (<table_hint> [,... n])] <table_hint >:={ index (index_val [,... n]) | fastfirstrow | holdlock | nolock | paglock | readcommitted | readpast | readuncommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock | xlock}
Vocabulary
Session)
The sequence of operations performed by the English query engine in English query. The session starts when the user logs on and ends when the user logs off. All operations during a session constitute a transaction scope and are governed by the permissions determined by the login user name and password. Heap table)
If a table has no indexes, data rows are stored in random order. This structure is called heap. This type of table is called a heap table. Intent lock)
A lock placed at a level of the resource hierarchy to protect shared or exclusive locks on lower-level resources. For example, before the SQL Server 2000 database engine task application table shares or row locks, place the intention lock on the table. If another task tries to apply the share or exclusive lock to the table level, it is blocked by the table-level intention lock controlled by the first task. The second task does not need to check the page or row lock before locking the table, but only needs to check the intention lock on the table. Exclusive lock)
A lock that prevents any other transaction from obtaining the lock on the resource until the original lock on the resource is released at the end of the transaction. The exclusive lock is always applied during update operations (insert, update, or delete. Isolation level)
Transaction attribute that controls the degree to which isolated data is used by a process and prevents interference from other processes. Setting the isolation level defines the default locking behavior of all select statements in SQL Server sessions. Extended (Disk) area (extent)
The unit that is allocated to an SQL Server Object (such as a table or index) when more space is required. In SQL Server 2000, an extension is eight adjacent pages. Lock Granularity)
Data in SQL Server is stored in 8 KB as a page, and eight consecutive pages form an extended (extent ). This method is used to allocate disk space when creating a database. When the database capacity increases, more pages and extensions need to be created. Locking Based on the Data Storage Structure (row, page, extent) means the lock granularity.
In SQL Server 2000, the lowest lock granularity is the row lock. SQL Server can independently lock rows, data pages, extensions, and tables. Assume that only one row of record is affected in the update operation, SQL Server locks the row record, and other users can modify the record only after the record update operation is complete. On the other hand, other users can modify row records that are not locked. Therefore, row-level locks are optimal for concurrency.
Now, if the update operation affects 1000 rows of records, does SQL Server lock one row at a time? This means that if there is such an option, 1000 locks are required if the memory permits. In fact, SQL Server determines whether to use several page locks, extended locks, or table locks based on whether the data is distributed on consecutive pages. If the SQL Server adds a page lock, the records on these pages cannot be accessed or modified by other users, even if some data on the page does not belong to the 1000 rows of records. This is a balance between concurrency performance and resource consumption.
SQL Server is very sensitive to the resources required for the lock. That is to say, when the SQL Server Query Optimizer detects that the available memory is low, it will use the page lock to replace multiple row locks. Similarly, when the memory consumption is lower, the table locks and several extension locks are preferentially selected. Lock information identifier
Lock type:
 
 

  
  
RID: Row identifier. Used to lock a single row in the table.
  
  
Key: key, the row lock inside the index. Used to protect the key range in a serializable transaction.
  
  
PAG: data or index page.
  
  
Ext: a group of eight adjacent data pages or index pages.
  
  
Tab: the entire table, including all data and indexes.
  
  
DB: database.
 
 
 
^ _ ^, Which is a brief introduction. In fact, I think there are too many locks to be introduced. Maybe there are some wrong things to be written. Please give me some advice. The Chinese Translation of words is applied from the SQL Server online help (books online. Next, let's start with the article, which is also published on the DBA website.
 
 
 
I have been using SQL Server for more than six years. I think that I am still familiar with SQL Server, and I like to figure out something inside SQL Server.
When I was teaching an SQL Server programming course, I noticed that Microsoft's msdn mentioned the lock compatibility and listed a table on msdn about the compatibility relationship.
After reading this link table, I want to know if there is an intent lock for update (intent update lock )? So I began to read relevant materials. This article is also the result of my research. This article is intended for readers who have some knowledge about the isolation level, intent lock, deadlock, and lock granularity. If you do not know about these fields, I suggest you read the relevant documents before reading this article.
I hope this article will help you better understand SQL Server locks. Some skills may also help you with SQL Server programming.
It must be noted that even if you do not know how the Lock works, you can enjoy using SQL Server for a long time and create high-quality code and database designs. However, if you like to explore the internal mechanism of things as I do, or your work requires some performance knowledge, I would be happy to teach you something useful.
Update locks)
A typical deadlock occurs when spid x locks resource A and waits for resource B to be locked, while spid y locks resource B and waits for resource A to be locked. In this way, a deadlock occurs. If you do not understand, query msdn or related materials.
Now we can imagine deadlocks in more cases. Assume that spid x adds a shared lock to resource A, and spid y also adds a shared lock to resource A, because it is a shared lock, so there is no problem. Now, X wants to upgrade the shared lock to exclusive lock for updating resources. X must wait for y to release the shared lock before it can be done. when X is waiting, y also wants to do the same thing. In this way, X is waiting for y release, and Y is waiting for X release, resulting in a deadlock. This deadlock is called a conversion deadlock ).
This situation is common. To avoid this deadlock, the update lock mechanism is introduced. The update lock allows the connection to read the resource and declares that it is required to lock the resource because the data is to be edited. SQL Server does not know in advance that a transaction needs to convert the shared lock into an exclusive lock. Of course, there is a special case, that is, only one SQL statement can complete the read and update operations, for example, update XXX (select YYY ....) this type. For general select statements, the updlock prompt must be displayed.