Shulou(Shulou.com)05/31 Report--

How to understand the lock in the database, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Background

There is a table called Hou Gong Jiali in the database, and millions of new little sisters are inserted into the table every day. Time flies, day and night, love for a long time, the table has billions of little sister data, see billions of little sisters, every night, I am worried to death, so many little sisters, I turn the card?

The solution, of course, is to streamline the administration, delete those with age > 18, and make room for young sisters.

So I added a regular Mini Program to the database, and every Sunday, I automatically ran the following script

Delete from 'Imperial Palace Beauty' where age > 18

At first I felt good about myself, but later I found that something was wrong. Every Sunday, the script was executed all day, and it took a long time to run. The point is that on this wonderful Sunday, I want to read the data of this table again. I can't read it any more. How can it be so empty? I'm so hard!

Why

Can not go on, the real background is that the company encountered a massive data table, every time the clean up of historical data, our program because can not read the data of this table, crazy error report, later found that it is because the timing of the delete statement design is unreasonable, resulting in the database data from Row lock to table lock (Table lock)?.

Solve this problem in the process of the database lock-related learning, here the learning results, share with you, hope to help you.

I will discuss the SQL Server lock mechanism and how to use the SQL Server standard dynamic management view to monitor locks in SQL Server. I believe that other data locks are more or less the same, which is of certain reference significance.

Foreshadowing knowledge

Before I begin to explain the SQL Server locking architecture, let's take a moment to describe what ACID (atomicity, consistency, isolation, and persistence) is. ACID refers to the database management system (DBMS) in the process of writing or updating data, in order to ensure that the transaction (transaction) is correct and reliable, it must have four characteristics: atomicity (atomicity, or indivisibility), consistency (consistency), isolation (isolation, also known as independence), persistence (durability).

ACID#

Atomicity (Atomicity) #

All operations in a transaction, either completed or not completed, do not end at some point in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed. That is, the transaction is inseparable and irreducible.

Consistency (Consistency) #

The integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all default constraints, triggers, cascading rollbacks, and so on.

Isolation (Isolation) #

The ability of database to allow multiple concurrent transactions to read, write and modify its data at the same time, isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including uncommitted reads (Read uncommitted), committed reads (read committed), repeatable reads (repeatable read), and serialization (Serializable).

Persistence (Durability) #

After the end of the transaction, the modification of the data is permanent, even if the system failure will not be lost.

Source: Wikipedia https://zh.wikipedia.org/wiki/ACID

Transaction (Transaction:) #

A transaction is the smallest stack in a process and cannot be divided into smaller parts. In addition, some transaction groups can be executed sequentially, but as we explained in the atomicity principle, even if one of the transactions fails, all transaction blocks will fail.

Lock (Lock) #

Locking is a mechanism to ensure data consistency. SQL Server locks the object when the transaction starts. After the transaction completes, SQL Server releases the locked object. This locking mode can be changed based on the SQL Server process type and isolation level. These locking modes are:

Lock hierarchy #

SQL Server has a locking hierarchy that is used to get locked objects in this hierarchy. The database is at the top of the hierarchy and the rows are at the bottom. The following figure illustrates the lock hierarchy of SQL Server.

Shared (S) lock (Shared (S) Locks) #

This locking type occurs when an object needs to be read. This locking type doesn't cause much of a problem.

Exclusive (X) lock (Exclusive (X) Locks) #

Occurs when this lock type occurs to prevent other transactions from modifying or accessing the locked object.

Update (U) lock (Update (U) Locks) #

This lock type is similar to an exclusive lock, but it has some differences. We can divide the update operation into different phases: the read phase and the write phase. During the read phase, SQL Server does not want other transactions to have access to this object to make changes, so SQL Server uses update locks.

Intention lock (Intent Locks) #

Intentional locking occurs when SQL Server wants to acquire a shared (S) lock or an exclusive (X) lock on some of the resources lower in the lock hierarchy. In fact, when SQL Server acquires a lock on a page or row, the intent lock needs to be set in the table.

SQL Server locking

With this background, we try to find the locks again in SQL Server. SQL Server provides many dynamic management views to access metrics. To recognize SQL Server locks, we can use the sys.dm_tran_locks view. In this view, we can find a great deal of information about the currently active lock management.

In the first example, we will create a demo table without any indexes and try to update the demo table.

CREATE TABLE TestBlock (Id INT, Nm VARCHAR) INSERT INTO TestBlock values (1) In this step, we will create an open transaction and analyze the locked resources. BEGIN TRAN UPDATE TestBlock SET Nm='NewValue_CodingSight' where Id=1 select @ @ SPID

Once you've got the SPID, let's see what's in the sys.dm_tran_lock view.

Select * from sys.dm_tran_locks WHERE request_session_id=74

This view returns a lot of information about active lock resources, but some data that is difficult for us to understand. Therefore, we must sys.dm_tran_locks join some other tables.

SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME (dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME (dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME (partitions.OBJECT_ID) END AS ObjectName Partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode Dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID () and request_session_id=74 ORDER BY request_session_id, resource_associated_entity_id

In the figure above, you can see the locked resources. SQL Server acquires the exclusive lock in the row. (RID: the row identifier used to lock a single row in the heap.) at the same time, SQL Server acquires exclusive locks and TestBlock table intentional locks in the page. This means that no other process can read this resource until SQL Server releases the lock, which is the basic locking mechanism in SQL Server.

Now, we will populate the test table with some synthetic data.

TRUNCATE TABLE TestBlock DECLARE @ K AS INT=0 WHILE @ K 0 GOTO delete_more SET ROWCOUNT 0

Create an index to make the query as efficient as possible to reduce the locking space of the query

Without an index, a table scan may increase the likelihood of lock escalation, and even more frightening, it increases the likelihood of deadlocks and often has a negative impact on concurrency and performance.

Create an appropriate index according to the query conditions to maximize the efficiency of the index lookup. One goal of this optimization is to make the index lookup return as few rows as possible to minimize the cost of the query.

If another SPID currently holds an incompatible table lock, lock escalation will not occur #

Locking upgrades always upgrade to table locks, not page locks. If another SPID holds an IX (intent exclusive) lock that conflicts with the upgraded table lock, it acquires a finer-grained level of locking (row, key, or page) and makes additional upgrade attempts on a regular basis. A table-level IX (intent exclusive) lock does not lock any rows or pages, but it is still incompatible with upgraded S (shared) or X (exclusive) TAB locks.

As shown below, if an operation is always completed in less than an hour, you can create a sql containing the following code and schedule it to be executed before the operation

BEGIN TRAN SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1'0 WAITFOR DELAY'1 UPDLOCK 00' COMMIT TRAN

This query gets and keeps the IX lock on the mytable for an hour, which prevents lock upgrades on the table during that time.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.