MySQL Lock summary

The role of locks: avoid simultaneous changes to the same data object when concurrent requests, resulting in inconsistent data. How to Lock: 1. Transaction T1 make a request to the system before R1 the operation on a data object and lock it L1.2. After that, the transaction T1 has control over the data object R1, and other transactions cannot be modified until T1 is released L1. Lock type: 1. Locks (X). 2. Shared Lock (S). The usual lock range: 1. Global lock. 2. Table lock. 3. Row lock. InnoDB Line Lock Range (granularity): 1.record lock.2.gap lock.3.next-key lock = record lock + gap lock. At the RC level, there is only record lock and no next key lock.  RR level, unless primary key or unique key is record lock, the rest is next key lock. Locking impact on the database: 1. Lock wait, lock L1 lock an object R1, lock L2 Wait for the lock to release, if not released, waits until the system preset timeout threshold is reached (rollback the entire transaction or roll back the current SQL only). 2. Deadlock, lock resource request generated loop, for example: L1 waiting for L2 release, L2 waiting for L3 release, L3 waiting for L1 release, Dead Loop. MyISAM LockDefault is table lock, read and write mutex, read-only share. Read lock, lock TABLE User Read, itself read only, cannot write, other threads are still readable and cannot be written. Multiple threads can submit read lock. Write lock, lock TABLE user write, itself can read and write, other threads are completely unreadable. The write lock priority is higher than the read lock. Select Auto-read lock (Shared lock). Other DML, DDL automatic write locks (exclusive locks). Release the lock, UNLOCK TABLES. Special case: Single-threaded to the last idle position of the MyISAM table serial write new data is not locked (and the hole in the middle is still locked). InnoDB LockThe default is row lock. The InnoDB is implemented by locking the index record and implementing the row lock. As a result, row locks cannot be implemented without an index, and an upgrade to a record lock on a table is equivalent to a table lock. Lock type: 1. Shared lock. 2. Exclusive lock. 3. Intent lock, InnoDB unique, load the lock on the table level. Other LocksGlobal Lock (1.global read Lock,2.query cache lock.) MDL table Lock. Self-increasing mutex (mutex), used to manage auto-incrementinnodb spin locks, spinlock. Global LockGlobal read lock locking: Ftwrl,flush TABLES with read lock. Closes all packages under the instance and adds a global read lock to prevent modification until the unlock TABLES is committed. Generally used for backup, mysqldump, Xtrabackup will be initiated.  InnoDB and MyISAM can be backed up separately, or--master-data will not be executed when xtrabackup. Query cache lock Global Query cache locks (mutexes), preferably close query cache. Query cache lock is raised when there is an update to the data in the QC. Status: watiting for query cache lock.

Close Query cache:

Query_cache_type=0 (set before instance start)

Query_cache_size=0.

MDL LockTable-level lock 5.5 within the Mdl,meta data lock transaction begins with the introduction of 5.6.6, when the transaction is turned on, the meta data lock of the table is locked and the other sessions have DDL operations on the table, waiting for the MDL to be released before continuing. 5.6.6 no longer blocks other sessions from executing the DDL, but when the original session accesses the datasheet again, there is an error prompt: Table definition has changed,please retry transaction timeout threshold definition: lock_wait _timeout. self-increasing lockis actually a lightweight mutex (mutex)-related option innodb_autoinc_lock_mode1, default value, can be used in the new method of pre-sentencing the number of rows, can not be pre-sentenced to still use the table lock, will cause the autoinc column self-increment hole, but the impact is very small. 0, that is, the old table-level lock mode, each request will wait for the table lock, but also very fast. Does not affect the entire transaction, affecting only the current INSERT statement. 2, directly all use new way, unsafe, not suitable for replication environment. InnoDB spin lock, Spin lockA locking mechanism, and a mutex type, which is proposed to protect shared resources, can only have one holder at any moment, controlling the CPU time slice allocation when the transaction is concurrent. Polling detection that is used to control InnoDB internal thread scheduling. Innodb_spin_wait_delay, control the polling interval by default of 6 seconds. Transaction concurrency is very high, the CPU is not busy, the transaction is in sleep state, spin round may also be very high. Show engine InnoDB Statusmutex spin waits 5970888,rounds 19812448,os wait 375285 shared lock of InnoDB lockShared locks, which do not allow other transactions to modify the locked rows, are read-only. SELECT ... LOCK in SHARE MODE. A select that is not in a transaction is a consistent, non-locked read without locking. exclusive lock of InnoDB lockWhen you DML a row of records, you need to add at least an exclusive lock. The lock range may be record lock, Next-key lock, or only gap lock, depending on the situation. Execute DML, or select ... For UPDATE. InnoDB Lock of Intent lockis, transaction t wants to get a shared lock on a few rows in the table. IX, the transaction T wants to get an exclusive lock on a few rows in the table. The intent lock is the root node of the B + tree structure that is loaded in the data table, which is the intent lock on the entire table. Intent lock action: avoids DDL operations on the table when DML is executed, resulting in inconsistent data. InnoDB Lock

	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

MySQL Lock summary