跳转至

MySQL事务及隔离级别与锁机制

事务ACID属性

  • 原子性(Atomicity)

事务是一个原子操作单元, 要符合不可分割原则, 即所有操作(CURD)要么全部成功要么全部失败

  • 一致性(Consistent)

事务从开始到结束, 数据必须保持一致性, 即所有操作都必须应用于数据, 以保持数据的完整性

  • 隔离性(Isolation)

每个事务互相隔离, 每个事务内部的数据互不影响, 具体是否受影响以及受影响范围可通过隔离级别调整

  • 持久性(Durable)

事务完成后, 应用于数据的修改是永久性不可丢失的

并发问题

  • 脏写, 也叫更新丢失(Lost Update)

多个事务对同一个数据操作, 即最后的更新覆盖了其他事务的更新

  • 脏读(Dirty Reads)

一个事务对数据进行了操作, 但尚未提交, 此时该数据处于不一致状态, 如果不加控制任由其他事务读取该数据, 这种现象就是脏读

  • 不可重读(Non- Repeatable Reads)

事务开始后, 重复读取某个数据, 发现结果发生变化, 即事务内部的相同查询语句在不同时刻读取的数据结果不一样, 不符合隔离性, 这种现象就是不可重复读

  • 幻读(Phantom Reads)

一个事务按照相同的条件重新读取之前检索过的数据, 发现了其他事务插入了满足其查询条件的新数据, 即某事务读取到了其他事务提交的新增数据, 不符合隔离性, 这种现象叫做幻读

事务隔离级别

不同的隔离级别可解决不同的并发问题, 如下表

隔离级别 脏读 不可重复读 幻读
读未提交(Read uncommitted) 是 是 是
读已提交(Read committed) 否 是 是
可重复读(Repeatable read) 否 否 是
可串行化(Serializable) 否 否 否

因为事务隔离是实质上就是使事务在一定程度上“串行”而非“并发”, 所以数据库的事务隔离级别越严格, 并发副作用越小, 代价越大

查看当前的事务隔离级别: show variables like 'tx_isolation';

设置当前的事务隔离级别: set tx_isolation='repeatable-read';

读未提交是read-uncommitted, 读已提交是read-commited, 可串行化是serializable

不同的session可以设置不同的隔离级别

MySQL默认的事务隔离级别是可重复读

脏读解释:

以脏读为例, 在两个session中均设置隔离级别是读未提交, 当session1中update某个数据, 但没有提交时, session2中可以看到session1中修改后的数据; 此时这个就是脏数据, 如果session2以这个脏数据为基准, 再次修改提交, 但是session1回滚了, 那么session2最终提交的数据是不对的

如果隔离级别是读已提交, 那么session2中无法看到session1中修改后的数据

如果隔离级别是可重复读, session1读出来数据后, session2修改数据(假设修改了字段number的值)并提交, 此时session1重复读取, 读到依旧是session2修改(cud操作)前的数据(MVCC机制), 但session1修改数据, 例如update a_table set number=number-1 where id=1;是正确的, 关键就在于number=number-1, number-1中的number会调用session2中修改后的最终数据, 具体看下面的解释

不可重复读解释:

关于 隔离级别 设置成 可重复读, 不可重复读 是 否, 比较绕, 意思就是现在可以重复读且重复读取的数据保持不变, 具体看下面的说明:

此时session1中对该数据操作, 并不会以你看到(session1读出来)的数据为基准, 而是以session2提交的数据为基准, 最终结果可以看到的可能不太一样, 这却是正确的

幻读解释:

依旧是两个session, 使用默认的可重复读隔离级别, 按照以下过程操作

  • session1中开启事务
  • session1中select查到1条数据
  • session2中开启事务后insert一条数据并提交
  • session1中select依旧是1条数据(读取历史版本)
  • session1中update id=2的数据显示成功(读取真正的版本, 已经有第2条数据了)
  • session1中再次select就查到了2条数据
  • 证明仍未解决幻读问题

想解决这些并发的副作用, 把隔离级别设置成可串行化, 在此级别下, select操作也会加锁, 就解决了幻读, 但这也导致了性能低, 基本不用

注意, 可串行化可能加表锁也可能加行锁, 具体什么锁取决于你的sql语句, 例如 select * from a_table; 是表锁, select * from a_table where id=1; 是行锁

锁分类

  • 从性能层面上分为乐观锁和悲观锁
  • 从数据层面上分为读锁和写锁(均属于悲观锁)
  • 读锁, 也叫共享锁或者S锁(Shared), 针对同一个数据可同时进行读操作, 不会互相影响
  • 写锁, 也叫排它锁或者X锁(eXclusive), 当前事务针对某数据的写操作完成之前, 其他事务无法对该数据读或者写
  • 加读锁时, 任何session都可读该表, 当前session插入或更新被锁的表会报错, 其他session的插入或更新操作则会等待
  • 加写锁时, 当前session可对该表进行增删改查操作, 其他session对该表的任何操作都被阻塞
  • 总结来说, 读锁会阻塞写但不会阻塞读, 写锁会阻塞读和写
  • 从颗粒度层面上分为表锁和行锁
  • 表锁, 给整张表加读锁或写锁
  • 行锁, 给某张表的某一张行加读锁或写锁

