基于更新SQL语句理解MySQL锁定详解-华尔子博-奥夏网

前言

MySQL数据库锁是实现数据一致性，解决并发问题的重要手段。数据库是一个多用户共享的资源，当出现并发的时候，就会导致出现各种各样奇怪的问题，就像程序代码一样，出现多线程并发的时候，如果不做特殊控制的话，就会出现意外的事情，比如“脏“数据、修改丢失等问题。所以数据库并发需要使用事务来控制，事务并发问题需要数据库锁来控制，所以数据库锁是跟并发控制和事务联系在一起的。

本文主要描述基于更新SQL语句来理解MySQL锁定。下面话不多说了，来一起看看详细的介绍吧

一、构造环境

  (root@localhost) [user]> show variables like 'version';  +---------------+------------+  | Variable_name | Value |  +---------------+------------+  | version | 5.7.23-log |  +---------------+------------+    (root@localhost) [user]> desc t1;  +-------------+--------------+------+-----+---------+----------------+  | Field | Type | Null | Key | Default | Extra |  +-------------+--------------+------+-----+---------+----------------+  | id | int(11) | NO | PRI | NULL | auto_increment |  | n | int(11) | YES | | NULL | |  | table_name | varchar(64) | YES | | NULL | |  | column_name | varchar(64) | YES | | NULL | |  | pad | varchar(100) | YES | | NULL | |  +-------------+--------------+------+-----+---------+----------------+    (root@localhost) [user]> select count(*) from t1;  +----------+  | count(*) |  +----------+  | 3406 |  +----------+    (root@localhost) [user]> create unique index idx_t1_pad on t1(pad);  Query OK, 0 rows affected (0.35 sec)  Records: 0 Duplicates: 0 Warnings: 0    (root@localhost) [user]> create index idx_t1_n on t1(n);  Query OK, 0 rows affected (0.03 sec)  Records: 0 Duplicates: 0 Warnings: 0  (root@localhost) [user]> show index from t1;  +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |  +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+  | t1 | 0 | PRIMARY | 1 | id | A | 3462 | | BTREE |  | t1 | 0 | idx_t1_pad | 1 | pad | A | 3406 | YES | BTREE |  | t1 | 1 | idx_t1_n | 1 | n | A | 12 | YES | BTREE |  +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+  select 'Leshami' author,'http://blog.csdn.net/leshami' Blog;  +---------+------------------------------+  | author | Blog |  +---------+------------------------------+  | Leshami | http://blog.csdn.net/leshami |  +---------+------------------------------+

二、基于主键更新

  (root@localhost) [user]> start transaction;  Query OK, 0 rows affected (0.00 sec)    (root@localhost) [user]> update t1 set table_name='t1' where id=1299;  Query OK, 1 row affected (0.00 sec)  Rows matched: 1 Changed: 1 Warnings: 0    SELECT trx_id,   trx_state,   trx_started,   trx_mysql_thread_id,   trx_tables_locked,   trx_rows_locked,   trx_rows_modified,   trx_isolation_level  FROM INFORMATION_SCHEMA.INNODB_TRX G    -- 从下面的结果可知，trx_rows_locked，一行被锁定   *************************** 1. row ***************************   trx_id: 6349647   trx_state: RUNNING   trx_started: 2018-11-06 16:54:12  trx_mysql_thread_id: 2   trx_tables_locked: 1   trx_rows_locked: 1   trx_rows_modified: 1  trx_isolation_level: REPEATABLE READ     (root@localhost) [user]> rollback;  Query OK, 0 rows affected (0.01 sec)

三、基于二级唯一索引

  (root@localhost) [user]> start transaction;  Query OK, 0 rows affected (0.00 sec)    (root@localhost) [user]> update t1 set table_name='t2' where pad='4f39e2a03df3ab94b9f6a48c4aecdc0b';  Query OK, 1 row affected (0.00 sec)  Rows matched: 1 Changed: 1 Warnings: 0    SELECT trx_id,   trx_state,   trx_started,   trx_mysql_thread_id,   trx_tables_locked,   trx_rows_locked,   trx_rows_modified,   trx_isolation_level  FROM INFORMATION_SCHEMA.INNODB_TRX G    -- 从下面的查询结果可知，trx_rows_locked，2行被锁定  *************************** 1. row ***************************   trx_id: 6349649   trx_state: RUNNING   trx_started: 2018-11-06 16:55:22  trx_mysql_thread_id: 2   trx_tables_locked: 1   trx_rows_locked: 2   trx_rows_modified: 1  trx_isolation_level: REPEATABLE READ     (root@localhost) [user]> rollback;  Query OK, 0 rows affected (0.00 sec)

