基于更新SQL语句理解MySQL锁定详解

2019-01-02 14:52:06来源:作者:人点击

分享

前言

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: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G-- 从下面的结果可知,trx_rows_locked,一行被锁定 *************************** 1. row *************************** trx_id: 6349647 trx_state: RUNNING trx_started: 2018-11-06 16:54:12trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 1 trx_rows_modified: 1trx_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: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G-- 从下面的查询结果可知,trx_rows_locked,2行被锁定*************************** 1. row *************************** trx_id: 6349649 trx_state: RUNNING trx_started: 2018-11-06 16:55:22trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 2 trx_rows_modified: 1trx_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: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G --从下面的查询结果可知,703行被锁定*************************** 1. row ***************************  trx_id: 6349672  trx_state: RUNNING trx_started: 2018-11-06 17:06:53trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 703 trx_rows_modified: 351trx_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: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM 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:41trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 3429 trx_rows_modified: 26trx_isolation_level: REPEATABLE READ(root@localhost) [user]> rollback;Query OK, 0 rows affected (0.00 sec)-- 也可以通过show engine innodb status进行观察show engine innodb status/G------------TRANSACTIONS------------Trx id counter 6349584Purge done for trx's n:o < 0 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 6349583, ACTIVE 2 sec2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1------------TRANSACTIONS------------Trx id counter 6349586Purge done for trx's n:o < 6349585 undo n:o < 0 state: running but idleHistory list length 1LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 6349585, ACTIVE 8 sec3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL 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_lvlFROM 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_queryFROM 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、如果过滤条件无任何索引或无法使用到索引,则锁定整张表上所有数据行

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对第七城市的支持。

您可能感兴趣的文章:

  • Mysql查询正在执行的事务以及等待锁的操作方式
  • PHP利用Mysql锁解决高并发的方法
  • PHP+MySQL高并发加锁事务处理问题解决方法
  • MySQL锁机制与用法分析
  • MySQL如何查看元数据锁阻塞在哪里
  • MySQL语句加锁的实现分析
  • Mysql使用kill命令解决死锁问题(杀死某条正在执行的sql语句)
  • MYSQL 解锁与锁表介绍
  • 一次Mysql死锁排查过程的全纪录
  • MySQL对于各种锁的概念理解

相关文章

    无相关信息

微信扫一扫

第七城市微信公众平台