Mysql事务隔离级别与锁机制

事务及其ACID属性

• 原子性(Atomicity)：在同一个事务中的语句，要么同时生效要么同时失败
• 一致性：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
• 隔离性(Isolation)：事务与事务之间不能互相影响
• 持久性(Durable) ：事务一旦提交，就要永久保存。

并发事务处理带来的问题

问题	描述
脏读	读到其他事务未提交的事务
脏写	两个事务同时写一行数据，后面的覆盖了前面的那条。
不可重复读	在事务中重复执行相同查询语句，得到的结果不一样
幻读	在事务中重复执行相同查询语句，会得到其他事务提交的记录

隔离级别

mysql默认是可重读读。如果spring不指定就是这个级别，spring事务有指定的话优先spring

隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	可	可	可
读已提交	否	可	可
可重复度（REPEATABLE-READ）	否	否	可
串行化	否	否	否

设置隔离级别

5.7查看隔离级别
show variables like 'tx_isolation';
8.0查看隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
5.7设置隔离级别
set tx_isolation='REPEATABLE-READ';
8.8设计隔离级别
set transaction_isolation='REPEATABLE-READ';

锁

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
  读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
  写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
• 从对数据操作的粒度分，分为表锁和行锁

总结：
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

表锁测试

#手动增加表锁 read 加读 write加写 逗号后可批量
lock table 表名称 read(write),表名称2 read(write);
#查看表上加过的锁
show open tables;
#删除表锁
unlock tables;

session1对表A加了读锁，那么在session1和其他session中都可读。如果在session1中写数据报错，其他session写数据会阻塞

session1对表A加了写锁，session1中可读可写。其他session对该表的所有操作被阻塞

行锁测试（仅限innodb）

for update 行X锁
lock in share mode 行S锁

#事务一
start TRANSACTION;
select * from account where id = 1 for UPDATE;

#事务二 不管加S锁还是X锁都会阻塞
select * from account where id = 1 for UPDATE;
select * from account where id = 1 for lock in share mode;

case2

#事务一
start TRANSACTION;
select * from account where id = 1 for lock in share mode;

#事务二 加X锁阻塞 加S锁可以正常查出数据
select * from account where id = 1 for UPDATE;
select * from account where id = 1 for lock in share mode;

case3

#事务一
start TRANSACTION;
select * from account where id = 1 for UPDATE;
#事务二 在除了串行化的隔离级别情况下直接select是不会加S锁，所以这样可以读取成功
select * from account where id = 1 ;

start TRANSACTION; 后记得commit。在除了串行化的隔离级别情况下，一般的select是不会加S锁的，除非手动for lock in share mode，在串行化隔离级别情况下，普通的select也会加S锁。因为这样可以防止幻读的情况

幻读测试

在可重复度的隔离级别下
1.首先开启一个事务进行查询

start TRANSACTION;
select * from account

2.再创建一共事务插入一条新数据

insert into account (id,name,balance) VALUES (4,'cyz',600);

3.事务一 再次查询

4.事务一 查不到插入的最新数据，但是这时执行update语句后再查询，发现竟然可以读取到事务，这就是幻读。

UPDATE account SET name = 'mm' where id = 4

可重复读和java代码之间的问题

1.开启一个事务一，执行查询。

2.开启事务二，修改mm 减去 100，并提交
3.事务一再次查询，mm依然是600，但是实际库中已经变成500
4.事务一执行修改语句，并不提交事务

UPDATE account SET balance = balance-100 where id = 4

5.在事务一中再次查询，发现结果是400并没有错误，并没有因为select是600而修改错数据。
6.证明在不可重复读的情况下，相同的select语句虽然每次查的都是一样，但是真实数据并不是和查出来的一样。有一个类似副本的东西（并不是副本而是mvcc机制），但是修改又可以正确处理。保证了最终一致性，做一个类似副本的东西只是为了帮助程序员可以清晰的开发代码，不然每次查的都不一样，无法很好的做判断。

注意：

# 可以正确跟新
UPDATE account SET balance = balance-100 where id = 4

java中

 // 获得库存
Account account = accountMapper.getById(4);
 // 各种业务操作
 //设置库存
account.setBalance(400)；
accountMapper.updateById(account);

在这种java代码中 先获取库存再set,如果这时库存改变了，并不能像sql语句一样保持最终一致性。
得使用java锁的机制来规避

# 错误 java代码最后执行的是这句
UPDATE account SET balance = 400 where id = 4
# 正确
UPDATE account SET balance = balance-100 where id = 4

间隙锁(Gap Lock) 和 临键锁(Next-key Locks)

间隙锁是在可重复读隔离级别下才会生效。
间隙锁可以在某些情况下解决幻读。

假如表

在事务一中执行

update account set name = 'zhuge' where id > 5 and id <18;

则其他事务没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(4,19]区间都无法修改数据，注意最后那个19也是包含在内的。这个就是间隙锁

如果是

update account set name = 'zhuge' where id > 5 and id <31;

31大于了表里最大的记录。这时候锁的区间就是 (4，正无穷 )

临键锁(Next-key Locks) 就是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁
区别：间隙锁只是锁住间隙，并没有锁住中间具体的行。临建锁加上那些真实存在的行锁。

意向锁（表级别的锁）

意向锁有两种

• 意向共享锁（IS）表示事务意图在表中的单个行上设置共享锁。
• 意向排他锁（IX）表明事务意图在表中的单个行上设置独占锁。

在给表或者行加上S锁时 会给表加上IS锁
在给表或者行加上X锁时 会给表加上IX锁

作用：如果事务一给某一行加上了X锁。事务二要加表锁。如果没有意向锁的话就要一行行的判断有没有行锁，非常麻烦。如果在加上X锁时，再给表加上一个意向锁。其他事务只要看一下有没有意向锁就知道能不能加表锁了。IS锁类似。
意向锁解释

无索引行锁会升级为表锁

行锁都是加载索引上，如果没有索引就会升级成表锁

行锁分析

show status like 'innodb_row_lock%';

这里的锁定是指第二个事务想获得锁的时候阻塞的情况，如果只有一个事务没有人和他竞争锁不会统计
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，
然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

 # 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
# 查看锁5.7
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看锁8.0
select * from performance_schema.data_locks
# 查看锁等待5.7
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查看锁等待8.0
select * from performance_schema.data_lock_waits;



‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

 ‐‐ 查看锁等待详细信息
 show engine innodb status\G