MySQL事务和锁实战篇

MySQL事务和锁

事务

说到关系型的数据库的事务，相信大家对四大特性都不陌生，分别是原子性、一致性、隔离性、持久性，简称为ACID特性。

MySQL中支持3种不同的存储引擎：

MyISAM存储引擎、Memory存储引擎、和InnoDB存储引擎

注：只有InnoDB才支持事务。

事务的控制语句

控制语句	作用
begin或者start transaction	开启一个事务
commit 或者 commit work	提交事务，进行持久性修改
rollback 或者 rollback work	回滚事务，撤销已经进行修改但未提交的操作
savepoint [保存点]	在事务中创建一个保存点，一个事务可以有多个保存点
releasavepoint [保存点]	回滚到指定的保存点
set transaction	设置事务的隔离级别

事务隔离级别设置

先复习一个事务的四大隔离级别

1. 读未提交（READ-UNCOMMITTED)

2. 读已提交（READ-COMMITTED)

3. 可重复读（RE-PEATABLE-READ)

4. 可序列化读（SERIALIZABLE)

下面是操作过程。

首先，查看默认的事务隔离级别，可以看到是可重复读（REPEATABLE-READ），

show variables like '%isolation%';

脏读

我们来演示一下脏读的场景，下面这张图是展示了我原先已经创建好的两个用户的账号都为100元。

分别打开两个连接mysql的会话窗口，其中一个会话的隔离级别为READ-UNCOMMITED，然后再另一个窗口中开启一个事务，例如，lisi给zhangsan转账100元，

set session transaction isolation level read uncommitted;
begin;
update user set money=money-100 where user=lisi;

我们在另外一个读未提交的窗口中查看，zhangsan看到钱已经转过来了，但是实际上lisi的事务还没有提交，假如这个时候，lisi不想转账了，回滚事务，那zhangsan就读到脏数据了。

不可重复读

下面来展示一下不可重复读的场景。

首先我们将zhangsan的窗口的事务隔离级别设置成READ-COMMITTED，并且在两个窗口都开启事务

set session transaction isolation level read committed;

假设zhangsan现在想统计全部人的钱有多少，很明显200；

但是这个时候lisi往账户里面存了100元，并提交事务，但是这个时候，zhangsan再次查询总和，我们会查询到总金额为300，但是这次查询是处于同一个事务中，查询到两次不一样的结果，属于不可重复读的情况。

update user set money=money+100 where user='lisi';
commit;

幻读

为了解决不可重复读的问题，我们将事务的隔离等级设置成RE-PEATABLE-READ，即MySQL默认的事务隔离等级，然后在两边都开启一个事务。

set session transaction isolation level repeatable read;

我们先在一个窗口插入一条数据并提交，然后在另外一个窗口查看，此时是查询不到这个记录的，但是假如这个时候我们新插入一条主键和刚插入的记录一样的话，我们就可以发现

insert into user values('zly1',100);
# 另外一个窗口
insert into user values('zly1',100);
ERROR 1062 (23000): Duplicate entry 'zly1' for key 'user.PRIMARY'

这样也算是一种幻读的现象，但是网上也有一种说法在可重复读的等级下，幻读是可避免的，这种说法不是非常准确的，如果在进行更新和插入时就可能会出现幻读的情况，如果想要解决幻读的情况，可以将事务隔离等级设置到SERIALIZABLE

锁机制

InnoDB的行级锁

InnoDB默认采用的行级锁，分为以下这两种，分别为共享锁和排他锁。

这两个概念我在这篇文章中也有介绍。

共享锁（S锁）：也叫读锁，如果在该数据对象上加了共享锁，该事务可以读取但是不能修改数据。其他事务也可以在该对象上加共享锁，但是不能修改数据。

排他锁（X锁）：也叫写锁，在一个数据对象只有一把排他锁，获取到该锁的事务可以读取数据和修改数据。

（加锁：一般的查询语句不会加任何的锁类型，当然也可以为数据加锁，比如在select * from … for update，这样可以为数据添加排他锁，而使用select … lock in share mode 可以为数据添加共享锁。

锁实战

首先关闭事务自动提交

set autocommit=0;

我们先在一个窗口输入一条获取到排他锁，虽然操作的是一条数据，但是锁的是整张表，因为我们没有添加索引。

select * from user where user='zly1' for update;

这个时候我们对user这一列添加索引，就可以看到我们对其进行加锁就不会出现阻塞的情况了。

alter table user add index(user);

注：在MySQL的行级锁是针对索引加的锁，而不是针对表中的行加级锁，虽然访问不同行的记录，但是如果不存在对应的索引，或者使用相同的索引的话，就会造成锁的冲突而锁住整张表。

死锁

死锁是指两个或者两个以上的额事务在执行过程中，因为互相的等待或者因为争抢相同的资源而造成的互相等待现象。

我们还是采用刚刚的例子，还是将事务的自动提交关闭掉，首先先在会话1中，更新id为1的记录，然后在会话2中更新id为2的记录，这个时候我们再回来在会话1更新id为2的记录，在会话2中更新id为1的记录，就会产生一个死锁；

总结

本文主要介绍了事务的隔离等级和事务的锁机制，主要更加偏向于实战部分，我前面也有一些文章涉及到。