MySQL开发技巧——并发控制

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	锁。操作一条记录也会锁住整个表	行锁。操作时只锁某一行
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还缓存真实数据
表空间	小	大
关注点	性能	事务
默认安装	是	是

锁机制

问：锁是什么，有什么用？

答：锁的主要作用是管理共享资源的并发访问，锁可以用于实现事务的隔离。

问：为什么要加锁？

答：为了避免多个事务同时操作数据库导致数据异常，一般会通过锁机制解决。

锁的分类：

从对数据操作的类型（读/写）分类：共享锁（读锁）、排它锁（写锁）；
从对数据操作的粒度分：表锁、行锁。

下面用一张图说明所之间的关系：

加锁原则：对于MySQL的InnoDB引擎来说，insert、update、delete等操作，会自动给涉及的数据加排他锁；对于一般的select语句，InnoDB不会加任何锁，但在事务可以通过指定SQL语句给显示加共享锁或排他锁。

表锁

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发送锁冲突的概率最高，并发度最低。

乐观锁、悲观锁的概念

乐观锁是指操作数据库时（更新操作），想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，因此悲观锁需要耗费较多的时间。

悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是读锁与写锁。下面就让我们一起来学习表锁如何添加读写锁。

给表添加读锁和写锁

为什么要给表添加锁呢？

不加锁，若同事两个人修改同一张表的数据，当再次查询时，其中一个总会发现莫名其妙表数据被修改了。因此我们需要添加锁。

在给表添加锁之前，我们首先要创建一张使用 MyISAM 存储引擎的mylock表：

create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
insert into mylock(name) values('a'),('b'),('c'),('d'),('e');

我们通过show open tables查看数据库中有没有加锁的表：

查询结果中In_use列中值全为0，说明没有表被锁，我们手动给表增加锁：

lock table 表名 read(write),表名2 read(write)，其他；

下面给数据库中的account表上写锁，给mylock上读锁:

发现In_use均为1，那么要怎么解锁呢？

下面我们具体来看添加锁的作用：

① 添加读锁后的特征：多个连接对于同一数据都能访问，但是只能读不能修改，要想修改除非开启锁的连接将锁关闭。

但是这种方式会是性能变的很慢，当我们还有多个连接也在等待修改时，会消耗很多时间。

② 添加写锁后的特征：多个连接对于同一数据不能同时访问，但加锁的连接中可以更新表数据，另一个连接要想访问数据，必须解锁。

编程要求

编写代码，为student添加并修改数据，要求使用读锁并且数据成功添加到表中，但修改的数据报锁表异常，修改失败（失败原因为添加了read读锁，因此不能修改表中信息），具体要求如下：

student表结构：

列名	类型	备注
stu_id	int	学号
name	varchar(25)	姓名
math	int	数学成绩
chinese	int	语文成绩

1.平台已在表中填加了学号为2的Jack的信息，接下来是你需要添加的信息：

stu_id	name	math	chinese
1	Tom	80	78
3	Lucy	97	95

2.由于老师添加2号学生成绩的时候数学成绩录入错误，需要将学号为2的同学数学分数改为100分。

预期输出：

'src/step1/query1.sql': Table 'student' was locked with a READ lock and can't be updated`（首行输出信息为锁表异常，因此不能更新）
+--------+------+------+---------+
| stu_id | name | math | chinese |
+--------+------+------+---------+
| 1 | Tom | 80 | 78 |
| 2 | Jack | 10 | 80 |
| 3 | Lucy | 97 | 95 |
+--------+------+------+---------+

use School;
#请在此处添加实现代码
########## Begin ##########
insert into student values(1,'Tom',80,78);
insert into student values(3,'Lucy',97,95);
lock table student read;
update student set math=100 where stu_id=2;

########## End ##########

第2关事务隔离级别

一、事务并发下出现的问题

通常事务并发会出现几种现象：1.脏读；2.不可重复读；3.幻读。

脏读

一个事务 A 读取了另一个并行事务 B 未最终提交的写数据, 那事务A的这次读取就是脏读。

