SQL lock mechanism advanced

SQL lock mechanism advanced

Before reading this article, I would like to briefly introduce the locks and translate several specialized words.

What is a lock?

SQL Server 2000 uses locks to implement synchronization control when multiple users modify the same data in the database at the same time.

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.

Advanced SQL Server Locking Source: SQL-Server-Performance.com Author: androé s Taylor

I thought I knew SQL Server pretty well. I 've been using the product for more than 6 years now, and I like to know my tools from the inside out .......

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.

The following is a sample code:

USE Northwind go set transaction isolation level repeatable read go begin tran select * FROM Orders (UPDLOCK) WHERE OrderID = 10633

 

I noticed that I opened the transaction but did not close the transaction. In this way, the lock will always exist. If another connection view obtains the update lock on the same record, it will only wait until the first transaction ends. In this way, the two update locks are incompatible with each other on the same resource.

Run SP_LOCK to display the record lines, fields, and locks related to the above operation:

As we expected, the primary key OrderID is locked by the update lock. The value (89003da47679) in the Resource column in the figure indicates the hash value of the primary key 10633. SQL Server uses a hash table to store lock information.

The record row containing that row is locked by the update intention lock as we expected. The value in the resource column () indicates that the data page is the database's 1st files and the page number is #242. Unexpectedly, SQL Server adds an IX table lock. Since SQL Server does not use the U/IU type lock on the table lock, only the X/IX type lock can be seen at the table lock level.

When the update operation contains the where syntax, SQL Server scans the entire table and/or scans the index to determine which records will be changed. Before reading information from a table or index, SQL Server First locks the object. Since SQL Server knows that you are committing an update transaction, it will choose to update the lock instead of share the lock. This is to avoid the deadlock mentioned above-the conversion deadlock ).

When SQL Server determines that the record rows need to be changed, it will further upgrade the update lock to the exclusive lock on these records. If it is a heap table ), the lock is applied to the RID (row identifier). If the index table is clustered, the lock is applied to the primary key. This means that the UPDATE lock will be immediately upgraded to the exclusive lock, so when you perform the UPDATE operation, it is almost impossible to see this UPDATE process.

However, there are exceptions. If SQL Server uses an index to locate the record row, it locks the index page and adds an update lock to the index. If no data column is changed in the index, the update lock is not upgraded to the exclusive lock. The following is an example:

Begin tran update Region SET RegionDescription = 'south' WHERE RegionID = 4

 

Region is a heap table with only the primary key of a non-clustered unique index on the RegionId. Therefore, when the preceding query is completed, SQL Server scans the index on the RegionId and locks the index page and key. After the record row is found to be changed, the update query does not change the value of RegionId, so it is not upgraded to the exclusive lock. Run SP_LOCK to obtain the following information:

We can see that there is an IX lock on the RID. The lock is located on the RegionId index. We can also see that there is an IX lock on the table and an X lock on the RID. The KEY is locked on the RegionId index, and evidence can be obtained from the Indid column. There is also an update lock on the index, which is one of the moments when the update lock is activated.

After the query is complete, there are still two page locks-one on the index page () and the other on the heap. This is because the Indid (Index id) of the heap is 0.

Lock Granularity)

SQL Server has several lock types. You can select different granularity for each type.

If you run SP_LOCK or view the "current activation" Information in Enterprise Manager, you can see at least four or five different lock types. The following is a brief review of these types:

• Database (DB): This is a session lock. For example, it does not involve any transactions, but is just a connection between a user and a database. This prevents the database from being detached when a user connects to the database. It is worth noting that, although the master and tempdb of SQL Server cannot be detached, there is no DB lock on these two databases.
• Table (TAB): This is the rough logical lock in SQL Server. Intention locks are often applied at the table level (do you think intention locks are unsafe? Here is more detailed information .)
• Extent (EXT): This lock generally occurs when SQL Server creates a new table or increases the capacity of an existing table, rather than locking record rows. Therefore, when the file capacity changes, we often see the existence of this lock.
• Page (PAG): When SQL Server wants to lock many record rows at the same time, but there are few available lock slots, the Page lock will be used. Intention locks at the page level are more common. Currently, SQL Server versions (including SQL Server 6.5) provide the best performance for these locks.
• Key (KEY): Like the RID lock, it may be the best lock in SQL Server. The KEY lock is used for indexing, while the RID lock is used for heap tables. (Note: Row locks include KEY locks and RID locks. Considering the lock level, row locks are the best for concurrency. However, in terms of performance, row locks consume a lot of resources, relevant information can be found in the previous blog ).

