在使用数据库的时候一般都会有多个事物进行对数据库操作 这样就会有并发问题 就会出现脏读、不可重复度、幻读、脏
写等这些问题 为了解决这些问题MySql设计了 事务隔离级别、锁机制、MVCC多版本并发控制隔离机制
事物是由多个SQL语句组合执行 为了不让数据出现问题 有了ACID（原子性、一致性、隔离性、持久性 这几个英语单词的
第一个首字母）属性
原子性就是在同一个事物中有三个SQL语句 不能第一个执行成功第二个执行失败第三个执行成功 必须要同时成功或者同时失败
一致性就是同一个事物中有三个SQL语句 不能第一条SQL对数据进行了更改 第二条更改失败 第三条更改成功 必须同时对数据操作成功或者失败 跟原子性一样 只不过原子性目的在于操作上 一致性在于数据上
隔离性就是只能查询到在进行开启事物那一刻的数据
持久性就是数据要永久的保存 不能重启一下系统数据丢失了
脏写或者更新丢失
同时开启了两个事物A跟B 这两个事物又同时执行了查询语句比如查出来的结果是20 A对这个结果进行减2 B对这个结果进行减3 然后A先把这条数据进行了更新 结果应该是18 然后B又对这条数据进行了更新结果变成了17 正确的结
果应该是15 这个17就叫做脏写
脏读
事物A跟B A对一条数据进行了修改比如20修改为10 这时没有提交 B又进行了查询查出来的结果为10 这样的情况叫做脏读 这样的后果就是代码不好写 值不确定一会是10一会是20编写逻辑会复杂
不可重复读
事物A跟B A对一条数据进行了修改比如20修改为10 这时把数据提交了 然后B查询 又查询出了10 这样的情况叫做不可重复读
幻读
事物A跟B A进行了插入数据 B查询是查不到 但是可以对A插入的数据进行update语句的操作 这就叫做幻读事物隔离级别
读未提交 都没解决
读已提交 解决了脏读
可重复读 解决了脏读 不可重复读
可串行化 解决了脏读 不可重复读 幻读
锁
上面的那些问题的解决办法就是加锁 事物A对数据进行操作时事物B也想对这个数据进行操作 让事物B等待事物A操作完在执行 锁又分为表锁跟行锁 表锁就是当事物A对这个表进行了操作 事物B也想对这个表进行操作然后就不让事物B对这
个表进行操作 行锁事物A对第一行进行操作 事物B也对第一行进行操作 这时就让事物B等着 但是事物B可以对除了第一
条任意一条进行操作
锁分类
锁分为乐观锁跟悲观锁、写锁（排它锁、X锁）、读锁（共享锁 S锁）
乐观锁是在表中创建一个版本的字段实现 当一个事物要更新一条数据时会先比较它的版本是否跟数据库中的版本一样 不一样就做不一样的逻辑
写锁、读锁都是悲观锁 只要有等待的就是悲观锁 加了写锁别的事物不能对数据进行查询、修改等操作 读锁其他事物可以进行查询但是不能修改数据
手动增加表锁
lock table 表名称 read或write,表名称2 read或write; // 后面这个read表示读锁 write表示写锁
查看表上加过的锁
show open tables;
删除表锁
unlock tables;
行锁
InnoDB跟MyiSAM引擎区别
InnoDB支持事物、InnoDB支持行锁
MyiSAM在执行select语句前会给表加读锁 在执行update、insert、delete前会加写锁
InnoDB在执行select语句前会给行加读锁 在执行update、insert、delete前会给行加写锁事物隔离级别的开启
set tx_isolation=‘read-uncommitted’; // 读未提交
set tx_isolation=‘read-committed’; // 读已提交
set tx_isolation=‘repeatable-read’; // 可重复读
set tx_isolation=‘serializable’; // 可串行化
串行化会给每个操作都加上锁只有事物在对一个数据进行操作 其他事物都必须等着它对数据操作完
可重复读采用的是MVCC机制
间隙锁
i d
1
2
5
间隙锁就是update account set name = ‘zhuge’ where id > 3 and id <6;这一个sql 大于3小于6 看上面的为1、2
、5 间隙锁就是2-5 5-正无穷 这些数据全给加锁了 在可重复读隔离级别下才会生效
临键锁
(3,5] 就是大于3小于等于5这种叫临键锁 这个是基于间隙所的 间隙所跟行锁的组合
无索引行锁会升级为表锁(RR级别会升级为表锁、RC级别不会升级为表锁)
锁一般都加载索引字段上 如果对非索引字段加锁 行锁可能变成表锁 InnoDB的行锁是针对索引加的锁 不是针对记录加锁 并且这个索引不能失效 要是失效了就会升级为表锁
行锁分析
通过show status like ‘innodb_row_lock%’; 这个SQL语句进行查看分析
对各个状态量的说明如下：
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（等待总时长）
要是等待次数过多而且每次等待时间也很长 我们就需要分析为什么
查看系统库锁相关的数据表
– 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
– 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
– 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
– 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id（这里写从INNODB_TRX表中查出来的trx_mysql_thread_id字段数据）
– 查看锁等待详细信息
show engine innodb status\G;
锁优化建议
尽可能的让所有数据检索都通过索引来完成 避免让它升级为表锁
合理设计索引，缩小锁的范围
尽可能减少查询条件范围避免间隙所
尽可能降低事务隔离级别
尽量让对数据修改的sql语句以及涉嫌加锁的sql放在最后执行
