MySQL5.7 锁详解

作者: 南风nanfeng | 来源:发表于2018-10-17 09:39 被阅读160次

1.锁特定比较

锁/存储引擎	MyISAM	InnoDB	特点
行锁	×	√	加锁快，开销小；不会死锁；锁颗粒度大，锁阻塞概率高，并发小。
表锁	√	√	加锁慢，开销大；可能死锁；锁颗粒度小，锁阻塞概率小，并发高。

2.MyISAM表锁机制

表共享读锁（Table Read Lock），MyISAM读锁不会阻塞同一表的读请求，但会阻塞对同一表的写请求。
表独占写锁（Table Write Lock），MyISAM写锁则会阻塞同一表的读写请求，所以，读、写请求是串行的。

3.InnoDB的行锁机制，参考：官方文档

3.1 InnoDB实现了以下两种类型的行锁。

共享锁（S）：即读锁，允许一个事务去读同一行，阻止其他事务获得相同数据集的排他锁。SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：即写锁，允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。SELECT * FROM table_name WHERE ... FOR UPDATE。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

InnoDB锁的互斥与兼容关系

锁类型	排他锁（X）	共享锁（S）	意向排他锁（IX）	意向共享锁（IS）
排他锁（X）	×	×	×	×
共享锁（S）	×	√	×	√
意向排他锁（IX）	×	×	√	√
意向共享锁（IS）	×	√	√	√

3.2 InnoDB锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

笔者测试发现，使用MySQL5.6和5.7版本的服务器，即使update不用索引，MySQL使用也是行锁。

0.修改锁超时时间为1小时，默认为50秒
> show global variables like 'innodb_lock_wait_timeout';
> set global innodb_lock_wait_timeout=3600;

1.挂起一个事务
> START TRANSACTION;
> UPDATE tbl_order set  Prdclass = '11111' WHERE imei = '863396023884685';

2. 对同一条记录发请求
 UPDATE tbl_order set  Prdclass = '22222' WHERE imei = '863396023884685';

3.show processlist
mysql> show processlist;
+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+
| Id | User        | Host               | db        | Command | Time | State                                                  | Info                                                                   |
+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+                                            |
| 16 | root        | 10.206.16.88:61968 | latest_db | Query   |   30 | updating                                               | UPDATE tbl_order set  Prdclass = '3333' WHERE imei = '863396023884685' |
+----+-------------+--------------------+-----------+---------+------+--------------------------------------------------------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

发现有第二条更新语句已经挂起了。

4. 查询innodb锁
mysql> SELECT * FROM information_schema.INNODB_LOCKS;
+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| lock_id             | lock_trx_id | lock_mode | lock_type | lock_table              | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| 405729757:1226:4:88 | 405729757   | X         | RECORD    | `latest_db`.`tbl_order` | PRIMARY    |       1226 |         4 |       88 | 19        |
| 405729754:1226:4:88 | 405729754   | X         | RECORD    | `latest_db`.`tbl_order` | PRIMARY    |       1226 |         4 |       88 | 19        |
+---------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

发现此时的两条记录的锁类型均为RECORD，即基于记录的。锁模式：排他锁（X）

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+---------------------+-----------------+---------------------+
| requesting_trx_id | requested_lock_id   | blocking_trx_id | blocking_lock_id    |
+-------------------+---------------------+-----------------+---------------------+
| 405729757         | 405729757:1226:4:88 | 405729754       | 405729754:1226:4:88 |
+-------------------+---------------------+-----------------+---------------------+
1 row in set, 1 warning (0.00 sec)
上表展示了锁的阻塞管理，事务405729754 阻塞了事物405729757的操作。

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 405729757
                 trx_state: LOCK WAIT
               trx_started: 2018-10-16 11:55:28
     trx_requested_lock_id: 405729757:1226:4:88
          trx_wait_started: 2018-10-16 11:55:28
                trx_weight: 2
       trx_mysql_thread_id: 16
                 trx_query: UPDATE tbl_order set  Prdclass = '3333' WHERE imei = '863396023884685'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 405729754
                 trx_state: RUNNING
               trx_started: 2018-10-16 11:55:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 86912
       trx_mysql_thread_id: 13
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 86911
     trx_lock_memory_bytes: 8429776
           trx_rows_locked: 6552922
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)

ERROR: 
No query specified

>commit

对此，官方给出解释如下：

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 14.8.2.1, “Clustered and Secondary Indexes”.
也就是说即使没有索引，innodb引擎也会选择基于索引的行锁，原因在于，innodb会默认创建clustered index，即聚簇索引。其创建规则如下：

如果表中有主键，则使用主键作为聚簇索引。
如果表中没有主键，则选择最前面一个唯一性索引作为聚簇索引。
如果即没有主键也没有唯一性索引，InnoDB会用rowId创建一列隐藏的列，名称叫做“GEN_CLUST_INDEX”，作为聚簇索引使用。