In studying the locking behavior of SQL Server 2000, I think SQL Server focuses more on concurrency performance than speed in most cases. High concurrency performance means that many users can operate databases at the same time. Therefore, the locks are as small as possible, and the possibility of unnecessary locking of data required by others is smaller. On the other hand, if a large lock is used, a higher speed will be obtained. (Translator's note: the understanding of this sentence should be examined on the premise of balancing performance .)

When SQL Server 2000 finds that operations will lock more and more record rows, it will increase the lock level. For example, SQL Server 2000 will upgrade to the table lock and discard the separate pages, keys, and RIDs locks. Note: To increase the lock level, you must upgrade to the table lock instead of upgrading the RID/KEY lock to the page lock.

When will the SQL Server lock be upgraded? It cannot know the proportion of the table you will lock, so the only thing it cares about is the number of locks generated. When the lock uses a high proportion of memory, SQL Server begins to upgrade the locks on all connection transactions. When the lock slot is used, the upgrade will also begin. You can use SP_CONFIGURE to configure the number of available lock slots for SQL Server. For example, you can reduce the number to observe the Update Status of the lock.

SQL Server tries its best to use a small lock to ensure high concurrency performance. But sometimes SQL Server does not know how the data will change, so it will change the lock level according to its rules, and this change is not what you want. For example, a large lookup table only reads data. You can use a table lock to replace many KEY locks. You can use the lock prompt or SP_INDEXOPTION.

The lock prompt is very common. There are a lot of documents about this in the online help (BOL), so I will not repeat it in this article. SP_INDEXOPTION is a good method to force SQL Server to use a lock of a specific size.

With SP_INDEXOPTION, you can disable row-or page-level locks. That is to say, you do not need to lock the prompt-all the tables or the locks on the index are the size you specified. Even if it claims that the stored procedure is used for the lock Granularity on the index, it can also be stacked on the user. A good implementation method is to replace the @ IndexNamePattern variable with the table name, which is rarely known.

Research in this area is not over. If you use one of the two higher isolation levels and no indexes are available in the Search rules, SQL Server does not lock the entire table, you can also query as many records as possible. The following is an example:

USE Northwind go set transaction isolation level serializable go begin tran update dbo. Orders SET Freight = Freight x 1.25 WHERE Freight BETWEEN 100 AND 200

 

Run SP_LOCK in another window. When I run it here, I see 853 locks on the connection. There are 830 rows in the Orders table in the database Northwind, and each row has a lock. Roll back the UPDATE transaction and rewrite it. Create an index before UPDATE, as shown below:

USE Northwind go create nonclustered index FreightTest ON Orders (Feight) go set transaction isolation level serializable go begin tran update dbo. Orders SET Freight = Freight * 1.25 WHERE Freight BETWEEN 100 AND 200

 

Currently, run SP_LOCK to display only 25 locks. This is often overlooked during performance debugging. Even if you use the default read commited isolation level, it is a huge difference from creating indexes-136 locks and 24 locks.

Locking Trace Flags)

Some Trace tags can help us debug locks and discover deadlocks.

The trace flag is used to enable or disable SQL Server. Dbcc traceon command to set the trace tag. If you want to enable the trace tag when SQL Server starts, you only need to add '-t' to the startup parameter.

• 1200: displays all locks for all connections. This option will have a huge amount of output information, so I suggest using it only in a controllable environment. For example, there is only one connection at a time.

