MySQL锁

MySQL锁的类型

加锁作用
当多个用户并发地存取数据时，在数据库中就可能会产生多个事务同时操作同一行数据的情况，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据的一致性（一种典型的并发问题——丢失更新）

InnoDB加锁机制

加锁机制 描述
乐观锁                假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务，在Innodb中 MVCC（多版本并发控制）使用乐观锁实现，在数据表中增加两个隐藏列。
悲观锁 假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁

InnoDB加锁粒度

加锁粒度 描述
行锁                     锁定的是索引记录。行锁就是索引记录锁，所谓的“锁定某个行”或“在某个行上设置锁”，其实就是在某个索引的特定索引记录（或称索引条目、索引项、索引入口）上设置锁，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高；只有查询的数据使用了索引才会加行锁。
页锁 InnoDB不支持；开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
表锁 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

InnoDB加锁类型

加锁类型 描述
共享锁（行锁）
 又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改;
加锁方式： select * from users WHERE id=1 LOCK IN SHARE MODE;
释锁：commit/rollback
排他锁（行锁） 又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对 数据行进行读取和修改，（其他事务要读取数据可来自于快照）
加锁方式：delete / update / insert 默认加上X锁；SELECT * FROM table_name WHERE … FOR UPDATE
释锁： commit/rollback
意向共享锁（表锁） 表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的，意向锁(IS、IX)是InnoDB数据操作之前自动加的，不需要用户干预
意向排他锁（表锁） 表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的。

读锁（共享锁） lock in share more：允许其他线程读，但不允许写
写锁（排它锁） for update：不允许其他线程进行读写

InnoDB加锁类型兼容性

  • 按照兼容性，如果不同事务之间的锁兼容，则当前加锁事务可以持有锁，如果有冲突则会等待其他事务的锁释放。
  • 如果一个事务请求锁时，请求的锁与已经持有的锁冲突而无法获取时，互相等待就可能会产生死锁。
  • 意向锁不会阻止除了全表锁定请求之外的任何锁请求。 意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。
排他锁（X） 意向排他锁（IX） 共享锁（S） 意向共享锁（IS）
排他锁（X） N N N N
意向排他锁（IX） N OK N OK
共享锁（S） N N OK N
意向共享锁（IS） N OK N OK

意向锁之间是互相兼容的，但是会与表级的共享锁或排他锁互斥。也就是说，如果一个事务已经持有表的共享锁或排他锁，那么其他事务就不能获取该表的意向锁；反之，如果一个事务已经持有表的意向锁，那么其他事务就不能获取该表的共享锁或排他锁。

意向锁的作用是为了在行级锁和表级锁之间进行协调，避免不必要的检查和冲突。例如，如果一个事务想要对整个表加共享锁，它只需要检查是否有其他事务持有该表的意向排他锁，而不需要检查每一行是否有排他锁；如果一个事务想要对某一行加排他锁，它只需要先获取该表的意向排他锁，然后再获取该行的排他锁。

如果MySQL中已经有其他事务具有意向排它锁了，那么另一事务能加排它锁吗？答案是：取决于具体情况。

  • 如果另一事务想要对整个表加排他锁，那么它会被阻塞，直到其他事务释放了意向排他锁。
  • 如果另一事务想要对某一行加排他锁，并且该行没有被其他事务加过排他锁，那么它可以先获取该表的意向排他锁（因为意向排他锁之间兼容），然后再获取该行的排他锁。
  • 如果另一事务想要对某一行加排他锁，并且该行已经被其他事务加过排他锁，那么它会被阻塞，直到其他事务释放了该行的排他锁。

InnoDB行锁算法

  • Record Lock: 单个记录上的锁。
  • Gap Lock: 间隙锁，锁定一个范围，但不包括记录本身
  • Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身(左开右闭)，RR级别通过Next-Key Lock机制来避免Phantom Problem（幻读问题）。

Next-Key Lock

  • 对于Insert语句
    若Insert语句包含该区间内键值（无论是否存在）都将导致阻塞
    若Insert语句包含区间下界值时，待插入记录的主键必须小于下界记录对应的主键值
    若Insert语句包含区间上界值时，待插入记录的主键必须大于上界记录对应的主键值
  • 对于update语句
    若update语句where条件包含给定键值，将阻塞
    若update语句where条件包含上下界键值，set字句不能包含被锁定区间的任意值，包含上下界
  • 对于delete语句
    delete操作只会阻塞在键值上。
    InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，另外一方面，是为了满足其恢复和复制的需要。
    很显然，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。

    当然，用户可使用下面两种方式显示地关闭间隙锁（Gap Lock）：
  1. 将事务的隔离级别设为READ COMMITTED
  2. 将参数innodb_locks_unsafe_for_binlog设置为1（目前已被弃用）
    注意：方式1显然破坏了事务的隔离性，会导致幻读的发生；方式2在replication中可能导致主从数据不一致。

MySQL是锁是如何实现的

  • LBCC（Lock-Base Concurrency Control）基于锁的并发控制；
  • MVCC（Multiversion Concurrency Control）多版本并发控制；