聚簇索引之所以查询快？
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时，他的数据行实际上存放在索引的叶子页（leaf page）中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起（这并非总成立）。

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

3.3 InnoDB间隙（Gap）锁、Next-key锁（行锁和间隙锁叠加）

使用范围更新时，如: between 10 and 20，则10~20范围内的数据都被上锁，如果此时插入15的数据，则会阻塞。
在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的

3.3 InnoDB表锁（意向锁）

笔者尝试官方文档中的案例，发现有出入，Intention Locks，结果还是行锁Record，这点费解。

3.4 Auto-Inc表锁

Auto-Inc是一种表锁，用在控制并发事务的场景中自增列的表锁。
在MySQL 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。
在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。如：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。这种方式对于可预判插入行数的插入语句有效，如：insert和replace。
对于无法提前获知插入行数的语句，如：insert...select...、replace...select...和load data则innodb还是使用表锁。

4. 死锁

MyISAM表锁不会发生死锁，因为一次能获取全部的表锁，具有排他性，要么全部满足，要么等待，因此不会死锁。InnoDB的行锁复杂程度高于MyISAM，除单个SQL组成的事务外，锁是逐步获取的，这就可能导致死锁。
笔者测试发现，发生死锁后，InnoDB一般能够检测出来，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

如果出现死锁，可以用SHOW ENGINE INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

笔者，尝试多种方法产生表锁，均未成功，比如表product，有model，name属性，分别创建索引，还有code属性，也创建索引，操作如下：

session 1> start transaction;
session 1> update product set price=1 where name='test' and model='N8';

session 2> start transaction;
session 2> update product set price=2 where code='Mj12321';

实际上都是更新同一行数据，发现session1获得行锁，session2阻塞，进入等待队列。

后续该表条件，选中一个范围获取Gap，又更新范围内的数据，同样获得行锁。又使用like避开索引，还是行锁。扩大update范围，如更新上百万行数据，均是使用行锁，未涉及表锁。

最后使用alter改表语句删除一列，发现问题所在，原来是不管表锁还是行锁，锁查询的lock_type都为RECORD
> SELECT * FROM information_schema.INNODB_LOCKS;
根据笔者的观察，lock_type有两个取值，TABLE和RECORD，而实际用到表锁是也是RECORD，这点确实奇怪，下面换一个查询方法，便豁然开朗：
> show OPEN TABLES where `Database` = 'latest_db' ;
该查询记录表锁的打开情况。

总结行锁便表锁的情况
1、表字段进行变更。
2、进行整表查询。(没使用索引)
3、like语句查询的时候。(没使用索引)

锁、事务相关命令：

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_TRX;

SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;

show OPEN TABLES where `Database` = 'latest_db' ;
show status like 'Table%';

5.聚簇索引概念

myisam的主索引和次索引都指向物理行，下面来进行讲解
innodb的主键下存储该行的数据，此索引指向对主键的引用

myisam的索引存储图如下，可以看出，无论是id还是cat_id，下面都存储有执行物理地址的值。通过主键索引或者次索引来查询数据的时候，都是先查找到物理位置，然后再到物理位置上去寻找数据。

20151004223450594.jpg

innodb的索引存储图如下，我们会发现，主键索引下面直接存储有数据，而次索引下，存储的是主键的id。通过主键查找数据的时候，就会很快查找到数据，但是通过次索引查找数据的时候，需要先查找到对应的主键id，然后才能查找到对应的数据。

20151004223815368.jpg

nnodb的主索引文件上直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,
1: 主键索引既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

参考：
https://www.jianshu.com/p/0d5b7cd592f9
https://blog.csdn.net/qq_25551295/article/details/48901317?utm_source=copy
本文参考官网及其他作者，欢迎转载！

MySQL5.7 锁详解

1.锁特定比较

2.MyISAM表锁机制

3.InnoDB的行锁机制，参考：官方文档

3.1 InnoDB实现了以下两种类型的行锁。

3.2 InnoDB锁实现方式

3.3 InnoDB间隙（Gap）锁、Next-key锁（行锁和间隙锁叠加）

3.3 InnoDB表锁（意向锁）

3.4 Auto-Inc表锁

4. 死锁

5.聚簇索引概念

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL5.7 锁详解

1.锁特定比较

2.MyISAM表锁机制

3.InnoDB的行锁机制，参考： 官方文档

3.1 InnoDB实现了以下两种类型的行锁。

3.2 InnoDB锁实现方式

3.3 InnoDB间隙（Gap）锁、Next-key锁（行锁和间隙锁叠加）

3.3 InnoDB表锁（意向锁）

3.4 Auto-Inc表锁

4. 死锁

5.聚簇索引概念

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

3.InnoDB的行锁机制，参考：官方文档