Mysql事务隔离级别与锁机制

三流程序员03
189 阅读10分钟

介绍

Mysql并发执行多个事务时可能会对相同的一批数据进行增删改查操作，可能出现脏写、脏读、不可重复读、幻读这些问题。

这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制，用一整套机制来解决多事务并发问题

事务

事务是一组操作要么全部成功，要么全部失败，目的是为了保证数据最终一致性

ACID属性

  • 原子性: 事务内的操作要么同时成功，要么同时失败。（undo log实现）
  • 一致性：使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现。
  • 隔离性：在事务并发执行时，他们内部的操作不能互相干扰（MVCC）
  • 持久性: 一旦提交了事务，它对数据库的改变就应该是永久性的(redo log)

事务隔离级别

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。 数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

-- 查看事务隔离级别，Mysql默认的事务隔离级别是可重复读
show variables like 'tx_isolation';

-- 设置隔离级别
set tx_isolation='REPEATABLE-READ';

读未提交

脏读：事务A读取到了事务B已经修改但尚未提交的数据 image.png

读已提交（RC）

不可重复读问题：事务A内部的同一条查询语句可能在不同时刻读出的结果不一致，隔离性差 image.png

可重复读（RR）

当前事务读取到的数据（所有表）不受别的事务影响，从始至终都一样 注意：更新数据时要使用当前读，否则会将别的事务的数据覆盖掉（脏写Lost Update）

UPDATE account set killer=killer+1 where name='亚索'; 
insert、UPDATE、delete 都是当前读

幻读:事务A读取到了事务B提交的新增数据 RR级别下，使用间隙锁(Gap Lock)，解决幻读问题

image.png

脏写：在查询完一次数据后，该数据被其他事务修改，这时候如果直接拿查询到的结果做运算，写入数据库，就会覆盖原有的数据

解决办法，当前读

  • 悲观锁：在数据库层面做操作update table set stock = stock - 20 where id=4,update是当前读；修改之后本数据读取的是最新数据
  • 乐观锁：加一个version的字段，update时比较version是否相等；（不能在RR级别下使用，因为事务里每次查询的version都一致）

image.png

串行化(Serializer)

隔离性最好，解决上面所有问题，缺点就是性能低 当多个事务对同一行数据读写时，只有一个事务可以操作，其他事务阻塞等待行锁（读操作时，阻塞其他事务的写操作，包括插入操作；写操作时，阻塞其他事务的读写操作）

image.png

RR和RC实现原理

MVCC实现读写并发：读的是快照历史数据，写的是最新数据，通过unlog实现（多个日志版本） RC,语句级快照 RR，事务级快照

为啥RC比RR性能高？

RR在多个读操作时如果需要保证数据在同一时间维度，就需要加事务，加了事务就会造成性能低 RC始终读最新提交的数据，加不加事务都一样，所以在不加事务的情况下，性能相对RR高

事务优化

大事务的影响

  • 并发情况下，数据库连接池容易被撑爆
  • 锁定太多的数据，大并发时会造成大量的阻塞和锁超时
  • 执行时间长，容易造成主从延迟
  • 回滚所需要的时间比较长
  • undo log膨胀
  • 容易导致死锁

事务优化

  • 将查询等数据准备操作放到事务外（RC下）
  • 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
  • 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
  • 更新等涉及加锁的操作尽可能放在事务靠后的位置
  • 能异步处理的尽量异步处理
  • 应用侧(业务代码)保证数据一致性（代码异常时执行回滚逻辑），非事务执行

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是需要用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

性能上

分为乐观锁和悲观锁

悲观锁：更新数据时加行锁（用sql更新数据），如果有多个事务更新同一行数据，会阻塞等待锁释放（适合写操作较多的场景）

乐观锁：保持乐观，认为自己在修改数据时，其他线程不会修改，所以不会对数据加锁；（CAS机制, 适合读操作较多的场景）

CAS：compare and swap ——比较并替换，加一个版本号字段，判断是否更新的是最新数据；如果不是，重新获取最新数据；（适合读操作较多的场景，因为写多的情况下，可能每次更新时版本号都不一样，会一直循环查,导致CPU狂飙）

操作类型上

分为读锁和写锁、意向锁

读锁和写锁互斥,读锁不互斥；写锁和写锁、读锁互斥。

读锁（共享锁，S锁(Shared)）：针对同一行数据，多个读操作可以同时进行而不会互相影响

//一般select不会加锁，lock in share mode，加读锁（串行化默认使用加读锁）
select * from T where id=1 lock in share mode

多个事务可以同时读取同一个资源，但不允许其他事务修改

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁；所有更新操作都会加写锁。

//for update 加写锁
select * from T where id=1 for update

意向锁（Intention Lock）：又称I锁，针对表锁，为了提高加表锁的效率 当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向锁主要分为：

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

操作粒度上

表锁

每次操作锁住整张表。开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景

--手动增加表锁 
lock table 表名称 read(write),表名称2 read(write); 

--查看表锁 
show open tables; 

--删除表锁 
unlock tables;
页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多。开销介于表锁和行锁之间，会出现死锁，并发度一般。

行锁

每次操作锁住一行数据。开销大，加锁慢（需要先找到对应行的数据）；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点：

  • InnoDB支持事务（TRANSACTION）
  • InnoDB支持行级锁

InnoDB的行锁实际上是针对索引加的锁（在索引对应的索引项上加标记），不是针对整个行记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁（RR级别下会升级为表锁，RC级别不会升级为表锁）

-- where 条件无索引，会升级为表锁
SELECT * from account where name='亚索' for update

RR级别下行锁升级为表锁的原因：

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时为了防止扫描过的索引被其它事务修改(不可重复读问题)或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，

总结

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。 简而言之，读锁和写锁互斥,读锁不互斥；写锁和写锁、读锁互斥。

间隙锁(Gap Lock)

RR级别下，锁的是两个值之间的空隙，对索引树的范围加锁；RC没有间隙锁，性能高的一个原因 RR级别下，在间隙范围内锁住一条不存在的记录就会锁住整个区间，不锁边界，解决幻读问题

数据记录为1，2，5，10，20
select * from account where id = 18 for update;  //(10,20)间加上间隙锁，不允许插入数据

select * from account where name = '亚索' for update;  //间隙锁针对主键索引，不使用主键会锁整张表
临键锁(Next-key Locks)

行锁和间隙锁的组合，在间隙锁的基础上，加了行锁，包括边界

select * from account where id > 2 and id <= 10 for update;

锁等待分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

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: 系统启动后到现在总共等待的次数 -------------------------------------（重要）

查看锁等待

-- 查看事务 
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

-- 查看锁等待详细信息 
show engine innodb status;

死锁：两个事务互相占用需要的资源，大多数情况mysql可以自动检测死锁并回滚产生死锁的事务，有些情况需要通过日志找到对应事务线程id，kill掉

锁优化

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少检索条件范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能低级别事务隔离

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第1天，点击查看活动详情

avatar
文章
阅读
粉丝