mysql事务隔离级别和锁机制

bijian-bijian

已于 2022-08-14 13:46:36 修改

阅读量214

点赞数

文章标签： mysql 数据库 java

于 2022-06-23 18:07:58 首次发布

本文链接：https://blog.csdn.net/qq_38166592/article/details/125432063

版权

概述：
数据库一般会出现并发执行多个事务，多个事务由同时操作同一批数据，可能会导致脏读、脏写、不可重复读、幻读这些问题。这些问题的本质就是多事务的并发问题，为了解决这些问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发隔离控制机制等一系列机制来解决这些问题。

事务及其ACID属性：

事务是由一组sql组成的逻辑处理单元，其具有ACID四个属性；
1.原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都成功，要么全都不成功；
2.一致性（Consistent）：在事务开始和结束时，数据都必须保持一致，比如转账业务，转出与转入的数据要保持一致；
3.隔离性（Isolation）：事务的并发操作是相互隔离，相互不影响的。
4.持久性（Durable）：事务执行完成之后，对数据的修改是持久性的，就算机器发生故障，也不会发生丢失。

并发事务操作带来的问题：

1.脏读dirty reads：事务A读取到事务B已经修改但是尚未提交的数据，如果事务B发生回滚，则存在数据不一致的现象；
2.丢失更新lost update或脏写：对同一条数据进行操修改时，最后修改数据的事务会覆盖其它事务修改的数据。
3.不可重复读non-repeatable-reads：一个事务相同的查询sql在不同的时间段查询结果不一样。
4.幻读phantom reads:事务A修改事务B新提交的数据，然后就能读取到这个事务B新增的数据，不符合隔离性。

事务隔离级别：
不同的隔离级别解决对应的脏读、不可重复读、幻读问题；
在这里插入图片描述
数据库的隔离级别越严格，并发带来的问题影响越小，但是付出的代价越大，并发访问的能力越低；

不同的应用对事务隔离级别的要求是不同的，有的应用可能对不可重复读和幻读不敏感，更关心数据库的并发访问能力，可以设置隔离级别是度已提交。
查看当前数据库事务的隔离级别：show variables like ‘tx_isolation’;
设置食物隔离级别：set tx_isolation=‘REPEATABLE-READ’;
Mysql默认的隔离级别是可重复读，用spring开发时，如果不设置隔离级别，就是用数据库默认的隔离级别，如果设置了就是用spring设置的。

锁详解：

锁是计算机协调多个线程并发访问某一资源的能力；
如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类：

从性能来上分乐观锁（CAS版本对比）和悲观锁；
从对数据库操作的类型分为读锁和写锁（都是悲观锁）
- 读锁（共享锁，S锁Shared）：针对同一份数据，多个读操作可以并发进行，互不影响，但是阻塞写；
- 写锁（排它锁，X锁Exclusive）：针对同一份数据，一次性只能有一个写锁，再完成之前会阻塞其它的读锁和写锁
从对数据的粒度分为表锁和行锁

表锁：
一次性锁住整张表；开销小，加锁快，不会出现死锁；锁粒度大，出现锁冲突的概率高，并发度最低。一般用在整表迁移的场景。
手动给表加读锁或者写锁：
lock table 表名称 read(write),表名称2 read(write);
lock table test write;
查看当前表上加的锁：
show open tables;
删除表锁：
unlock tables;

行锁：
每次锁住其操作的行数据；开销大，加锁慢，会出现死锁，锁粒度小；发生锁冲突的概率低，并发高。
Innodb和myisam的最大不同点是：
Innodb支持事务和行锁，而myisam不支持；

Myisam在执行select时给所有涉及的表加读锁，会给增删改操作所涉及的表加写锁；
Innodb在执行select操作时不加任何锁（串行化隔离级别除外），但是在执行增删改操作时会加行锁或表锁。

当修改数据时，查询条件不走任何索引，会是表锁；
当修改数据时查询条件走了索引，并且其它的session修改数据使用的查询字段相同时，会走行锁，如果参数不一样，是互不影响的；
当是修改时，两个session修改数据时的查询字段不一样，那么对后面的session而言也是表锁，会被卡住，等待锁释放；
如果修改sql中查询条件使用了索引，则对这条修改sql来说是行锁，如果条件不使用索引，则是表锁；

间隙锁（Gap Lock）:（只有在可重复读隔离级别才生效）
间隙锁，锁的就是两个值的间隙；
在这里插入图片描述
这个数据的间隙有(2,5] (5,18]

select *from test where id>4 and id <10 for update ;
其它session没办法在这个sql查询范围中所涉及的间隙进行插入数据，也就是(2,18];
并且修改和删除数据时，这个查询条件存在的数据在(2，18]范围内的会进行阻塞。

临建锁（Next-key lock）
是行锁和间隙锁的组合，像上例中(2，18]范围可以叫做临建锁。

无索引行锁会升级为表锁，innodb中，行锁是针对索引加锁的，不是针对记录加锁的。

行锁分析：
执行命令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（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，
然后根据分析结果着手制定优化计划。

死锁
两个session相互嵌套执行，导致死锁；

set tx_isolation=‘repeatable-read’;
Session_1执行：select *from test where id=1 for update ;
Session_2执行：select *from test where id=5 for update ;
Session_1执行：select *from test where id=5 for update ;
Session_2执行：select *from test where id=1 for update ;
查看近期死锁日志信息：show engine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁Deadlock found when trying to get lock; try restarting transaction

锁优化建议