• 1204: enter information related to the deadlock. The following is an example of this information:

  Node: 1 KEY: 6: 885578193: 2 (010086470766) CleanCnt: 1 Mode: U Flags: 0x0 Grant List 0: Owner: 0x42c0b2e0 Mode: U Flg: 0x0 Ref: 2 Life: 02000000 SPID: 53 ECID: 0 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1 Input Buf: Language Event: update Region set RegionDescription = 'A' where RegionID = 1 Requested By ResType: LockOwner Stype: 'OR 'mode: u spid: 51 ECID: 0 Ec :( 0x42E25568) Value: 0x42c0b220 Cost: (0/0) Node: 2 RID: 6: 1: 300: 0 CleanCnt: 1 Mode: U Flags: 0x2 Grant List: 0 Owner: 0x42c0b320 Mode: S Flg: 0x0 Ref: 1 Life: 02000000 SPID: 51 ECID: 0 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1 Input Buf: Language Event: update Region set RegionDescription = 'A' where RegionID = 1 Requested By ResType: LockOwner Stype: 'OR 'mode: x spid: 53 ECID: 0 Ec :( 0x434A1568) Value: 0x42c0b240 Cost: (0/0) Victim Resource Owner: ResType: LockOwner Stype: 'OR' Mode: x spid: 53 ECID: 0 Ec (0x434A1568) Value: 0x42c0b240 Cost: (0/0)

   

  KEY: The index information involved in the deadlock. You can also use similar parameters to specify any other lock information, such as page, RID, and table.

  ECID is obtained from master. dbo. sysprocesses, which is used to differentiate the locks generated by different threads. Mode is a deadlock request Mode, such as S, X, or U.

  String "6: 885578193: 2" indicates that the database id is 6, the Object id is 885578193, and the index id is 2. The value in the parentheses below is the hash value of the lock, which is stored in the rsc_text column of the master. dbo. syslockinfo table. Unfortunately, this value is a one-way hash, that is, it alone cannot find the row of the locked record. Spid is the ID of the lock system process.

  Node 1 & 2 shows a deadlock. Both locks are in the waiting queue. "Requested By:" indicates this.

• 1205: prints information about lock management. After each deadlock search task is initialized, the trace tag notifies the lock management to print the search information. Before this option works, the system prompts that the trace mark 1024 must be given.
• 1211: Upgrade of all locks is prohibited. This tag notifies lock management not to upgrade any lock, even if the lock resources are used up.

Column Locks)

As you know, the lowest lock in SQL Server 2000 is the row lock. SQL Server does not directly provide column locks. The following describes how to simulate column-level locks through index locks.

Column locks are generally considered to be slow in some cases, and SQL Server is no exception. However, since the row lock does not automatically lock the table index, you can always use the locked data on the index page. We will use the Region table of the database Northwind as an example.

The Region table is a heap table with two fields: RegionDescription and RegionId. The RegionId field has a unique non-clustered index.

We use a simple UPDATE operation to UPDATE the content of the RegionDescription field.

USE Northwind go begin tran update Region SET RegionDescription = 'south' WHERE RegionDescription = 'south'

 

For this query, SQL Server does not use indexes because there is no index in the RegionDescription field. Therefore, SQL Server scans the entire table to find the record row to be updated. Once found, the update locks on those records will be upgraded to the exclusive locks. To confirm this, run SP_LOCK in another window. Therefore, the corresponding data should have the RID lock. In the window that runs SP_LOCK, enter a SELECT query:

SELECT * FROM Region

 

At this time, we will not enter the waiting status. If you look like me, you will like to see the execution plan, because the execution plan will tell us why we are not waiting at this time.

As shown above, to complete the SELECT Operation on SQL Server, You must SELECT an index scan to obtain data. Since SLECT * can be completed using indexes, it does not need to read the data on the stack. We call this query overwrite the query ).

Pay attention to the two principles in the process above. The first criterion is that the data involved in the query must be indexed. Remember, if the table has a clustered index, all non-clustered indexes will have an index field, which is the value of the clustered index field.

The second criterion is that the previous UPDATE operation cannot change the values of fields contained in any index. If it is changed (that is, the index value is also changed), it will be upgraded to the exclusive lock, so the above skills are also invalid.

Extended Lock Capability Table)

This table can be found in online help and MSDN. It identifies the locks that are compatible with each other. I will list a more complex table here and hope it will be useful to you:

Conclusion

I did find the update intention lock that is hard to grasp, and I did a lot of research on it. There is very little information about locks and locks in online help, so this study has also been added. I wrote this article after a lot of research, hoping to share the relevant knowledge with you.