Polish your eyes and look at SQLServer to talk about the lock mechanism.

Before talking about the locking mechanism of SQLServer, let's think about the following scenario: When you ride your car to your destination during the hot summer, there are several long red lights on the road, is it depressing? Sometimes you may not be able to handle a red light. In fact, it is not a problem in most cases. If there are a lot of cars, it is hard to say. Because

Before talking about the lock mechanism of SQL Server, let's think about the following scenario: When you ride your own car to your destination during the hot summer, there are several long red lights on the road, is it depressing? Sometimes you may not be able to handle a red light. In fact, it is not a problem in most cases. If there are a lot of cars, it is hard to say. Because

Before talking about the lock mechanism of SQL Server, let's think about the following scenario: When you ride your own car to your destination during the hot summer, there are several long red lights on the road, is it depressing? Sometimes you may not be able to handle a red light. In fact, it is not a problem in most cases. If there are a lot of cars, it is hard to say. Because too many people do not comply with the rules, they all take shortcuts to achieve their goals and are unwilling to wait. In this way, the traffic police are available. The role of traffic police is to maintain the rules of these traffic lights. These traffic lights are like locks, locking or extending your destination time. But what will happen if no traffic police do not comply with the traffic light rules? Everyone knows about it.

There is a lock manager in the Transaction Manager provided in an article in this series, which is the traffic police here. It maintains the lock in SQLServer. Most of the cases mentioned earlier refer to the fact that when the system transaction volume is not large, the lock will never be a big problem. Unless you know that your system will always be used by several people, you have to go deep into the unlock mechanism to avoid data security and efficiency problems caused by the rise in concurrency in the future. Before studying the lock, assume that you have understood the ACID concept of the transaction, which is the essence of the entire SQL Server. If there is no transaction, you don't need to talk about the lock. anything except the transaction needs to be locked requires this mechanism to make SQL not free. After all, the lock is a balance between concurrency and data security mechanism. If no lock is available, any SQL statement can overwrite the data executed by other SQL statements, and the data will be inconsistent. If the lock is too harsh, it will affect the concurrency and efficiency of the database system (including the additional overhead of the lock itself ). In this case, you need to weigh the two. The SQLServer lock manager acts as the role to weigh the relationship between the two, as shown in:

There are too many lock knowledge points in SQL Server. For example, the lock mode can be divided into: Shared lock (S), update lock (U), exclusive lock (X) architecture lock (Sch-S, Sch-M), intention lock (IS, IU, IX), conversion lock (SIX, SIU, UIX), large capacity update lock (BU ); locks are divided into database locks, file locks, table locks, heap locks, index locks, page locks, key locks, partition locks, row locks, application locks, and metadata locks; there is a compatibility problem between locks; locks will be upgraded based on the situation; deadlocks may occur if the lock control is poor; isolation of pessimistic locks: uncommitted read, committed read, Repeatable read, and serializable; optimistic lock isolation: Read/submit Snapshot isolation, Snapshot isolation, and bolt (shuan) lock... It may take a lot of time to clarify a lot of problems. We still want to analyze a specific problem with the style of the previous articles-lock upgrade.

1. Preparation

There is a dynamic management view to view all the locks: sys. dm_tran_locks, there is a dynamic management view to view which requests are blocking other requests: sys. dm_ OS _waiting_tasks

2. What is lock upgrade?

Lock upgrade refers to the fine-to-coarse conversion of lock granularity. For example, convert a row lock to a table lock.

3. Do I need to update the lock?

Generally, the smaller the lock granularity, the better the concurrency, but the more locks are needed if there are more to be locked, which will consume the cpu and memory of SQLServer. The memory occupied by a lock is about 96 bytes. You can calculate the amount of memory required to lock millions of tables with row locks. Management locks (creating locks, maintaining locks, and destroying locks) are also costly and consume cpu resources. If a large lock is used, the millions of locks are merged into a lock, which facilitates resource consumption.

4. When will the lock be upgraded?

SQLServer occurs when the page or number of rows locked is too large. How can we realize it is too big? Two methods are used to identify: the number of locks requested for exceeds the threshold value of the number of locks; the lock manager consumes too much memory for a single query and exceeds the memory critical value. If another one exceeds the critical value, SQLServer will try to upgrade. Note that the lock data and memory values here are generated by the same query, rather than the total. The critical value here is not fixed. SQLServer uses a heuristic algorithm to dynamically adjust it.

5. control lock upgrade

SQLServer provides some portals for us to control lock upgrade. In SQLServer2008, you can use:

Alter table test

Set (lock_escalation = auto | table | disable)

We can also display the specified pagelock and tablock prompts in the code to force to use a thicker lock. However, if this setting is unreasonable, the concurrency will be reduced. We recommend that you do not use it unless you are clear about the impact.

6. Examples

6.1 create databases and tables:

Create database Test

Create table test

(

ID identity (1, 1) primary key,

[Name] varchar (50) not null default '',

CreatedTime datetime not null default getdate ();

)

View the current lock status:

By default, a connection has a shared lock for the entire database.

6.2 insert several 100,000 records cyclically:

While 1 = 1

Insert into test (Name) values ('kk ')

Lock snapshot during insertion:

The Snapshot contains three database share locks, one page-level intention exclusive locks, one table-level intention exclusive locks, and two row-level exclusive locks.

Three database share locks: As mentioned above, a default connection has a share lock for the entire database;

One page-level intention exclusive lock and one table-level intention exclusive lock: some pages and tables indicate that some of the resources have actually been locked for protection, this benefit allows other request locks to be checked at the table page level, reducing unnecessary and finer lock requests to improve performance. For example, in this case, if the alter operation is allowed, the Operation will wait because there is a table-level exclusive lock, and it prompts that the alter operation has a dynamic table.

6.3 tracking Lock: Escalation event

In profiler, set to only trace Lock: Escalation events and Lock upgrade events.

6.4 update table records:

Update test set name = 'name' where name = 'kk'

The Lock: Escalation event is triggered in profiler:

When the snapshot is updated (in order ):

For example, the update operation locks the updated row in exclusive mode.

For example, the update operation locks the entire table with an exclusive lock and locks its related metadata table with the schema stability lock (Sch-S.

For example, the schema stability lock (Sch-S) lock for the metadata table is released, and the exclusive lock for the entire table is left.

From the above analysis, it is found that the SQLServer lock mechanism is a bit complicated, but it is also very interesting. After research, you will find that it is really intelligent. This is the end of today's analysis. If your description is inappropriate, please note that. Common progress is the final principle. (Source: Blog)