mysql lock

foreword

MySQL is divided into three types according to the scope of the lock

• Table-level lock: low overhead and fast locking; no deadlock, large locking granularity, highest probability of lock conflict, and lowest concurrency.
• Row-level locks: High overhead, slow locking, deadlocks, small locking granularity, lowest probability of lock conflicts, and highest concurrency.

• Page locks: The overhead and locking time are between table locks and row locks, and deadlocks will occur. The locking granularity is between table locks and row locks, and the degree of concurrency is average.
  Judging from the characteristics of the above three kinds of locks, it is difficult to say which kind of lock is better, but which kind of lock is more suitable according to the characteristics of the specific application. For example, MyISAM and MEMORY engines use table-level locks; the InnoDB engine supports both row-level locks and table-level locks, but row-level locks are used by default.

  InnoDB lock mode

  InnoDB implements the following two types of row locks.

• Shared lock (S): An exclusive lock that allows a transaction to read a row and prevents other transactions from acquiring the same data, also called a read lock.
• Exclusive lock (X): Allows the transaction that obtains the exclusive lock to update data, and prevents other transactions from obtaining shared locks and exclusive locks on the same data set, also known as write locks.

At the same time, mysql also supports table shared locks and table exclusive locks similar to row shared locks and row exclusive locks. Because the granularity of locks is different, the scope of table locks covers the scope of row locks, so table locks and row locks will conflict, such as transactions. A adds a row lock to a row of data in the table, and then transaction B wants to add a table lock. Normally, there should be a conflict. To determine whether there is a conflict, it is necessary to traverse each row of data, which is not efficient, so we have an intention table lock.

The main purpose of the intention lock is to make the row lock and the table lock coexist. Before applying for a row lock, a transaction must first apply for the intention lock of the table, and then apply for the row lock after success.

Intentional locks are divided into intentional shared locks and intentional exclusive locks.

• Intention shared lock (IS): The transaction intends to add a row shared lock to a data row. The transaction must first go to the intention shared lock of the table before adding a shared lock to a data row.
• Intentional exclusive lock (IX): The transaction intends to add a row exclusive lock to a data row. The transaction must first obtain the intentional exclusive lock of the table before adding an exclusive lock to a data row.

The compatibility of the above lock modes is shown in the following table

The right side represents the requested lock mode, and the lower side represents the current lock mode	X	IX	S	IS
X	conflict	conflict	conflict	conflict
IX	conflict	compatible	conflict	compatible
S	conflict	conflict	compatible	compatible
IS	conflict	compatible	compatible	compatible

If the lock mode requested by a transaction is compatible with the current lock mode, InnoDB grants the requested lock to the transaction, otherwise, if the two are not compatible, the transaction waits for the lock to be released.

The intent lock is a table-level lock, but it means that the transaction is reading or writing a row of records, not the entire table, so there will be no conflict between the intent locks, and the real conflict is checked when adding row locks.

lock method

Intent locks are automatically added by InnoDB and do not require user intervention.

Implicitly locked

• For UPDATE, DELETE and INSERT statements, InnoDB will automatically add an exclusive lock to the design dataset;
• For ordinary SELETE statements, INNODB will not add any locks;

• InnoDB will automatically lock when needed according to the isolation level;

  Explicitly locked

  select * from tableName lock in share mode;//读锁
  select * from tableName for update;//写锁

  unlock

• Commit the transaction (commit)
• Rollback the transaction (rollback)

• kill blocking process
  Read lock instance

  Transaction A	Transaction B
  begin;
  select * from teacher where id = 2 lock in share mode;// read lock
  	select * from teacher where id = 2;// can be read normally
  update teacher set name = 3 where id =2;// can update operation	update teacher set name = 5 where id =2;// blocked
  commit;
  	update teacher set name = 5 where id =2;// The update operation is successful

  Write lock example

  Transaction A	Transaction B
  begin;
  select * from teacher where id = 2 for update;// write lock
  	select * from teacher where id = 2;// can be read normally
  update teacher set name = 3 where id =2;// can update operation	update teacher set name = 5 where id =2;// blocked
  rollback;
  	update teacher set name = 5 where id =2;// The update operation is successful

  Why is the write lock on, other transactions can still read operations?
  Because InnoDB has the MVCC mechanism (multi-version concurrency control), snapshot reads can be used without being blocked.

  InnoDB row lock implementation

  Record Lock

  Row locks always lock index records. If no index is set when the InnoDB storage engine table is created, the InnoDB storage engine will use an implicit clustered index for locking.

  Gap Lock

  When we retrieve data with range conditions instead of equality conditions, and request a shared or exclusive lock, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the condition range but do not exist, It is called "GAP", and InnoDB will also lock this "gap". This locking mechanism is the so-called gap lock (Next-Key lock).
  Advantages: Solve the phantom read problem of transaction concurrency
  Disadvantage: Because the query is searched through the range during the execution process, it will lock all the index key values in the range, even if the key value does not exist.
  Gap lock has a fatal weakness, that is, after locking a range of key values, even some non-existing key values will be locked innocently, and any data in the locked key value range cannot be inserted when locked. In some scenarios this can be a huge performance hit.

  Next-key Lock

  Simultaneously lock data + gap lock
  Under Repeatable Read isolation level, Next-key Lock is the default row record locking algorithm.
  If there are only 101 records in the teacher table, and their id values are 1-101, the SQL statement is as follows

  Select * from teacher where id  〉 100 for update；

  This is a range conditional retrieval. InnoDB not only locks the records with the id value of 101 that meet the conditions, but also locks the "gap" with the id greater than 101 (non-existing records).

  Optimistic locking and pessimistic locking

• Optimistic Lock: Assuming that no concurrency conflicts will occur, only checks for data integrity violations when the operation is committed. Optimistic locking does not solve the problem of dirty reads.
  Optimistic locking, as the name suggests, is very optimistic. Every time I go to get data, I think that others will not modify it, so it will not be locked. However, when updating, it will judge whether others have updated the data during this period. You can use version number and other mechanisms. Optimistic locks are suitable for multi-read application types, which can improve throughput. For example, if the database provides a mechanism similar to write_condition, it is actually an optimistic lock.

• Pessimistic Lock: Assuming that concurrency conflicts will occur, all operations that may violate data integrity are blocked.
  Pessimistic lock is very pessimistic. Every time you go to get data, you think that others will modify it, so every time you get data, it will be locked, so that others who want to get this data will be blocked until it gets the lock. Many such locking mechanisms are used in traditional relational databases, such as row locks, table locks, etc., which are locked before operations are performed.

  Summarize

Locks and Multi-Version Data (MVCC) are InnoDB's means of achieving consistent reads and four isolation levels.

Therefore, under different isolation levels, InnoDB requires different locks when processing SQL.