MVCC:有了锁，当前事务没有写锁就不能修改数据，但还是能读的，而且读的时候，即使该行数据其他事务已修改且提交，还是可以重复读到同样的值。这就是「MVCC，多版本的并发控制，Multi-Version Concurrency Control」

Innodb 中行记录的存储格式，有一些额外的字段：「DATA_TRX_ID和DATA_ROLL_PTR」。

字段 描述 备注
DATA_TRX_ID 数据行版本号 用来标识最近对本行记录做修改的事务 id
DATA_ROLL_PTR 指向该行回滚段的指针 该行记录上所有旧版本，在 undo log 中都通过链表的形式组织
PK 主键
other column 其他列

ReadView:

在每一条 SQL 开始的时候被创建，有几个重要属性：

  • 「trx_ids:」 当前系统活跃(未提交)事务版本号集合。
  • 「low_limit_id:」 创建当前 read view 时“当前系统最大「事务版本号」+1”。
  • 「up_limit_id:」 创建当前read view 时“系统正处于「活跃事务」最小版本号”
  • 「creator_trx_id:」 创建当前read view的事务版本号；

现在开始查询，一个 select 过来了，找到了一行数据。

  • 「DATA_TRX_ID <up_limit_id ：说明数据在当前事务之前就存在了，显示。」

  • 「DATA_TRX_ID >= low_limit_id：」说明该数据是在当前read view 创建后才产生的，数据不显示。

    • 不显示怎么办，根据 「DATA_ROLL_PTR 从 undo log 中找到历史版本，找不到就空。」

  • 「up_limit_id <DATA_TRX_ID」 <「low_limit_id ：就要看」隔离级别了。

RR级别的幻读:幻读通常针对的是 「INSERT,」 不可重复度则针对 「UPDATE 。」

事物 1 事物 2
begin begin
select * from dept
- insert into dept(name) values("A")
- commit
select * from dept
update dept set name="B"
select * from dept
commit

期望结果为:事务2的A并未被修改为B,实际上被事务1的update修改了;事务1的两次查询数据一致,不会查出事务2插入的记录

在MySQL的可重复读级别下，事务1查询全表记录后事务2插入一条数据并提交，事务1再次更新全表数据，是否可以会更新到事务2插入的记录？答案是：不一定。

这是因为在MySQL的可重复读级别下，查询操作和更新操作使用的一致性视图是不同的。查询操作使用的是快照读（snapshot read），即基于当前事务创建时的一致性视图来查询数据，不受其他事务的影响。而更新操作使用的是当前读（current read），即基于最新的数据来更新数据，会受到其他事务的影响。

具体来说，当事务1第一次查询全表记录时，它会创建一个一致性视图，该视图中包含了所有已经提交的数据，以及自己未提交的数据（如果有的话），但不包含其他未提交的数据。当事务2插入一条数据并提交时，该数据并不会进入事务1的视图中，因为事务1的视图已经固定了。所以当事务1再次查询全表数据时，它还是按照自己的视图来查询，而不会看到事务2插入的记录。

但是，当事务1再次更新全表数据时，它就不再使用自己的视图来更新，而是使用最新的数据来更新。

  • 如果事务2插入的记录没有被其他事务加锁或修改，那么事务1就可以更新到该记录；
  • 更新后的第三次查询可以查到事务2插入的记录:该行的DATA_TRX_ID已经更新为事务1的事务id了,所以可以查到
  • 如果事务2插入的记录已经被其他事务加锁或修改，那么事务1就不能更新到该记录，可能会发生锁等待或死锁。

MySQL 可重复读的隔离级别只解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，MVCC 对于幻读的解决时不彻底的。

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

  • T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
  • T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
  • T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。LBCC

InnoDB行锁升级为表锁的情况

  • where条件中的列没有索引时加锁

行锁是针对的索引进行加锁，未走索引时加锁自然是表锁

使用表锁的情况

  • 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；
  • 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

使用表锁需要注意几点：

  • 使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，表级锁不是 InnoDB 存储引擎层管理的，而是由其上一层 MySQL Server 负责的
  • 在用 LOCK TABLES 对 InnoDB 表加锁时需要注意，要将 AUTOCOMMIT 设置为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK_TABLES 隐含提交事务；COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加表级锁。

InnoDB避免死锁

  • 死锁常见的原因：
  1. 事务资源使用顺序
    Transaction 1: 更新表A -> 更新表B
    Transaction 2: 更新表B -> 更新表A
  2. 同一张表加锁顺序不一致
    事务A 更新数据的顺序 为1，2；
    事务B更新数据的顺序为2，1。
    这样更可能会造成死锁
  • 避免死锁
  1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
  2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
  3. 在删除操作和更新操作尽量使用 主键来进行操作。
  4. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
  5. 如果出现死锁，可以用mysql> show engine innodb status命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

更多MySQL事务相关内容见MySQL事务及事务隔离级别

参考原文:MySQL 可重复读隔离级别，完全解决幻读了吗？

posted @ 2020-07-04 20:51  Abserver  阅读(144)  评论(0)    收藏  举报