5：行锁与事务隔离级别案例分析
2021-04-26 本文已影响0人
_River_
事务的提交
Session 会话 （窗口）
当前session禁用autocommit（默认开启 自动提交事务），SET autocommit = 0;
自此句执行以后，该session里面的每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。
Transation事务（窗口里面的事务）
begin（ start transaction） 是指在该Session里面开启一个新的显式事务，当其提交或者回滚之后，
该显式事务会接受到。
事务的开始 begin 或 start transaction 都是显式开启一个事务；
事务的提交 commit 或 commit work 都是等价的；
事务回滚 rollback 或 rollback work 也是等价的；
查看autocommit的状态（默认为 1 即是ON 开启）
SHOW variables like 'autocommit';
修改autocommit的状态
SET autocommit = 0;
1：START TRANSACTION 后
不管autocommit 是1还是0 ，
只有当commit数据才会生效，ROLLBACK后就会回滚。
2：当autocommit 为 0 时
不管有没有START TRANSACTION。
只有当commit数据才会生效，ROLLBACK后就会回滚
建表语句
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');
1：读未提交：
1：打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：
SET tx_isolation='read-uncommitted';
BEGIN;
SELECT * FROM account;
2：在客户端A的事务提交之前，打开另一个客户端B，更新表account：
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id =1;
3：这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据
SELECT * FROM account;
4：一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：
客户端B：ROLLBACK;
特别注意 有没有其他方法可以避免 脏读
如果客户端A的目的 只是修改余额
那么不要 在查询后 在Java代码里面设置新的值 然后重新执行修改
而是 直接在数据库中进行修改 因为这个时候的修改 里面的balance是一个真实的值
UPDATE account SET balance = balance - 50 WHERE id =1;
2：读已提交（解决 读未提交 的脏读问题）：
1：打开一个客户端A，并设置当前事务模式为read committed（读已提交），查询表account的所有记录：
SET tx_isolation='read-committed';
BEGIN;
第一次查询：SELECT * FROM account;
2：在客户端A的事务提交之前，打开另一个客户端B，更新表account：
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id =1;
3：这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：
第二次查询： SELECT * FROM account;
4：客户端B的事务提交
COMMIT；
5：客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题
第三次查询： SELECT * FROM account;
注意：
6：客户端A执行与上一步相同的查询，这个时候提交客户端A的事务，再次执行查询 结果和第三次查询一样
COMMIT；
第四次查询： SELECT * FROM account;
3：可重复读（Mysql默认隔离级别）（最重要）
1：测试可重复读
1：打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录
SET tx_isolation='repeatable-read';
BEGIN;
第一次查询：SELECT * FROM account;
查询结果是400
2：在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id =1;
COMMIT;
修改之后真实值变成350
3：在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题
查询结果是400
4：提交在客户端A的事务，再次查询，发现数据已经减少50了
COMMIT;
SELECT * FROM account；
客户端B把400的真实值修改成350了
2：可重复读 无法解决的幻读
测试可重复读 中把数据修改回400 然后不执行第四步的事务提交
UPDATE account SET balance = 400 WHERE id = 1；
假设：在第三步之后 B窗口执行以下修改SQL 会发生什么
1：B窗口已经可以查询到新增的数据
INSERT INTO account values(4,'hesuijin',1000);
SELECT * FROM account;
2：A窗口还是没有看到新增的数据
SELECT * FROM account;
3：A窗口执行COMMIT后重新查询
SELECT * FROM account;
恢复数据回去
DELETE account WHERE id =4;
这个时候看起来幻读的问题好像解决了：也就是B窗口提交的新增数据 窗口A并没有读到
但尝试一下在步骤2之后 不要执行步骤3
而是在A窗口执行修改 ID =4 的数据
UPDATE account SET balance=999 WHERE id =4;
居然发现改成功了
然后重新使用 SELECT 语句进行查询 发现也能查询到该ID 至于为什么 请继续查看下面
3：可重复读 测试修改 隔离级别中使用 update对 select的修改
测试可重复读 中把数据修改回400 然后不执行第四步的事务提交
UPDATE account SET balance = 400 WHERE id = 1；
假设：在第三步之后 A窗口执行以下修改SQL 会发生什么
update account set balance = balance - 50 where id = 1
1：首先balance会根据 窗口B中的400-50=350来算
2：然后窗口A 执行SQL 再减少50 等于 300
执行修改语句之前：400
执行修改语句之后 哪怕不执行COMMIT提交事务 查询结果也会变成300
注意：这个时候窗口A的事务还没被提交
重新执行步骤2：在窗口B执行修改SQL 发现窗口A重新查询也不会变成250
4：可重复读 中MVCC机制了解
可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，
在可重复读的隔离级别下
select操作不会更新版本号，是快照读（历史版本）；
insert、update和delete会更新版本号，是当前读（当前版本 真实的值）
5：可重复读 MVCC版本控制流程说明 （原理下章讲解）
MVCC版本控制原理后面回详细说明
1：窗口A 设置可重复读隔离级别
2：窗口A 执行select操作 查询数据balance 当前版本号变为1
3：窗口B 修改数据Balance
100减少50等于50（获取当前版本号的真实值）
然后版本号变为2 并提交
4：窗口A 执行select操作 查询数据balance 查询的版本号为1 发现数据不变
5：窗口A 执行update操作 修改数据balance
修改操作会获取数据库真实的balance值
50-50=0 （获取当前版本号的真实值）
并设置版本号为3
不需要Commit提交
6：窗口A 执行select操作 查询数据balance
由于本事务已经进行版本号的修改 因此查询会获取最新版本号的值
查询结果为0 （获取当前版本号的真实值 ）
7：由于窗口A的事务还没有提交， 在窗口B进行查询。
由于事务隔离级别是可重复读，在B窗口的事务中查询的版本号是2 ，因此读到数据为 balance =50
8：假如这个时候窗口A的事务ROLLBACK；
那么整个数据会回到版本2的状态；
4：可串行化（查询修改语句 都会加间隙锁）（解决幻读问题）
可串行化 读写 都会加间隙锁
可重复读 写 才会加间隙锁
间隙锁：所有事务都可读，但只有本事务可写
1：打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：
SET tx_isolation='serializable';
BEGIN;
SELECT * FROM account;
2：打开一个客户端B，更新相同的id等于1的记录会被阻塞等待，新插入一条数据也会阻塞等待；
UPDATE account SET balance = balance - 50 WHERE id =1;
INSERT INTO account values(4,'hesuijin',1000);
3：打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：
SET tx_isolation='serializable';
BEGIN;
SELECT * FROM account WHERE id =1；
4：打开一个客户端B，更新另外的id不等于1的记录会成功，新插入一条数据也会成功；
结论：
如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围，
就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁。
此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。
这种隔离级别并发性极低，开发中很少会用到。
5：间隙锁(Gap Lock)
间隙锁：所有事务都可读，但只有本事务可写
1：哪个隔离级别就存在间隙锁
间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？
间隙锁在某些情况下可以解决幻读问题
2：可重复读 和 可串行化 什么条件下用间隙锁
可串行化 读写 都会加间隙锁
可重复读 写 才会加间隙锁
间隙锁：所有事务都可读，但只有本事务可写
3：间隙锁的范围真的只是在范围内吗
1：在窗口A 下面执行
SET tx_isolation='repeatable-read';
BEGIN;
UPDATE account SET name = 'hahahaha' where id > 1;
SELECT * FROM account;
2：在窗口B 下面执行
INSERT INTO account values(5,'hesuijin',1000);
长时间等不到间隙锁 直接报错了
3：提交窗口A的事务 这个时候也不会新增ID等于5的数据了
4：深入理解间隙锁的间隙：
间隙锁有可能会超时法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，
即id在(4,20]区间都无法修改数据，注意最后那个20也是包含在内的。
SET tx_isolation='repeatable-read';
BEGIN;
#注意此时的范围有
#1到4（包含4) 4到10(包含10) 10到20（包含20）20到正无穷
#那么间隙锁 锁住8到18的话 实际上会锁住 4到20（左开右闭）
UPDATE account SET name = 'hahahaha' where id > 8 and id <18;
SELECT * FROM account;
特别注意：
假如间隙锁 的范围到达了21 （20到正无穷区间）
那么20到正无穷的所有已存在的行以及间隙都会加锁。。。
5:临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。
像上面那个例子里的这个(4,20]的整个区间可以叫做临键锁。
6：无索引行锁会升级为表锁（极度危险）
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
窗口A 执行：
SET tx_isolation='repeatable-read';
BEGIN;
UPDATE account SET balance = 800 WHERE name = 'lilei';
窗口B 执行：
INSERT INTO account values(19,'hesuijin',1000);
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，
例如：select * from test_innodb_lock where a = 2 for update;
这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
结论
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，
但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当
系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。
7：行锁分析（了解即可 其他交给DBA）
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';
核心数值
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，
我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
了解：
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
‐‐ 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
‐‐ 查看锁（查看所有锁）
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
‐‐ 查看锁等待（查看正在等待的锁）
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
开启两个事务 均执行以下SQL
SET tx_isolation='repeatable-read';
BEGIN;
UPDATE account SET balance = 999 WHERE id = 1 ;
如果导致了长时间等待锁（或者死锁） 需要进行释放锁
释放锁：trx_mysql_thread_id可以从INNODB_TRX表里查看到
配合使用： select * from INFORMATION_SCHEMA.INNODB_TRX;
线程ID：833 在等待 线程ID：837 执行完
如果死锁则需要Kill 调线程 ID：837
kill 837 （kill trx_mysql_thread_id）
然后线程ID：833就可以继续执行了
8：死锁（了解即可 其他交给DBA）
查看近期死锁日志信息（注意发生的时间要很近）：
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁
Navicat上：show engine innodb status；
Linux上：show engine innodb status\G;
可以把信息复制出来到TXT文件上进行分析
产生死锁：for update 相对于 串行化
1：窗口A执行：
set tx_isolation='repeatable-read';
BEGIN;
select * from account where id=1 for update;
2：窗口B执行：
set tx_isolation='repeatable-read';
BEGIN;
select * from account where id=2 for update;
3：窗口A执行：select * from account where id=2 for update;
4：窗口B执行：select * from account where id=1 for update;
Mysql有时候可以帮助检查到死锁
9：锁优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
