数据库隔离级别
数据库隔离级别
如果没有隔离级别会出现的问题
脏读
意思是读取到了事务正在修改的数据，如果事务回滚，那么拿到的数据就是错误的
| 时间 | 事务A | 事务B |
|---|---|---|
| 1 | 开始事务 | |
| 2 | 读取quantity为5 | |
| 3 | 修改quantity为4 | |
| 4 | 开始事务 | |
| 5 | 读取到quantity为4 | |
| 6 | 发生错误，回滚，quantity为5 | |
| 7 | 提交事务 |
在按照正常逻辑quantity应该为5
不可重复读
| 时间 | 事务A | 事务B |
|---|---|---|
| 1 | 开始事务 | |
| 2 | 读取quantity为5 | |
| 3 | 开始事务 | |
| 4 | 修改quantity为4 | |
| 5 | 提交事务 | |
| 6 | 读取quantity为4 | |
| 7 | 提交事务 |
在同一个事务内，两次读取同一个数据产生不一致
幻读
| 时间 | 事务A | 事务B |
|---|---|---|
| 1 | 开始事务 | |
| 2 | 更新所有行的quantity为100 | |
| 3 | 开始事务 | |
| 4 | 插入一行quantity为5 | |
| 5 | 提交事务 | |
| 6 | 查询所有行的quantity | |
| 7 | 提交事务 |
当一个事务内更新所有行后，另一个事务插入了新行，当再次查看记录时，发现有未更新的记录，好像幻觉一样
丢失更新
第一种情况：
| 时间 | 事务A | 事务B |
|---|---|---|
| 1 | 开始事务 | |
| 2 | 查询到quantity为10 | |
| 3 | 开始事务 | |
| 4 | 查询到quantity为10 | |
| 5 | 更新quantity为11 | |
| 6 | 提交事务 | |
| 7 | 更新quantity为9 | |
| 8 | 事务回滚，quantity为10 |
可以看到，回滚的事务把正常事务的数据覆盖了，正常事务的数据丢失了
第二种情况：
| 时间 | 事务A | 事务B |
|---|---|---|
| 1 | 开始事务 | |
| 2 | 查询到quantity为10 | |
| 3 | 开始事务 | |
| 4 | 查询到quantity为10 | |
| 5 | 更新quantity为9 | |
| 6 | 提交事务 | |
| 7 | 更新quantity为11 | |
| 8 | 提交事务 |
这种情况是事务在执行期间，其他事务对数据进行了修改，那么当前事务拿到的数据就是错的，对错的数据进行更新，那也就没有意义了
解决方法
对于脏读、不可重复读、幻读
我们可以使用数据库提供的隔离级别来避免以上情况
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read-Uncommitted(读取未提交的内容) | √ | √ | √ |
| Read-Committed(读取已提交的内容) | × | √ | √ |
| Repeatable-Read(可重读) | × | × | √ |
| Serializable(串行化) | × | × | × |
Mysql的默认隔离级别为Repeatable-Read,可以通过以下命令查看
SELECT @@global.tx_isolation;--查看全局隔离级别
SELECT @@session.tx_isolation;--查看当前连接的隔离级别
修改隔离级别
SET @@global.tx_isolation='Read-Committed'
SET @@session.tx_isolation='Read-Committed'
--或
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE
对于丢失更新
使用悲观锁
悲观锁主要有共享锁（读锁）和排他锁（写锁）
- 共享锁是指多个事务可以共享一个一把锁，都可以读取到数据，但是不能修改
- 排他锁就是一个事务获得了排他锁，那么其他事务就不能获得锁（包括共享锁和排他锁），获取排他锁的事务可以对数据进行访问和修改
Mysql默认开启了自动事务提交，可以使用以下命令关闭
SET autocommit=0 --关闭自动事务提交
这里必须要强调一下锁的概念，不管是共享锁还是排他锁，都是我们给每一个数据元素加的，如果一个数据元素已经有了排他锁，那么久不能再给它加任何锁，如果一个数据元素有共享锁，那么还可以给它加共享锁，Mysql的InnoDB引擎默认给insert、update、delete都加了排他锁，而select未加任何锁
新建一个查询窗口，开始事务，但是没有提交，因为update默认给数据元素加排他锁，所以这个时候我们去更新该数据元素就会出现
上一个事务还没有提交，数据元素还有排他锁，这个update语句要给数据元素加排他锁，所以只有等待，这也验证了update语句默认会给相关的数据元素加排他锁
如果使用select语句加共享锁进行查询一样会阻塞
但是使用select语句不加任何锁是可以查出数据的，但是数据是更新之前的
所以，使用悲观锁在高并发情况下，对于减库存这样的操作，首先要使用排他锁的select语句拿到库存，如果已经有事务对这个数据元素上了锁，那么只有等待该事务释放锁，只有这样拿到的库存才是正确的
BEGIN;
DECLARE @now_quantity INT;
SELECT quantity INTO @now_quantity FROM item WHERE id=1 FOR UPDATE;//一定要加排他锁
UPDATE item SET quantity=@now_quantity-1 WHERE id=1;
COMMIT;
而且需要注意，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住
使用悲观锁的方式解决丢失更新很简单，但是也会带来效率上的问题，如果一个事务上了锁，那么其他的都只有等待
使用乐观锁
我们可以给表中加上一个version自增的版本字段，查询的时候拿到版本字段和库存，当需要去更新的时候，如果版本不一致，那么需要重新查询，重复上述步骤，知道拿到的版本和数据库中的版本一致时，才进行更新，这样就不需要等待，效率更高
