Revisiting the MySQL lock mechanism

Article directory

• Lock
• • Lock classification
  • Shared lock (read lock, read and write mutually exclusive, reading and reading do not affect each other)
  • Exclusive lock (write lock, exclusive lock)
  • The difference between shared lock and exclusive lock
• Table lock
• ##Intention lock: intention shared lock and intention exclusive lock
  • Why you need to add intention lock
  Single table lock
  • Global table lock
Row lock
• Row lock is upgraded to table lock (row The lock actually locks the index. If the entire table is scanned without using the index, the entire table will be locked)
• When a certain row record is modified or deleted, and the transaction is not submitted, the row will always be locked. Lock lock
• Record lock
• Gap Locks
• The role of gap lock: prevent phantom reading
  • innodb automatic Conditions for using gap lock
  • Area locked by gap lock
  • next-key lock【Key lock】
• Record lock, gap The difference between locks and temporary locks

Related free learning recommendations: mysql video tutorial

Lock

Lock is a mechanism for computers to coordinate multiple threads to access the same shared resource. It is mainly used to solve the concurrency security problem of multiple threads accessing the same shared resource.

Lock classification

(1) From the perspective of performance, it is divided into: optimistic lock and pessimistic lock

MySQL adopts version number control, JVM CAS

is used in (2) The database operation type is divided into: read lock (shared lock), write lock (exclusive lock) [Read lock and write lock are both pessimistic locks]

Read lock (shared lock): For the same row record, multiple read operations can be performed at the same time, but no transaction can be performed
• Write lock (exclusive lock): Transactions that obtain exclusive locks can both read data , and can modify the data. Until the transaction that acquires the write lock is completed, it will block other transactions from acquiring write locks or read locks.

(3) From the granularity level, it is divided into table locks and row locks

Note: (1) Read lock, write lock The locks are all row-level locks, that is, transaction 1 acquires the write lock on product A, and transaction 2 acquires the write lock on product B and will not block each other.
(2) If the SQL statement uses row locks, when SQL does not use indexes but uses full table scans, row-level locks will become table locks.

(3) The lock will only be released when commit or rollback is executed, and all locks will be released at the same time.

(4) When the Innodb engine executes ordinary modification and deletion sql statements, it will add row locks to the modified and deleted rows.

Shared lock (read lock, read and write mutual exclusion, reading and reading do not affect each other)

Transaction A used a shared lock to obtain a certain (or certain) record When transaction B can read these records, it can continue to add shared locks, but it cannot modify these records (when transaction C modifies or deletes these data, it will enter a blocking state until the lock wait times out or transaction A commits)

How to use shared locks and release shared locks

# 加锁SELECT ... LOCK IN SHARE MODE# 释放锁commit;rollback;

• Function

  SELECT ... LOCK IN SHARE MODE adds shared locks to several rows of records read (share lock), other transactions can only query these rows but cannot modify these records. Multiple transactions can add shared locks to the same row record, so the transaction that acquires the shared lock may not necessarily be able to modify the row data
  

• Usage scenarios: Reading results The latest version of the set, while preventing other transactions from updating the result set For example: concurrent operations on product inventory
  

Exclusive lock (write lock, exclusive lock)

select ... for update adds an exclusive lock to the read row record, and only allows the transaction that obtains the exclusive lock to modify the row record, preventing other transactions from modifying the row, just like ordinary The update statement will add row locks when executed.

How to use exclusive locks

# 加排他锁select ... for update //排他锁 # 释放锁commit;rollback;

The difference between shared locks and exclusive locks

(1) Once a transaction acquires Once the exclusive lock is acquired, other transactions can no longer acquire the exclusive lock.

Multiple transactions can add shared locks to the same row of data.
(2) A transaction that adds a shared lock to a specified row may not necessarily modify the row data, because other transactions may also add a shared lock or exclusive lock to the row; a transaction that adds an exclusive lock to the specified row, Then you can modify the row data with exclusive lock

Table lock

Table-level locking is mainly used by some non-transactional storage engines such as MyISAM, MEMORY, and CSV. .

Table locks are generally used during data migration.

Intention lock: intention shared lock and intention exclusive lock

The prerequisite for adding a shared lock to a row is that the table where the row's data is located will first obtain the intention shared lock.

The prerequisite for adding an exclusive lock to a row is that the table where the row's data is located will first obtain the intended exclusive lock.

Note: Intention shared locks and intention exclusive locks are table locks and cannot be created manually.

Why do you need to add intention lock

意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。

单个表锁定

lock tables saas.goods read,saas.account write;　　// 给saas库中的goods表加读锁,account表加写锁unlock tables;   //解锁

全局表锁定

FLUSH TABLES WITH READ LOCK; 　　// 所有库所有表都被锁定只读unlock tables;         //解锁

注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

行锁

• 行锁是锁一行或者多行记录
• MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
  
  如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
  

行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）

上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。

修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定

窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。

记录锁

• 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
• 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）。

间隙锁（Gap Locks）

经典参考文章

间隙锁的作用：防止幻读

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止止间隙内有新数据被插入
（2）防止范围内已存在的数据被更新

innodb自动使用间隙锁的条件

（1）数据隔离级别必须为可重复读
（2）检索条件必须使用索引（没有使用索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。下图中，where number=5的话，那么间隙锁的区间范围为[4,11]；

session 1:start  transaction ;触发间隙锁的方式1：select  * from news where number=4 for update ;触发间隙锁的方式2：update news set number=3 where number=4; session 2:start  transaction ;insert into news value(2,4);#（阻塞）insert into news value(2,2);#（阻塞）insert into news value(4,4);#（阻塞）insert into news value(4,5);#（阻塞）insert into news value(7,5);#（执行成功）insert into news value(9,5);#（执行成功）insert into news value(11,5);#（执行成功）

next-key锁【临键锁】

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。InnoDB默认加锁方式是next-key 锁。

select * from news where number=4 for update ;

next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。

记录锁、间隙锁、临间锁的区别

update news  set number=0 where id>15

sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of Revisiting the MySQL lock mechanism. For more information, please follow other related articles on the PHP Chinese website!