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

This article focuses on "what is the MySQL lock mechanism", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the MySQL locking mechanism"?

Whenever there are multiple connections that modify data at the same time, the problem of concurrency control is involved. MySQL implements two levels of concurrency control: the service layer and the engine layer.

Lock classification according to usage scene

Shared locks: shared locks (shared lock) are also known as read locks (read lock). Shared locks are shared, or do not block each other. Multiple connections can read the same resource at the same time without interfering with each other.

Exclusive locks: exclusive locks (exclusive lock) are also known as write locks (write lock). Write locks are exclusive, that is, one write lock blocks other write and read locks.

Classified according to the idea of locking

Pessimistic lock: maintain a pessimistic attitude towards data being modified by the outside world, and the data is locked during the whole data processing process.

Optimistic lock: it believes that the data will not cause conflicts in general, and it will only check whether the data conflicts or not when the data is updated.

Classified according to lock granularity

Global lock: locks the entire database instance, which makes the entire database instance read-only.

Table-level lock: a way to lock an entire table. MySQL table-level locks are divided into table locks and metadata locks.

Row-level locks: row locks support concurrent processing to the maximum extent. Row locks are implemented in the storage engine layer, while row locks are not implemented in the MySQL service layer. InnoDB storage engine row-level lock types: Record Lock, Gap Lock, Next-key Lock.

Read-write lock

Shared locks: shared locks (shared lock) are also known as read locks (read lock). Shared locks are shared, or do not block each other. Multiple connections can read the same resource at the same time without interfering with each other.

Lock command select. Lock in share mode; test

When testing, set the transaction to commit manually: set autocommit = 0. If not explicitly prompted later, the autocommit is 0.

During the test, we open two windows and establish two connections. Window 1 and window 2 correspond to transaction An and transaction B, respectively.

Window 1: query the row data of id=6 and add read locks to return the data correctly.

Window 2: still query the row data of id=6 and add a read lock to return the data correctly. There is no conflict between reading.

Window 1: performs a write operation (update statement) on the row of id=6. Before the transaction of window 2 commits, the write operation blocks and may time out.

If the write lock wait time is too long, it will time out.

Window 1 mysql > update user set age = 20 where id = 6; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

If a transaction in window 1 performs a write operation on the same row of data while the transaction in window 2 is waiting for a write operation, it will cause a deadlock error.

Window 2 mysql > update user set age = 30 where id = 6; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction write lock

Exclusive locks (exclusive lock) are also known as write locks (write lock). Write locks are exclusive, that is, one write lock blocks other write and read locks.

Lock command select. For update; test

Window 1: query the row data of id=6 and add a write lock to return the data correctly.

Window 2: still query the row data of id=6 and add write locks, blocking. Writing conflicts.

Window 1: performs a write operation (update statement) on the row of id=6. The write operation is not blocked.

After the window 1 transaction is committed, the query statement of window 2 returns the result.

Pessimistic lock and optimistic lock

Whether optimistic lock or pessimistic lock is a concept defined by people, it is not a lock implementation, it is an idea. Optimistic lock is more suitable for scenarios with more reading and less writing, while pessimistic lock is suitable for scenarios with more writing and less reading.

Pessimistic lock

When we modify a piece of data in the database, in order to prevent others from modifying the same row of data at the same time, we lock the data to prevent concurrency problems. With the help of the database locking mechanism, this way of locking and then modifying the data before modification is called pessimistic Pessimistic Lock.

Pessimistic lock has strong exclusivity and exclusivity, and the data is locked in the whole process of data writing. The implementation of pessimistic locks often requires the locking mechanism provided by the database.

The realization of pessimistic lock:

The locking mechanisms of database, such as row lock, table lock, read lock and write lock, are all pessimistic locks.

The synchronized keyword learned in Java is also pessimistic.

Optimistic lock

Optimistic lock is relative to the concept of pessimistic lock, optimistic lock is based on the assumption that there is no concurrency conflict in data in general, and the conflict of data will be verified only when the data is updated. If there is a conflict, tell the user the result, and it is up to the user to decide what to do next.

Optimistic locking is a loose locking method, which does not need to use the locking mechanism of the database itself.

Implementation of optimistic locks:

MVCC, database multi-version control uses version number to control the concurrency of data update, which is the realization of optimistic lock.

CAS, compare and swap is the implementation of optimistic locks in Java.

Global lock

Global lock: locks the entire database instance, which makes the entire database instance read-only.

Lock command (FTWRL) Flush tables with read lock; application scenario

Global locks are often used to logically back up the entire database instance.

During the global lock period, both the data update operation (DML) and the table structure modification operation (DDL) of the business will be locked.

At this point, do you have a question: when do you use mysqldump directly for backup in development? when do you use FTWRL?

When mysqldump, the official logical backup tool, uses the parameter-single-transaction, it starts a transaction when exporting the data to ensure that it gets a consistent view. When learning about transaction isolation, we learned that based on the consistent view of MVCC, the data in this process can be updated normally. But we need to know that transactions are the implementation of the engine layer, and not every storage engine supports transactions. We used mysqldump for most of the backups in our development, mainly because our storage engine used the default engine InnoDB for most of the time.

Table level lock

