二|七、Mysql事务隔离机制和锁机制数据库|mysql|java|redis|python|mys

一、myql索引底层数据结构与算法
二、mysql——Explain工具介绍
三、mysql——索引最佳实践
四、mysql索引优化实战
五、mysql——常见sql语句优化
六、mysql索引优化实战二
七、mysql事务隔离机制和锁机制
ACID A：原子性——事务是一个整体，要么全部执行，要么全部不执行；
C：一致性——从数据角度看，事务中对于数据的操作，都要保持一致状态，以保证数据的完整性；
I：隔离性——事务之间的操作相互不影响；
D：持久性——对于数据的修改是持久性的，即使出现系统故障也能够保持。
并发事务带来的问题 1、脏写（更新丢失）其他事务最后的跟新操作覆盖前面事务所做的操作
2、脏读事务A读取到了事务B已经修改但是没有提交的数据；
3、不可重复读事务A内部不同时候的相同的查询语句，查询出来的结果不同；
4、幻读事务A读取到了事务B的新增的数据；
事务隔离级别
文章图片

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation';

设置事务隔离级别：

set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别
锁详解锁分类 1、乐观锁，悲观锁
2、读锁，写锁
针对同一份数据，读锁之间不互斥；
写锁，与写锁/读锁都互斥；
添加读锁

lock table mylock read;

当前session和其他session都可以读该表
当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
添加写锁

lock table mylock write;

当前session对该表的增删改查都没有问题，
其他session对该表的所有操作被阻塞
表锁VS行锁表锁锁住一张表，锁粒度大，并发低，一般用在数据库迁移的场景；
手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

行锁：每次操作锁住一行数据，开销大，加锁慢；锁粒度小，发生冲突的概率低，并发高；
一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞；
总结 InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
可重复读隔离级别下，如何正确的更新数据

update account set balance = balance - 50 where id = 1

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。**
MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。 undo版本链

文章图片

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。间隙锁 Mysql默认隔离级别是可重复读，配合间隙锁在某些情况下可以解决幻读问题；

文章图片

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18; ，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。间隙锁是在可重复读隔离级别下才会生效。
临键锁(Next-key Locks) Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。
注意——无索引行锁会升级为表锁锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。
结论 Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。
对各个状态量的说明如下 Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg: 每次等待所花平均时间 Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
对于这5个状态变量，比较重要的主要是： 【二|七、Mysql事务隔离机制和锁机制】Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
锁优化建议尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁合理设计索引，尽量缩小锁的范围尽可能减少检索条件范围，避免间隙锁尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行