MySql--事务

脏写实例：

保单保额 10000
同时两个事务开启操作保额信息

事务A
读取到保额为10000 将保额修改为5000

事务B
读取到保额为10000 将保额修改为9000

事务A和事务B读取到初始信息相同，但做了不同的操作，事务A先提交，事务B后提交，数据库保单保额变为9000，但实际保单保额应该为4000

总结：最后的更新覆盖了其它事务所做的更新

脏读实例：

保单保费5000
同时两个事务开启操作保费信息

事务A
读取到保费为5000，将保费更新为8000

事务B
读取到保费为8000，生成8000元的保费待收数据

事务B读取到了事务A未提交的信息，事务B先提交，事务A回滚了，此时事务B生成的待收数据是错误的，实际待收应该是5000

总结：事务B读取到了事务A已经修改但尚未提交的数据

不可重复读实例：

客户年收入1000000
同时两个事务开启操作客户年收入信息

事务A 事务B同时开启
事务A读取到客户年收入为1000000，此时事务A进行中，还未提交

事务B操作客户年收入信息为800000
事务A再次执行查询，此时事务A读取到客户年收入为800000

即在同一个事务中，两次相同的查询获取到不同的结果

总结：事务A内部的相同查询语句在不同时刻返回的结果不同

幻读：

一个机构下大于2000000保额的保单有5张
同时两个事务开启查询操作保单信息

事务A事务B同时开启
事务A读取到该机构下大于2000000保额的保单有5张，此时事务A还未提交

事务B新增一条3000000保额的保单并提交
事务A再次读取，发现此时该机构下大于2000000保额的保单有6张

即事务A两次查询结果由于事务B的影响得到了不同结果

总结：事务A读取到了事务B新增的数据

幻读和不可重复读概念有些类似，幻读更倾向于读取到了别的事务新增的数据，不可重复读更倾向于读取了被别的事务修改的数据。

事务隔离级别

-- 读取事务隔离机制	默认可重复读
-- mysql8之前的版本
show VARIABLES like 'tx_isolation';
-- mysql8
show VARIABLES like 'transaction_isolation';
-- 设置事务隔离级别
-- mysql8之前的版本
set tx_isolation='REPEATABLE-READ';
-- mysql8  
set transaction_isolation='REPEATABLE-READ';

-- READ-UNCOMMITTED 无法解决以上问题
set transaction_isolation='READ-UNCOMMITTED';
-- READ-COMMITTED 解决脏读
set transaction_isolation='READ-COMMITTED';
-- REPEATABLE-READ 解决脏读、重复读 在该级别下间隙锁会生效
set transaction_isolation='REPEATABLE-READ';
-- 串行化 所有执行中的范围数据都会被加锁 解决脏读、重复读、幻读
set transaction_isolation='SERIALIZABLE';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果用Spring设置了就用已经设置的隔离级别

锁

通过锁机制解决事务并发导致的数据库问题
从性能分为乐观锁和悲观锁
从对数据库操作的类型，分为读锁和写锁(都属于悲观锁)
从数据操作的粒度，分为表锁和行锁     

乐观锁：通过版本对比来实现

拿脏写实例，事务A已经将保额更新为5000并提交，此时版本已经变化，事务B更新的是旧版本的数据，数据提交就会失败，需要获取最新版本的数据进行更新。

悲观锁：等待上一个操作执行完毕后执行

拿脏写实例，事务A中执行了update保额的语句，事务B中也有update的操作，此时事务B需要等到事务A提交之后再执行。

悲观锁包含读锁和写锁

读锁(共享锁Shared)：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁(排它锁exclusive)：当前操作没有完成前，它会阻断其他写锁和读锁

总结：读锁会阻塞写，但是不会阻塞读。写锁会阻塞读写。

表锁：每次操作锁住整张表，一般用于数据迁移。
行锁：每次操作锁住一行数据。 

-- 手动增加表锁 lock table 表名 read(write),表名2 read(write);
lock table test read;
-- 查看表上加过的锁 被加锁的in_use=1
show open tables;
-- 删除表锁
unlock tables;

死锁实例

事务A与事务B同时开启
事务A执行语句锁住客户张三信息，此时事务还未提交；
事务B执行语句锁住客户李四信息，此时事务还未提交；
事务A接着需要操作客户李四信息，但是需要等待B解锁；
事务B接着需要操作客户张三信息，但是需要等待A解锁；
此时事务A和事务B相互等待都无法进行下去，导致死锁。
但是mysql有自己的解锁机制，对于很明显的死锁，会自动解锁。

-- 查看近期死锁日志
show engine innodb status;

InnoDB和MYISAM区别

InnoDB支持事务，支持行级锁，MYISAM不支持。        
MYISAM在执行select前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时，因为有mvcc机制不会加锁，但是update、insert、delete操作会加行级锁。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，若没有索引或索引失效，会从行锁升级为表锁。

间隙锁

锁住两个值之间的空隙，在可重复隔离级别下才会生效。
mysql默认级别是repeatable-read，有幻读问题，间隙锁可以解决幻读问题。
间隙锁是针对索引加锁。

select * from user where user_id=200 for update;
--此时(200,正无穷)的数据都会被锁住。

临键锁

行锁与间隙锁的组合。
锁等待分析
通过InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock';

状态变量解析参考

变量名称(红色需要重点关注)	变量释义	变量释义
Innodb_row_lock_current_waits	The number of row locks currently waited for by operations on InnoDB tables.	当前正在等待锁定的数据
Innodb_row_lock_time	The total time spent in acquiring row locks for InnoDB tables, in milliseconds.	锁等待总时间 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	The average time to acquire a row lock for InnoDB tables, in milliseconds.	锁等待平均时间 每次等待所花平均时间
Innodb_row_lock_time_max	The maximum time to acquire a row lock for InnoDB tables, in milliseconds.	从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits	The number of times operations on InnoDB tables had to wait for a row lock.	等待总次数 系统启动后到现在总共等待的次数

 

 

当等待次数很高，且等待时间也很久的时候，需要分析原因，根据分析结果进行优化。
查看INFORMATION_SCHEMA系统库锁相关数据表

--查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
--查看锁
--8.0之前
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
--8.0之后
select * from PERFORMANCE_SCHEMA.DATA_LOCKS;
--查看锁等待
--8.0之前
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;