Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the MySQL locking mechanism is. It is very detailed and has a certain reference value. Friends who are interested must read it!

Preface

The purpose of designing locks in the database is to deal with the concurrency problem. When accessing resources concurrently, the database should reasonably control the access rules to resources.

The lock is a data structure used to implement these access rules.

In concurrent operations on data, the absence of locks may cause data inconsistencies and lead to the loss of updates.

Classification of locks optimistic locks and pessimistic locks

Optimistic lock: be optimistic about the possibility of update loss, first assume that there will be no update loss, and make a comparison when the data is finally updated.

CREATE TABLE `t` (`id` int (11) NOT NULL, `value` int (11) DEFAULT NULL, `version` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB; insert into t values

* * pessimistic lock: * * it is pessimistic about the possibility of update loss. Lock is added when querying to ensure that it will not be operated by other transactions. This can be achieved through select...fot update.

Select * from t where id = 1 for update; shared lock (S lock) and exclusive lock (X lock)

Shared lock (shared lock) means that lock resources can be shared among multiple transactions, which are generally added when reading data, also known as read lock (read lock).

Select * from t where id = 1 lock in share mode

Exclusive lock (exclusive lock,X lock), also known as write lock (write lock).

When transaction An adds an X lock to the data, other transactions cannot add any locks to the data until transaction A releases the X lock on the data.

Adding, deleting, and modifying all add X locks to the data, and using for update in the query statement will also add X locks.

S Lock X Lock S Lock √ × X Lock × × Global Lock distinguishes global lock according to lock granularity

As the name implies, a global lock locks the entire database, and after locking, the entire library cannot write to other transactions. A way to add global read locks is provided in MySQL, and the command is: flush tables with read lock (FTWRL).

-- add global read lock flush tables with read lock;-- to unlock unlock tables

Usage scenario: full library logical backup.

However, using global locks for backups has the following problems:

If the backup is on the main database, no updates can be performed during the backup, and the business will basically have to shut down.

If the backup is on the slave library, the binlog synchronized from the master library cannot be executed from the slave library during the backup, resulting in master-slave delay.

If all the tables in the library are innoDB engines or other storage engines that support transactions, you can use the official backup tool mysqldump.

When mysqldump uses the parameter-single-transaction, a transaction is initiated before the import data to ensure that a consistent view is obtained. Because of the support of MVCC, the data can be updated normally during this process.

If there is a storage engine such as MyISAM that does not support transactions in the library, mysqldump cannot be used.

Is it feasible to use set global readonly=true?

You cannot use set global readonly=true to make a logical backup after the entire library is read-only. There are two main reasons:

Read-only is generally used to distinguish between primary and secondary libraries. Modifying global variables has a great impact, so it is not recommended to modify it.

The exception handling mechanism is different. If the client disconnects abnormally after executing the FTWRL command, the server will automatically release the global lock. However, when read-ony is set to true, it will take effect permanently, and if the client is disconnected abnormally, the database will remain in a read-only state.

Table-level locks (table locks and MDL locks)

There are two types of table-level locks in MySQL: table locks and metadata locks (meta data lock,MDL).

Watch lock

Lock tables... Read;lock tables... Write;-- unlocks unlock tables

The overhead of using table lock is relatively small, locking is fast, and there is no deadlock, but the locking granularity is larger, the probability of lock conflict is higher, and the degree of concurrency is lower.

Table locks are not recommended in the innoDB storage engine and are only used in storage engines that do not have transaction support, such as MyISAM.

Metadata lock (MDL)

Metadata locks are introduced in MySQL5.5, and MDL does not need to be explicitly added. MDL read locks are added when adding, deleting, modifying and querying table data, and MDL write locks are added when DDL operations are performed on the table.

The purpose of metadata locking is to ensure the metadata consistency of tables when multiple transactions operate on the same table.

What would be the problem without a metadata lock?

Transaction isolation problem: for example, under the repeatable read isolation level, session A modifies the table structure during two queries, and the results of the two queries are inconsistent and cannot meet the requirements of repeatable readings.

Data synchronization problem: for example, when session An executes multiple update statements, another session B changes the table structure and submits it first, which will cause the slave database to redo the alter table statement first, and then redo the update statement, there will be a replication error.

MDL read locks are not mutually exclusive, because a table can support multiple transactions to add, delete, change and query at the same time, read locks and write locks, write locks and write locks are mutually exclusive, which is used to ensure the security of table structure changes.

When DDL is performed on the table, it will cause all additions, deletions, changes and queries to block. So be very careful when you modify or add fields to the table field.

Generally speaking, we will pay special attention to the DDL of big data scale so as not to affect the online business. However, you should also be careful when doing DDL operations on small tables, such as the following scenarios:

Transaction A starts first, and then a MDL read lock is added to table t.

Then transaction B adds fields to table t, which requires acquiring a MDL write lock, but since transaction A has not yet committed, the MDL read lock is not released, so transaction B will be blocked

It doesn't matter if it's just transaction B blocking. At most, DDL will execute later; but after that, all additions, deletions, changes and queries to table t will be blocked, so that table t cannot perform any read and write operations.

Intention lock

Intention lock is a lock added at the table level, which is divided into intention sharing lock (IS lock) and intention exclusive lock (IX lock).

The intention lock, as its name implies, indicates what type of operation to do next.

Intention shared lock (IS): when you are ready to add an S lock to the table data, you need to obtain the IS lock for the table first.

Intention exclusive lock (IX): when you are ready to add an X lock to the table data, you need to obtain the IX lock for the table first.

The reason for the intentional existence of locks, so in the above example:

Another main reason for the emergence of intention locks is to be more efficient when supporting different granularity locks.

Transaction An adds a row lock to a data row in table T, and transaction B adds a table lock to table T, but before adding it, you need to check whether any other transaction holds the X lock of the table, and if so, block it.

Transaction B is inefficient and time-consuming by traversing whether all rows in table T have locks.

Because the intention lock is a table-level lock, an IX lock will be automatically added by the database at the table level before transaction A updates the data to add a row lock, so when transaction B needs to acquire an X lock, it only needs to check whether there is an IX lock at the table level. If there is an IX lock, it means that another transaction is currently performing a write operation on the table or the data in the table, and the lock cannot be successfully added.

Row lock

Row locks in MySQL are implemented at the storage engine layer and are not supported by all storage engines. For example, there are no row locks in the MyISAM engine.

As the name implies, a row lock is added to a row of data. for example, if transaction A updates a row of data, it first adds a row lock, and then transaction B also updates the row data, it must wait until transaction A releases the row lock.

Timing of locking and unlocking row locks

In InnoDB transactions, row locks are added when needed, but not immediately when they are not needed, but not until the end of the transaction. This is the two-phase locking protocol.

Begin;update t set value = value + 1 where id = 1 * * update t set value = value + 1 where id = 2 * begin; update t set value = value + 1 where id = 1

Therefore, if you need to lock multiple rows in your transaction, try to put back the locks that are most likely to cause lock conflicts and affect concurrency as much as possible.

Gap lock

Gap lock, lock is the gap between two values.

We all know that every technology appears to solve a problem, so what is the gap lock to solve?

Suppose there is no gap lock, let's take a look at the following example, which is all under the premise of repeatable isolation level.

There is a table as follows:

CREATE TABLE `t` (`id` int (11) NOT NULL, `c` int (11) DEFAULT NULL, `d` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ENGINE=InnoDB;insert into t values

Suppose we were to perform the following SQL, how would we lock and unlock it?

Begin;select * from t where dail5 for update;commit

It is easy to understand that this statement hits the line of diter5, corresponding to the primary key id=5, so after the execution of the select statement, the line of id=5 adds a write lock, and because of the two-phase locking protocol, this write lock is released when the commit statement is executed.

Because there is no index on field d, this query does a full table scan. So, will the other 5 rows of records that are scanned but do not meet the conditions be locked?

Three queries were executed in transaction A, all of which acquired the write lock through for update and are currently read.

Assuming that only the row id=5 is locked, the execution results of the three queries are as follows:

The result returned by Q1 is (5pm 5pm 5)

The returned result of Q2 is (0rec 0penny 5), (5pr 5rem 5).

The result returned by Q3 is (0re0rem 5) (1jm 1rem 5) (5je 5pr 5)

Then the data of id=1 is queried in the results of Q3, which is called "phantom reading".

This breaks the semantics that the select * from t where dail5 fot update; in transaction A wants to lock the data of all dumb5.

Second, there will be data consistency problems.

If you get the binlog to the standby database in transaction B, you will get a different result.

Actual verification shows that the result is that not only the id=5 line is locked, but also all gaps are locked. This ensures that no new data can be inserted.

Next-key lock (key lock)

The gap lock and the row lock are called next-key lock, and each next-key lock is a front open and back closed interval. That is to say, after our table t is initialized, if we use select * from t where for update to lock up all the records of the entire table, we will form seven next-key lock, namely (- ∞, 0], (0 supremum 5], (5 supremum 10], (10) 15], (15) 20], (20, 25), (25, + 10).

