The most comprehensive explanation of the locking mechanism in MySQL

Preface

Differentiate according to the granularity of locking

• Global Lock
• Table lock
• Row Lock
  • Record Lock
  • Gap Lock
  • Pro Key Lock

According to the locking scenario

• Optimistic Locking
• Pessimistic Lock

Global Lock

The lock object is: the entire database instance

Flush tables with read lock (FTWRL)-makes the entire database read-only

Usage scenario: Make a logical backup of the entire database

Full database logical backup

Why do we need a global lock to back up data?

For example, for sales, I have one table to record shipments and another table to record deductions. As a result, I backed up the shipment record table. At this time, someone bought something, but only the payment was deducted but there was no shipment record. This is obviously not acceptable.

The official logical backup tool is mysqldump. When mysqldump uses the parameter –single-transaction, a transaction is started before importing data to ensure a consistent view. Thanks to the support of MVCC, the data can be updated normally during this process. However, this is based on transactions and is not available for the MyISAM data engine. In this case, it is possible that some tables are not based on the InnoDB data engine.

Of course, if all are innodb data engine tables, then it is better to use the default mysqldump to add the parameter –single-transaction to perform global logical backup.

Difference between FTWRL and set global readonly=true

1. Readonly will be used in other logic (different systems have different)
2. ftwrl can automatically release the lock when the client connection is disconnected to prevent deadlock problems

Table lock

Command: lock table {tableName} read/write (write is more powerful than read, if you can write, you can also read), unlock table

The locked resource only allows the current thread to perform the corresponding operation. And the current thread can only perform the corresponding operation on the locked table.

For example: lock table t1 read, the current thread can only read but not write, and other threads cannot read or write

MDL lock

No explicit use is required, it is automatically added when accessing the table (to prevent problems caused by changes in the table structure)

Apply an MDL read lock when adding, deleting, modifying, or checking a table, and apply an MDL write lock when changing the structure of a table.

• MDL read lock (shared lock), locks are not mutually exclusive. Therefore, multiple threads can be allowed to perform addition, deletion, modification and query at the same time
• MDL write lock (exclusive lock), this lock is mutually exclusive with other read-write locks. That is, the current data change, query or structure change must wait for other MDL write locks to be released before execution

Row Lock

Row locks are implemented at the engine level by each engine (MyISAM does not support row locks, so the engine can only perform update operations on one thread at a time)

In a transaction: row locks are added when a row or multiple rows of data are needed, but all row locks are not released until the transaction is committed. In other words, if other threads need to access the row data, they need to wait until the thread's transaction is committed before they can access it.

Example:

Thread A does the following
begin;
update t1 set a=1 where id=1;

update t2 set b=2 where id=2;

commit

At this time, thread A locks t1's id=1 and t2's id=2 respectively. If thread B accesses t1's id=1 at this time, it cannot access it. Even if the first statement has been executed, thread B can only obtain the data after thread A performs a commit operation.

Therefore, the point we need to pay attention to is: when performing transaction operations, if the update is not sequential, then try to execute the statement with the most access last (because locking is sequential, but releasing locks is released together)

Deadlock

Many situations will cause deadlocks, most of which are caused by problems with database operations. For example

Thread A and Thread B both modify id=1 and id=2 and start transactions

Thread A first modifies id=1, causing id=1 to be locked by thread A

Thread B modifies id=2, causing id=2 to be locked by county B

At this time, thread A has to wait for id=2 to release the lock and then perform the operation on id=2, while thread B has to wait for id=1 to release the lock and then perform the operation on id=1.

This results in a cyclic deadlock situation.

There are two strategies for dealing with this problem:

1. Go directly into waiting until timeout. This timeout can be set by the parameter innodb_lock_wait_timeout (default 50s). This cannot be set too short, what if it is not a deadlock?
2. Initiate deadlock detection. When a deadlock is found, actively roll back a transaction in the deadlock chain to allow other transactions to continue executing. Setting the parameter innodb_deadlock_detect to on (the default is on) means turning on this logic.

Record Lock

A case of row lock

It is aimed at a certain record information locked by the transaction after locking

Triggering condition: The query condition is accurately matched and the matching condition field is unique

For example: update t1 set name="张三" where id=12138

Function: When a record is managed by the current transaction, it will not be acquired by other transactions after being locked, causing "repeated reads" and "data dirty reads" problems

Gap Lock

A case of row lock

The gap means the data in between

There are multiple unfilled data in the primary key index id. At this time, if there are two threads A and B, A is querying data between 0-10, and B is inserting data into id=3, it will cause data dirty read problem.

Therefore, when performing a transaction in a range such as between, a gap lock will be added for constraint.

Pro Key Lock

The temporary key lock will lock the queried records, and will also lock all the gap spaces within the range query, and then it will also lock the next adjacent interval.

(Lin means adjacent)

Optimistic and pessimistic locking

Summarize

This is the end of this article about the locking mechanism in MySQL. For more information about the locking mechanism in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!