MySql – lock

1. What is a MySQL lock

A lock is a mechanism that computers use to coordinate access to shared resources between multiple processes. Lock ensures the consistency and validity of concurrent data access. Lock in MySQL is implemented at the server layer or storage engine layer

Ps. See Baidu for specific explanation

2. Look at the lock through the execution process of an Update statement. Where is the lock

General classification of MySQL locks

• Classification by particle size

  1. Global lock: lock the entire mysql instance. This is implemented by mysql’s SQL Layer
  2. Table level locking: lock a table (updates to different rows need to be performed sequentially, slow!)
  3. Row-level locking: lock specific rows, and possibly lock gaps between rows. Only InnoDB engine supports this. (Different rows in the same table can be changed in parallel to increase concurrency)

• Classification by function

  1.Exclusive Locks – X Locks:

  • Compatibility: Records with an X lock do not allow S and X locks to be added to other transactions
  • Locking mode:select ... for update

  2.Shared Locks -s (read Locks)

  • Compatibility: records with an S lock are allowed to add S locks to other transactions, and X locks to other transactions are not allowed
  • Locking mode:select ... lock in share mode

  3. MetaData Locks, Intention Locks and auto-Inc Locks are also included

Lock implementation and use

1. Global lock

On the whole database instance global lock lock, lock the whole strength is in a read-only state, after the subsequent MDL written statements, DDL statements and have updated commit statements will be blocked, the typical usage scenario is to do the whole database logical backup, to lock all of the tables, so as to obtain consistent view, ensure the integrity of the data

Global lock: flush tables with read lock;

Command to release the global lock: unlock tables;

Ps. Disconnect the session to automatically release the global lock

⚠️ Global locking is dangerous. If the primary database is locked, data cannot be written to the entire database, affecting service running during backup. If the secondary database is locked, data synchronization between the primary and secondary databases is delayed

If InnoDB is a transaction engine, you can use the –single-transaction parameter when using MySQL dump. MVCC is used to provide a consistent view instead of using a global lock. For tables that do not support transactions, such as MyISAM, the consistent view can only be obtained through a global lock with the parameter –lock-all-tables

2. The table level lock

• Shared lock (S lock): After a Session is locked, the table is in the read-only state, and all sessions can only read but not modify the table

  Lock table_name read;

  Unlock: unlock tables;

• Exclusive lock (X lock): After locking, the current session monopolizes resources. The locked session can read and write, while other sessions cannot read and write

  Lock: ‘lock table_name write;

  Unlock: unlock tables;

  MySQL implements table-level locking with a contention state variable:

  show status like 'table%'; -- table_LOCKs_IMMEDIATE: number of times table-level locks are generated. -- table_LOCKs_waited: number of times waits occur when table-level lock contention occursCopy the code

  Check table lock status:

  show open tables;

• Metadata lock: When a transaction is opened and a query is performed on a table, the data on the table is locked and the table structure is not allowed to be modified (to ensure that the read and write are correct).MDL does not need to be used explicitly

  In MySQL 5.5, MDL was introduced. When CRUD operations were performed on a table, MDL read locks were added, and when structural changes were performed on the table, MDL write locks were added

  • Read locks are not mutually exclusive, so multiple threads can CRUD a table at the same time
  • P: When two threads add fields to a table at the same time, one thread must wait until the other thread finishes adding fields to the table

  session1: begin; Select * from mylock; Session2: ALTER table mylock add f int; -- Modify blocking session1: commit; -- Commit transaction session2: Query OK; 0 rows affected(10.23sec) -- Modify completed promptCopy the code

• Auto-inc Locks: Auto-inc Locks are special table-level Locks that occur when you design transactional inserts for an AUTO_INCREMENT column

MySQL row level lock – InnoDB

InnoDB row locks are implemented by locking index entries, so InnoDB’s row lock implementation features mean: InnoDB will only use row-level locks if the data is retrieved by index criteria. Otherwise,InnoDB will use table locks. For UPDATE, DELETE, and INSERT statements,InnoDB will automatically assign exclusive locks (X locks) to the data set involved The table lock cost is large, the locking is slow, the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest

View row level lock contention status:

show status like 'innodb_row_lock%'; -- Innodb_row_lock_current_waits The number of locks currently waiting -- Innodb_row_lock_time; Total lock time since system boot -- Innodb_row_lock_time_avg; Average wait time -- Innodb_row_lock_time_max; Max wait time since system boot -- Innodb_row_lock_wait; Total number of times the system has waited since bootCopy the code

SQL select * from information_schema.innodb_locks; select * from information_schema.innodb_lock_waits; select * from information_schema.innodb.trx;Copy the code

InnoDB row locks are classified by lock range:

• Record Locks: Locks a Record in the index where ID = 1 for the primary key instruction
• Gap Locks: Locks either values in the middle of an index record, before the first index record, or after the last index record
• Next-key Locks: a combination of a record lock on an index record and a gap lock that precedes the index record (gap + record lock)
• Insert intentional Locks: Locks for record ids added during an Inert operation

My Intention is Locks

Introduction:

The intentional lock is a table-level lock that is used internally by MySQL and does not require user intervention. The intentional lock and row lock can coexist. The main function of the intentional lock is to improve the performance of the full table update data

1. Indicates – a transaction is holding a lock on some rows, or the transaction is about to hold a lock

2. Intentional locks exist to coordinate the relationship between row locks and table locks, and to support the coexistence of multiple granularity locks (table locks and row locks)

3. Intent locks include:

   • Intended shared lock (IS lock): A transaction intends to impose a shared lock on certain behaviors in a table. That IS, the transaction must acquire the IS lock before requesting the S lock
   • Intent Exclusive lock (IX lock):… A transaction acquires an IX lock before requesting an X lock

Function:

When we need to add an exclusive lock, we need to determine whether the rows in the table are locked based on the intention lock

1. If an intention lock is a row lock, it needs to facilitate the validation of each row of data
2. If an intentional lock is a table lock, you only need to determine once whether a row is locked to improve performance

The compatibility relationship between the intention lock and shared lock and exclusive lock

Intent locks are compatible with each other because IX and IS only indicate X and S operations that apply to lower-level elements (page, record). After a row level X lock IS applied, the row level X lock will not be blocked if another transaction IS applied to IX. A MySQL database allows multiple row level X locks to exist at the same time, as long as they are not applied to the same row

2. Record Locks

InnoDB will create a hidden aggregation index in the background, and the record lock will lock the index, even if there is no index on the table

An X lock is added to each aggregate index when an SQL entry does not go through any indexes. This is similar to table locking, but the principle is completely different from table locking

select * from table_name where id = 1 lock in share mode; Select * from table_name where id = 1 for update; -- Add record exclusive lockCopy the code

3. Gap Locks

• Interval lock, which locks only one index interval (open interval, not including double-ended endpoints)
• Locking between index records or before or after an index record does not include the index record itself
• Gap locks are used to prevent phantom reads and ensure that no data is inserted between indexes

-- session1: begin; select * from table_name where id > 3 for update; -- session2: insert into table_name values (4, 20); Insert into table_name values (2, 20); Success -Copy the code

Um participant: as the primary key index row lock is 1, 3, 6, 12, clearance for (- up, 1), (1, 3), (3, 6), (6, 12), the key to (- up, 1), (1, 3), (3, 6], (6, 12]

4. Next-key Locks

• Record Locks + Gap Locks, open left and close right
• By default,InnoDB uses next-key Locks to lock records – select.. for update;
• When the query index has unique properties, next-key Locks optimizes and degrades it to Record Locks, locking only the index itself, not the range

Insert Intention Locks

• An insert intent lock is a Gap lock, not an intent lock, that occurs during an INSERT operation
• When multiple transactions write different data to the uniform index gap at the same time, there is no need to wait for other transactions to complete, and no lock wait occurs
• Suppose you have a record index with keys 1 and 4, and different transactions insert keys 2 and 3, respectively. Each transaction inserts an intent lock between 1 and 4, acquiring exclusive locks on the inserted rows, but does not lock each other because rows do not conflict
• An insert intention lock does not block any locks and holds a record lock for an inserted record

6. Row lock and lock rule