Both the gap lock and the temporary lock are used to solve the problem of repeatable readability, and both the gap lock and the temporary lock will fail at the read commit level.

Locking rule

The rules for data locking in MySQL can be summarized into the following three categories:

Two principles

The basic unit of locking is next-key lock.

The objects accessed during the lookup process will be locked.

Two optimizations

An equivalent query on an index. When a unique index is locked, next-key lock is reduced to a row lock.

When the equivalent query on the index traverses to the right and the last value does not satisfy the equivalence condition, the next-key lock is reduced to a gap lock.

A BUG

The range query on the unique index accesses the first value that does not meet the condition

Deadlock and deadlock detection

What is a deadlock?

In the system that supports concurrent operations, different threads are cyclically dependent on resources, and threads hold the resources needed by each other, resulting in threads entering a state of infinite waiting, which is called deadlock.

Because of the existence of locking mechanism in the database, it will also lead to deadlock. For example:

Transaction A first acquires the row lock of id=1, and then transaction B acquires the row lock of id=2

Then transaction An acquires the row lock of id=2 and finds that it is held by transaction B and is blocked.

Transaction B wanted to acquire the row lock of id=1 and found that it was held by transaction An and blocked.

Two transactions entered a deadlock state.

When a deadlock occurs, there are two processing strategies:

Go directly to wait until the connection times out, which can be set through innodb_lock_wait_timeout.

Initiate deadlock detection and actively roll back one transaction in the deadlock after finding the deadlock so that other transactions can execute normally. Set the parameter innodb_deadlock_detect to on, which means that deadlock detection is turned on.

The above is all the content of the article "what is the MySQL locking mechanism?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.