Mysql lock overview

Database locks: When dealing with concurrency problems, databases need to control the access rules of resources, and locks are important data structures used to implement these access rules. Scope of locking: global locking, table locking, row locking.

Global lock

Flush tables with read Lock (FTWRL), unlock tables is unlocked. The entire database is read-only, and other threads cannot add, delete, define (create tables, modify structures), update transactions,

Scenario: Full library logical backup.

Disadvantages: During the backup of the master database, updates cannot be executed and services are stopped. Secondary database backup. During the backup, the binlog from the primary database cannot be synchronized, resulting in the master/slave delay.

However, not locking results in inconsistent data

Repeatable read level Start the next transaction to get the consistency view, but MyISAM engine does not support, InnoDB supports.

Set Global ReadOnly =true, the entire library is read-only, but this is not recommended. One is that in some systems, the readonly value is used to do other logic, such as determining whether the master or the slave, so how you modify the global variable is very influential. However, the exception handling mechanism is different. After FTWRL is executed, Mysql will automatically release the global lock after the client is disconnected abnormally, and the whole library can return to the normal update state. The readOnly is abnormal and the database remains unchanged, which will make the database in the unwritable state for a long time, with high risk.

After global lock is added, not only DML cannot be added, but DDL can also be changed

Table level lock

Meta Data lock meta data lock meta data lock meta data lock… Read /write. Unlock tables is used to release the lock actively and automatically when the client is disconnected. In addition to limiting reads and writes to other threads, Lock tables also limits what the thread can operate on next.

Lock tables T1 read, T2 write: Prevents other threads from writing T1 and writing T2 (blocks this statement). This thread can only read T1 and write T2 before executing unlock tables.

MDL locks do not need to be used explicitly, but are automatically added when accessing a table to ensure correct read and write. For example, if you iterate over a table, another thread makes changes to the table in the meantime, deleting a column, changing the structure, and causing an error.

MDL was introduced after MySql5.5 to add MDL read locks to tables CRUD and MDL write locks to table structure changes. Read lock All threads can normally read metadata, does not affect CRUD, cannot modify table structure, that is, DML is allowed, block DDL. Add MDL write lock, block other threads DML and DDL.

If there are four threads in sequence, the first thread reads the lock, and the second thread also reads the lock, it will not be affected, but the third thread changes the table structure and is blocked, and the subsequent request for MDL read lock will be blocked by the third thread. If the table is frequently queried, and the client has a retry mechanism, the library threads will fill up.

Therefore, to resolve a long transaction, the transaction is not committed and will occupy the MDL lock. If the DDL changes to a table that happens to have a long transaction, either suspend the DDL or kill the transaction. However, if the transaction is frequent, kill may not work, because the new will come soon, you can set the wait time, in the specified time to obtain the MDL write lock, do not block the following statement, you can first abandon, repeat the command.