不可重复读

假设“脏读”问题完全解决了，那就意味着事务中每次读取到的数据都是“持久性”的数据（被别的事务最终“提交/回滚”完成后的数据）。

但是解决了脏读问题, 只是能保证你在事务中每次读到的数据都是持久性的数据而已!

如果在 一个事务 中多次读取同一个数据，正好在两次读取之间，另外一个事务确实已经完成了对该数据的修改并提交，那问题就来了，可能会出现 多次读取结果不一致 的现象，这种现象也就被称为不可重复读：

幻读

事务 A 在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务 B 执行了新增数据的操作并提交后，这个时候事务 A 读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，这种现象就称为幻读：

二、事务隔离级别

针对上面所出现的问题，数据库提出了对应的解决方案，就是事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复度（Repeatable read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

未提交读：就是一个事务可以读取另一个未提交事务的数据。

已提交读：就是一个事务要等另一个事务提交后才能读取数据。（ O\fracle 数据库的默认级别）

可重复读：就是在开始读取数据（事务开启）时，不再允许修改操作，也是 MySQL 数据库的默认隔离级别。

可串行化：意思是说这个事务执行的时候不允许别的事务 并发写 操作的执行。这是事务隔离的最高级别，虽然最安全最省心，但是效率太低，一般不会用。

三、查询事务级别

查询事务级别的 SQL如下：

select @@tx_isolation;

四、修改数据库的事务级别

事务级别是可以被修改了，那么修改的 SQL 如下：

SET SESSION TRANSACTION ISOLATION LEVEL 事务级别；

五、头歌实验

任务描述

本关任务：修改并查询数据库的隔离级别。

事务并发下出现的问题

通常事务并发会出现几种现象：1.脏读；2.不可重复读；3.幻读。

脏读

一个事务 A 读取了另一个并行事务 B 未最终提交的写数据, 那事务A的这次读取就是脏读。

不可重复读

假设“脏读”问题完全解决了，那就意味着事务中每次读取到的数据都是“持久性”的数据（被别的事务最终“提交/回滚”完成后的数据）。

但是解决了脏读问题, 只是能保证你在事务中每次读到的数据都是持久性的数据而已!

幻读

小提示，幻读和不可重复读的区别： **幻读** 是在同样的条件下，你读取过的未存在的数据，当你插入时，发现数据又存在了，它的重点在于`insert`。 **不可重复读** 是在同样的条件下，你读取过的数据，再次读取出来发现值不一样了，重点在于`update`和`delete`。

事务隔离级别

针对上面所出现的问题，数据库提出了对应的解决方案，就是事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复度（Repeatable read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

未提交读：就是一个事务可以读取另一个未提交事务的数据。

已提交读：就是一个事务要等另一个事务提交后才能读取数据。（ O\fracle 数据库的默认级别）

可重复读：就是在开始读取数据（事务开启）时，不再允许修改操作，也是 MySQL 数据库的默认隔离级别。

查询事务级别

查询事务级别的 SQL如下：

select @@tx_isolation;

例如，查询 MySQL 的默认级别：

修改数据库的事务级别

事务级别是可以被修改了，那么修改的 SQL 如下：

SET SESSION TRANSACTION ISOLATION LEVEL 事务级别；

例如，我们将 MySQL 的事务级别修改成未提交读：

编程要求

补充代码，将隔离级别修改成“未提交读”，并查询你修改后的隔离级别。

小贴士：完成本关任务后，可以通过开启两个或以上命令行连接数据库，在连接中都开启事务，体验上述几种事务并发情况，连接数据库用户名为`root`，密码为`123123`。（点击`+`添加一个命令行窗口 ![](https://data.educoder.net/api/attachments/368219)）。

在数据库mydb中提供了一张account表数据可供你体验使用：

name	money
A	100
B	100

预期输出：

+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+

use mydb;

#请在此处添加实现代码
########## Begin ##########

#1.修改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL Read uncommitted;

#2.查询隔离级别
SELECT @@tx_isolation;

########## End ##########