三、基于二级非唯一索引

  (root@localhost) [user]> start transaction;  Query OK, 0 rows affected (0.00 sec)    (root@localhost) [user]> update t1 set table_name='t3' where n=8;  Query OK, 350 rows affected (0.01 sec)  Rows matched: 351 Changed: 351 Warnings: 0    SELECT trx_id,   trx_state,   trx_started,   trx_mysql_thread_id,   trx_tables_locked,   trx_rows_locked,   trx_rows_modified,   trx_isolation_level  FROM INFORMATION_SCHEMA.INNODB_TRX G     --从下面的查询结果可知，703行被锁定  *************************** 1. row ***************************    trx_id: 6349672    trx_state: RUNNING   trx_started: 2018-11-06 17:06:53  trx_mysql_thread_id: 2   trx_tables_locked: 1   trx_rows_locked: 703   trx_rows_modified: 351  trx_isolation_level: REPEATABLE READ    (root@localhost) [user]> rollback;  Query OK, 0 rows affected (0.00 sec)

四、无索引更新

  (root@localhost) [user]> start transaction;  Query OK, 0 rows affected (0.00 sec)    (root@localhost) [user]> update t1 set table_name='t4' where column_name='id';  Query OK, 26 rows affected (0.00 sec)  Rows matched: 26 Changed: 26 Warnings: 0    SELECT trx_id,   trx_state,   trx_started,   trx_mysql_thread_id,   trx_tables_locked,   trx_rows_locked,   trx_rows_modified,   trx_isolation_level  FROM INFORMATION_SCHEMA.INNODB_TRX G    -- 从下面的查询结果可知，trx_rows_locked，3429行被锁定，而被更新的仅仅为26行  -- 而且这个结果超出了表上的总行数3406  *************************** 1. row ***************************    trx_id: 6349674    trx_state: RUNNING   trx_started: 2018-11-06 17:09:41  trx_mysql_thread_id: 2   trx_tables_locked: 1   trx_rows_locked: 3429   trx_rows_modified: 26  trx_isolation_level: REPEATABLE READ    (root@localhost) [user]> rollback;  Query OK, 0 rows affected (0.00 sec)    -- 也可以通过show engine innodb status进行观察    show engine innodb statusG    ------------  TRANSACTIONS  ------------  Trx id counter 6349584  Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle  History list length 0  LIST OF TRANSACTIONS FOR EACH SESSION:  ---TRANSACTION 421943222819552, not started  0 lock struct(s), heap size 1136, 0 row lock(s)  ---TRANSACTION 6349583, ACTIVE 2 sec  2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1      ------------  TRANSACTIONS  ------------  Trx id counter 6349586  Purge done for trx's n:o < 6349585 undo n:o < 0 state: running but idle  History list length 1  LIST OF TRANSACTIONS FOR EACH SESSION:  ---TRANSACTION 421943222819552, not started  0 lock struct(s), heap size 1136, 0 row lock(s)  ---TRANSACTION 6349585, ACTIVE 8 sec  3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1  MySQL thread id 2, OS thread handle 140467640694528, query id 29 localhost root

五、锁相关查询SQL

1：查看当前的事务

  SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;    SELECT trx_id,   trx_state,   trx_started,   trx_mysql_thread_id thr_id,   trx_tables_locked tb_lck,   trx_rows_locked rows_lck,   trx_rows_modified row_mfy,   trx_isolation_level is_lvl  FROM INFORMATION_SCHEMA.INNODB_TRX;    SELECT r.`trx_id` waiting_trx_id,   r.`trx_mysql_thread_id` waiting_thread,   r.`trx_query` waiting_query,   b.`trx_id` bolcking_trx_id,   b.`trx_mysql_thread_id` blocking_thread,   b.`trx_query` block_query  FROM information_schema.`INNODB_LOCK_WAITS` w   INNER JOIN information_schema.`INNODB_TRX` b   ON b.`trx_id` = w.`blocking_trx_id`   INNER JOIN information_schema.`INNODB_TRX` r   ON r.`trx_id` = w.`requesting_trx_id`;

六、小结

1、MySQL表更新时，对记录的锁定根据更新时where谓词条件来确定锁定范围

2、对于聚簇索引过滤，由于索引即数据，因为仅仅锁定更新行，这是由聚簇索引的性质决定的

3、对于非聚簇唯一索引过滤，由于需要回表，因此锁定为唯一索引过滤行数加上回表行数

4、对于非聚簇非唯一索引过滤，涉及到了间隙锁，因此锁定的记录数更多

5、如果过滤条件无任何索引或无法使用到索引，则锁定整张表上所有数据行

本文刊载的所有内容，包括文字、图片、音频、视频、软件、程序、以及网页版式设计等部门来源于互联网，版权均归原作者所有！本网站提供的内容服务于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯本网站及相关权利人的合法权利。
联系信息：邮箱aoxolcom@163.com或见网站底部。

THE END