Table-level lock: that is to lock the whole table. The definition of a table consists of two parts: data and structure, so table-level locks are also divided into two categories: table locks and metaspace locks.

Watch lock

Table lock is the most basic locking strategy in MySQL, and it is also the one with the least overhead. The table lock locks the entire table and needs to be acquired before writing to the table (insert, delete, update, etc.), which blocks all read and write operations to the table by other users. Only when there is no write lock can other read users acquire the read lock. Do not block each other before reading the lock.

The write lock takes precedence over the read lock, so a request for a write lock may be inserted in front of the read lock queue, but the read lock cannot be inserted in front of the write lock.

Add and unlock command-add read lock lock tables to the table. Read;-adds a write lock to the table lock tables. Write;-release lock unlock tables; test table lock read lock test

Window 1: add a read lock to the user table.

Window 2: read the full table of the user table and return the full table data normally.

Window 2: modify the id=6 data in the user table, blocking. Read-write conflict.

Window 1: modify the id=6 data in the user table and report an error.

Window 1: the read lock is released, and the update data of window 2 is executed successfully.

Table lock write lock test

Window 1: add a write lock to the user table.

Window 2: full table query user table, blocking.

Window 1: update the data of id=6 in the user table, the update is successful.

Window 1: release the lock, and the full table query of window 2 returns the latest data.

Meta-space lock

Metaspace locks (matadata lock) are introduced in the MySQL5.5 version, and MDL read locks are added when adding, deleting, modifying and querying a table. MDL write locks are added when the table structure is changed. The role of MDL is to prevent concurrency conflicts between DDL and DML.

MDL locks are added by default and do not need to be explicitly added.

test

Window 1: query a piece of data in the table. At this time, a MDL read lock is added when the query statement is executed.

Window 2: add a field, where the alter statement is executed, and the MDL write lock is added. At this point, the read lock on window 1 is not released, so the alter statement will block.

Window 3: querying a piece of data in the table, due to the blocking caused by window 2, will also cause blocking when window 3 applies for a MDL read lock.

Window 1: commit the transaction, window 3 acquires the MDL read lock and returns the query result.

Window 3: the commit transaction releases the read lock, window 2 acquires the write lock, and the field is added successfully.

Row level lock

MySQL's row locks are implemented by various storage engines at the engine layer. Not all storage engines support row locking, for example, the MyISAM engine does not support row locking. Not supporting row locks means that only table locks can be used in concurrency control, which also means that only one update to the same table is executed at the same time, which seriously affects concurrency. InnoDB supports row locks, which is one of the important reasons why InnoDB can replace MyISM.

Two-phase locking protocol

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

InnoDB is a two-phase locking protocol. Locks can be locked at any time during transaction execution, locks are released only when commit or rollback are executed, and all locks are released at the same time.

After transaction An executes two update statements, transaction B also executes the update statement, but transaction B blocks until transaction A commits the transaction.

Row lock

We create a simple table t, where id is the primary key and an is the index, inserting six pieces of data.

CREATE TABLE `t1` (`id` int (11) NOT NULL, `a` int (11) DEFAULT NULL, `b` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx` (`a`) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into T1 values (0), (5) (5), (10), (15), (20), (25) and 25); Gap Lock), which locks the gap between two values.

For table T1, there are 7 gaps in 6 pieces of data, as shown in the following figure:

Let's take a look at the example of writing locks that we learned earlier:

Begin; select * from T1 where b = 5 for update; commit

The select query with a write lock is currently read and reads the latest data values.

The b field is not the index field of the table, it scans the entire table and adds a write lock to all rows that meet the criteria, as well as locks on both sides of the rows that meet the criteria.

There is no conflict between gap locks, and what conflicts with gap locks is the operation of inserting data into the gap.

Next-key lock

The combination of row lock and gap lock is called next-key lock. Each next-key lock is an interval that opens and closes before and after. Don't be confused here, the gap lock is an open space, and the next-key lock generated by the gap lock plus the row lock is an interval that opens before and closes behind.

The introduction of gap lock and next-key lock helps us to solve the problem of phantom reading.

The gap lock only takes effect under the repeatable isolation level, and if we set the isolation level to read commit, there will be no gap lock.

test

Earlier, when we were learning about other locks, to save trouble, we set autocommit to 0. Now we need to set autocommit to 1.

Window 1: use explicit transactions to add write locks through for update to lock rows and gaps that meet the criteria.

Window 2: update the row with id 0, which was not successfully updated with a lock.

Window 3: inserts data, satisfies the next-key lock of (0recom 5), blocking waiting.

Summary

According to the lock granularity, locks are divided into global locks, table locks and row locks.

The row lock is the implementation of the engine layer, and the row lock described in this paper is based on the implementation of the InnoDB storage engine.

InnoDB's row locks use the two-phase locking protocol, which is added when needed, and all locks are released at once only when the transaction commit or rollback.

There is a gap lock under the repeatable isolation level, and there is no gap lock if it is set to another isolation level. The gap lock is to lock the gap between the two sides of the row data. The gap lock plus row lock is called next-key lock, which is an interval that opens before and closes behind. Gap lock solves the problem of phantom reading.

We still need more hands-on practice when learning to lock.

At this point, I believe you have a deeper understanding of "what is the MySQL locking mechanism". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.