MySQL > MySQL > MySQL > MySQL

As the name suggests, a global lock is a lock on the entire database instance. MySQL provides a global read lock with the Flush tables with read lock (FTWRL) command. When you want to make the entire library read-only, you can use this command to block DML statements, DDL statements, and commit statements for updating class transactions.

A typical use scenario for global locks is to do a full library logical backup.

What happens if you make the entire library read-only:

• If the backup is performed on the primary database, updates cannot be performed during the backup, and the business essentially shuts down.
• If the backup is performed on the secondary library, the secondary library cannot execute the binlogs synchronized from the primary library during the backup, resulting in a master-secondary delay.

It seems that the global lock is not good, then can not add?

If the global lock is not used to back up data in full, DML and DDL statements are executed in the database during the backup process. As a result, the data obtained by the system backup is not at a logical point in time, and the view is logically inconsistent.

MySQL’s RR(repeatable read) isolation level allows you to obtain a consistent view, ensuring that the same data is read multiple times within a transaction and the results are consistent. And because of the support of MVCC, the data can be updated normally in this process.

With this feature, why do you need FTWRL?

Consistent reads are fine, but only if the engine supports this isolation level. The MyISAM engine does not support the RR isolation level, so backup will have to be done through the FTWRL method.

The syntax is Lock tables tableName read/write. You can use unlock tables to release the lock actively or automatically when the client disconnects.

If lock tables T1 read, T2 write is executed in thread A, then all statements written to and written to T1 from other threads will be blocked. At the same time, thread A can only read T1 and read T2 before executing unlock tables.

In the absence of more fine-grained locks, table locking is the most common way to handle concurrency. InnoDB, which supports row locking, does not use the lock tables command to control concurrency, because locking the entire table has too much impact.

InnoDB supports multi-granularity locking, allowing both row locks and table locks. There are two types of intentional locks: intentional shared locks and intentional exclusive locks.

• An intentional shared lock (IS) means that the IS lock of a data row must be acquired before a shared lock can be added to the table
• An intended exclusive lock (IX) means that the IX lock of a table must be acquired before an exclusive lock can be added to the table

In fact, the purpose of intentional lock is similar to MDL, which is to prevent data inconsistency caused by DDL statement operation during the transaction. Table level lock type compatibility is summarized in the following figure:

A shared lock is also called a read lock. If a transaction acquires a read lock of a data row, other transactions can acquire the corresponding read lock but cannot acquire a write lock. That is, when a transaction reads a data row, other transactions can read it but cannot add, delete, or modify the data row.

As the name suggests, a record lock is a lock on a row (row lock for short), which is actually a lock on an index record. InnoDB supports row locking, so when updating the same row of data, there will be lock wait.

• When two sessions update data in different rows of an index field at the same time, the update is successful and no lock wait occurs.
• When two sessions update data on the same row of an index field at the same time, a lock wait occurs on one session.
• What happens when two sessions simultaneously update data on an ordinary field (no index), but on different rows?

In the third case above, when the updated field has no index, even if the record is on a different row, the lock wait occurs. So InnoDB’s record locks are loaded on indexes. If InnoDB locks records on non-index fields,

Next-key Lock is a combination of a record Lock and a gap Lock. When InnoDB scans an index record, InnoDB will first apply a record Lock to the selected index record and then apply a gap Lock to the gap on both sides of the index record.

Suppose an index contains the values 10, 11, 13, and 20. Possible next-key locks for this index cover the following range, with open left and close right.

(negative infinity, 10]	# minus infinity
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)	# is infinite
Copy the code

At this time, start two sessions according to the following table, and the execution can be successful until the sixth step, which starts blocking, and the eighth step can be executed normally. So sessionA is actually locking a range, locking the range (11, 13] and (13, 20]) where 13 is, so sessionB will block when it inserts 11 and 14.

In the case above where ID is secondary and not unique, what if id is unique?

If the id column is changed to the primary key, in the table above, steps 6 and 7 of sessionB can be successfully executed, and values in the ranges (11, 13] and (13, 20] can be successfully inserted, except in the case of primary key conflicts. The reason for this is that InnoDB degrades the lock to a record lock, which only locks a single record, because the index is unique. This improves concurrency.

• useSHOW ENGINE INNODB STATUSCommand to view the causes of recent deadlocks, which can help you tune your application to avoid deadlocks
• If frequent deadlock warnings, enableinnodb_print_all_deadlocksParameter to collect more extensive debugging information
• Be prepared to reissue the transaction if it fails due to a deadlock
• Keep transactions small and of short duration to reduce the likelihood that they will collide
• Commit transactions immediately after making a set of related changes to reduce the likelihood that they will conflict, and do not leave an interactive mysql session open for a long time with uncommitted transactions
• If you use lock reads, try to use a lower isolation level, such as RC
• When you modify multiple tables in a transaction or different sets of rows in the same table, these operations are performed in a consistent order each time
• Be careful when adding indexes so that queries scan fewer index records and set fewer locks
• Use less locking
• Serialize your transactions with table-level locks, which prevent concurrent updates to tables, thereby avoiding deadlocks at the cost of slower response times on busy systems

summary

This article mainly introduces the MySQL lock, such as interested in MySQL can continue to follow the MySQL column.