MySQL语句加锁的示例解析

发布时间：2022-02-19 10:53:21 所属栏目：MySql教程来源：互联网

导读：这篇文章主要介绍了MySQL语句加锁的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。建立一个存储三国英雄的hero表： CREATE TABLE hero ( number INT, name VARCHAR(100), c

这篇文章主要介绍了MySQL语句加锁的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

建立一个存储三国英雄的hero表：

CREATE TABLE hero (    number INT,    name VARCHAR(100),    country varchar(100),    PRIMARY KEY (number),    KEY idx_name (name)) Engine=InnoDB CHARSET=utf8;
然后向这个表里插入几条记录：

INSERT INTO hero VALUES    (1, 'l刘备', '蜀'),    (3, 'z诸葛亮', '蜀'),    (8, 'c曹操', '魏'),    (15, 'x荀彧', '魏'),    (20, 's孙权', '吴');
然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：

语句加锁分析

其实啊，“XXX语句该加什么锁”本身就是个伪命题，一条语句需要加的锁受到很多条件制约，比方说：

事务的隔离级别
语句执行时使用的索引（比如聚簇索引、唯一二级索引、普通二级索引）
查询条件（比方说=、=<、>=等等）
具体执行的语句类型
在继续详细分析语句的加锁过程前，大家一定要有一个全局概念：加锁只是解决并发事务执行过程中引起的脏写、脏读、不可重复读、幻读这些问题的一种解决方案（MVCC算是一种解决脏读、不可重复读、幻读这些问题的一种解决方案），一定要意识到加锁的出发点是为了解决这些问题，不同情景下要解决的问题不一样，才导致加的锁不一样，千万不要为了加锁而加锁，容易把自己绕进去。当然，有时候因为MySQL具体的实现而导致一些情景下的加锁有些不太好理解，这就得我们死记硬背了～

我们这里把语句分为3种大类：普通的SELECT语句、锁定读的语句、INSERT语句，我们分别看一下。

普通的SELECT语句在：

READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED隔离级别下，不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了。不过这里有一个小插曲：
# 事务T1，REPEATABLE READ隔离级别下
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number = 30;
Empty set (0.01 sec)
# 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交
mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM hero WHERE number = 30;
| number | name    | country |
|     30 | g关羽   | 蜀      |
1 row in set (0.01 sec)
在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入了一条记录便提交了，ReadView并不能阻止T1执行UPDATE或者DELETE语句来对改动这个新插入的记录（因为T2已经提交，改动该记录并不会造成阻塞），但是这样一来这条新记录的trx_id隐藏列就变成了T1的事务id，之后T1中再使用普通的SELECT语句去查询这条记录时就可以看到这条记录了，也就把这条记录返回给客户端了。因为这个特殊现象的存在，你也可以认为InnoDB中的MVCC并不能完完全全的禁止幻读。
SERIALIZABLE隔离级别下，需要分为两种情况讨论：
在系统变量autocommit=0时，也就是禁用自动提交时，普通的SELECT语句会被转为SELECT ... LOCK IN SHARE MODE这样的语句，也就是在读取记录前需要先获得记录的S锁，具体的加锁情况和REPEATABLE READ隔离级别下一样，我们后边再分析。
在系统变量autocommit=1时，也就是启用自动提交时，普通的SELECT语句并不加锁，只是利用MVCC来生成一个ReadView去读取记录。为啥不加锁呢？因为启用自动提交意味着一个事务中只包含一条语句，一条语句也就没有啥不可重复读、幻读这样的问题了。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键