乐观锁从本质上讲不是锁, 默认操作不会有冲突，在update的时候再去检查冲突, 例如:

begin;
select number from product where id=1; //number等于100
update product set number = 150 where id=1 and number = 100;
commit;

即, 更新时比对原先的数据(判断number=100), 若没有发生变化(表示未收到其他事务影响), 即可进行操作

表锁的开销小加锁快, 不会出现死锁, 锁定颗粒度大, 发生锁冲突概率高, 并发程度低, 一般的用途是, 迁移数据库时, 对所有表增加一个读锁, 防止新数据写入, 然后安心迁移

相对于表锁, 行锁的开销大加锁慢, 可能会出现死锁, 锁定颗粒度小, 出现冲突概率低, 并发程度高

原因就是表锁只需找到这张表, 行锁还需要找到行, 所以开销大加锁慢, 且表锁是锁整张表, 所以不会发生死锁

关于不同的引擎的锁的总结:

  • MyISAM执行select前会自动给涉及的表加读锁
  • MySIAM执行insert、update和delete时会自动给涉及的表加写锁
  • InnoDB执行select时, 一般情况下, 因为有mvcc机制(只针对读已提交和可重复读这两个隔离级别起作用)不会加锁
  • InnoDB执行insert、update和delete时会自动给涉及的表的行加写锁

锁的扩展

上面提到设置隔离级别为可串行化可解决幻读问题, 但此隔离级别导致性能低下,

除此之外, 将隔离级别设置为可重复读时使用间隙锁(Gap Lock, 默认启用无需任何语法)也可解决某些情况下的幻读问题

为啥rr隔离级别有间隙锁, rc和ru级别没有间隙锁?

因为rr级别是可重复读, 可重复读是什么意思? 就是相同的sql重复读取保证数据不变, 例如 where id < 10, 间隙锁就保证不会发生插入、修改或删除数据的操作, 保证了相同的sql重复读取数据不变

rc和ru级别本身就是不可重复读的, 也就是说相同的sql重复读取的结果可能不一样, 反正数据是会变的, 随便插入、修改或删除, 所以不需要间隙锁

间隙锁是锁住两个值之间的空间, 例如表中有5条数据, id分别是1、2、3、10、100

那么间隙就是(3,10), (10,100), (100, 正无穷)

比如session1中执行update操作的条件是 where id > 5 and id < 50 时, 其他session无法在这个范围所包含的所有行记录(5~50)以及记录所在的间隙((3, 10)和(10, 100))里插入或修改任何数据

即此时session2中insert id=4 或者 id=30 或者 id=90 都会处于阻塞

同时, 上面在session2中插入100也会阻塞, 所以实际上阻塞的不是(3,10)和(10,100), 而是(3,10]和(10,100], 那么实际加锁的区间就是(3,100], 这个也叫临键锁(Next-key Locks)

锁主要是加在索引上, 如果对非索引字段更新, 行锁可能会升级为表锁

证明索引很重要!!!

锁的分析

锁表

// 对表 table1 加读锁
lock table table1 read;
// 对表 table2 加写锁
lock table table2 write;

查看上锁的表

show open tables; // in_use 为 1 表示该表被上锁

解锁表

unlock tables;

行锁分析

show status like 'innodb_row_lock%';

Innodb_row_lock_current_watis: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定的总时长

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_watis: 系统从启动到现在总共等待的次数

当等待此时很高, 且每次等待时间不短, 需要分析, 通过以下语句分析:

-- 查看所有事务
select * from information_schema.innodb_trx;
-- 查看涉及到锁的事务
select * from information_shcema.innodb_locks;
-- 查看等待锁的事务
select * from information_shcema.innodb_lock_waits;
-- 释放锁, trx_mysql_thread_id从innodb_trx表中得到
kill trx_mysql_thread_id
-- 查看死锁日志
show engine innodb status\G;

锁的优化

  • 尽可能使用低级别事务隔离
  • 尽量用过索引检索数据, 避免无索引行升级为表锁
  • 合理设计索引, 尽量缩小锁的范围, 即减少检索条件范围, 避免间隙锁
  • 尽量控制事务大小, 减少锁定资源量和时间长度
  • 尽量将涉及事务加锁的sql放到最后执行

MVCC

MVCC(multi-version concurrency control, 多版本并发控制)机制, 仅对读已提交和可重复读两个隔离级别有效, 有以下特性:

  • 只针对innodb引擎的数据库有效
  • 每个数据库只有一个版本链(可以理解为串行起来的表中的每一行的数据), 通过undo日志实现
  • 每条链中包含一个事务ID的字段(每一行的数据+事务ID字段)
  • 事务开启(begin)时不会生成事务ID, 当执行CRUD任一sql时才生成真正的事务ID
  • 生成事务ID的同时会生成每个事务自己独有的一致性视图read-view
  • 读已提交隔离级别下的read-view在每次执行sql时都会重新生成, 而可重复读隔离级别在commit之前不会重新生成(保证了可重复读)
  • select语句检索的数据库来源于read-view, 是通过比对当前事务ID与版本链中的ID及一系列规则获取