MySQL Lock/Transaction

Posted on

MySQL Lock 锁机制是为了解决并发问题，而关键问题就是保证事务。MySQL5.5+版本将默认引擎从 MYISAM 换为 InnoDB 可以高效的支持事务。

用锁保证事务

  • 示例 1：
    1. A 从银行转账 100 给 B；
    2. 同时 C 从 A 的账户取走 100；

其中 1 是由两个动作组成的：A 账户减少 100、B 账户增加 100。如果 C 的动作夹在中间执行，就可能造成最终错误的结果。

  • 示例 2:
    1. 统计当前人数，然后做一些事情，最后按照人数写入一个值
    2. 在 1 执行过程中，新增一个人。

2 的操作可能引起 1 的结果错误。

事务的四大特性 （ACID）

  1. 原子性（Atomicity） 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性（Consistency）事务前后数据的完整性必须保持一致。 这里的一致是语义而不是语法，是由业务逻辑决定的，比如用户购买商品后商品表数据增加、剩余金额表数据减少。
  3. 隔离性（Isolation）多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间的数据要相互隔离。
  4. 持久性（Durability）一个事务一旦被提交，它对数据库中的数据改变就是永久性的。接下来的其他操作或故障不应该对其有任何影响。

ACID 思考

  • MySQL 如何保证一致性？

    • 在数据库层面，必须保证 A(原子性)I(隔离型)D(持久性) 之后，才能实现一致性。前面三个是手段，一致性是目的。
    • 在应用层面，通过代码逻辑判断事物是否有效，是提交还是回滚。

  • MySQL 如何保证原子性？ Innodb 的 undo log 是实现原子性的关键，undo log 里记录了变更操作(delete/update/insert)时对应的回滚信息， 当事务回滚时能够撤销所有已执行的 SQL 语句。

  • MySQL 如何保证持久性？ 利用 Innodb 的 redo log。 Mysql 是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

    • 为什么不能在提交前把数据刷入磁盘？
      • 磁盘存储单位为页(16KB)，如果稍微修改一个字节就刷到磁盘 16KB 会大大浪费性能
      • 一个事务可能涉及多个页的修改，而这些页可能是不相邻的也就是随机 IO，而随机 IO 速度比较慢
    • 解决方案： 在数据修改中除了修改内存数据，也将操作记入 redo log 中，当事务提交的时候，会将 redo log 日志进行刷盘(redo log 一部分在内存中，一部分在磁盘上)。 当数据库宕机重启的时候，会将 redo log 中的内容恢复到数据库中，再根据 undo log 和 binlog 内容决定回滚数据还是提交数据。
    • redo log 进行刷盘比对数据页刷盘效率高，原因如下
      • redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快
      • redo log 是一直往末尾进行追加，属于顺序 IO。效率显然比随机 IO 来的快
    • redo log 和 binlog 的关系
      • bin log 是 server 层直接根据请求写入的记录，redo log 是引擎层写入的数据

  • MySQL 如何保证隔离性？ 在隔离级别为Repeatable Read下结合LBCC+MVCC是可以实现隔离性的，具体后面说明。

可能引发的问题

并发执行事务操作，可能造成下面问题：

  • 脏读(Dirty Read) 事务 A 读到了事务 B 未提交的数据。
  • 不可重复读(Non-repeatable Read) 事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到 row1，但列内容发生了变化。
    • 不可重复读对应update和delete操作
  • 幻读(Phantom) 事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到两行记录 row1 和 row2。读到了原本不存在的新记录。
    • 幻读对应insert操作
  • 丢失更新 事务 A B 两次更新顺序到来，最后结果应该是 B，但是在并发更新结束后，结果是 A，B 被覆盖了。

四种隔离级别及其特点

隔离级别 存在脏读 存在不可重复读 存在幻读
Read Uncommited(未提交读) Y Y Y
Read Commited(已提交读) N Y Y
Repeatable Read(可重复读) N N Y
Serializable(串行化) N N N
  • 随着从上到下隔离级别越来越严格，同时并发性能越来越差。Serializable无法实现并发，所以实际中不使用。
  • MySQL 的默认隔离级别是Repeatable Read(可重复读)

事务隔离级别的实现方式

  • LBCC(Lock-Based Concurrent Control)

  • MVCC(Multi-Version Concurrent Control)

  • InnoDB 是将上面两种方式结合实现的事务

MySQL 事务相关基本操作

  • AutoCommit(自动提交) show variables like 'autocommit';这个配置参数是默认打开的，表示每个 SQL 语句自动提交，说明本条 SQL 是符合事务的。
  • 手动控制 在事务开头begin、最后commit或rollback，可以手动控制事务的执行。
  • 设置隔离级别 set session transaction isolation level repeatable read;

锁的分类

  • LBCC是 InnoDB 实现事务的主要方式

  • MySQL 的锁都是可重入锁，即一个事务自己的锁可以重复申请，不会被阻塞

表锁与行锁的区别：

  • 锁定粒度：表锁 > 行锁，表锁范围大
  • 加锁效率：表锁 > 行锁，表锁加锁快
  • 冲突概率：表锁 > 行锁，

  • 并发性能：表锁 < 行锁，行锁并发性更好

  • 示例： 预定旅店时，如果一间一间定，比较复杂；整体包旅店，可以快速知道结果；

根据操作类型分为：

  • 读锁(共享锁、S 锁(Share Lock))
    • 对同一个数据，多个读操作可以同时进行，互不干扰。
    • 读锁可以先锁定数据，避免数据在事务操作结束前被修改
    • 开启读锁的语句select * from ... where ... lock in share mode;
  • 写锁(互斥锁、排他锁、X 锁(Exclusive Lock))
    • 如果当前操作没有完毕，则无法进行其他的读/写操作。
    • 开启写锁的语句select * from ... where ... for update; 注意，这里开启写锁的前提是已经开启了事务，否则不会开启写锁
    • insert into、update、delete from都可以触发写锁

  • 意向锁(Intention Lock) 意向锁是表锁，是自动创建的，不是手动创建的。意向锁用于加表锁时直接判断是否有已锁定的情况，避免逐行遍历判断效率低。 意向锁分为两种：

    • 意向共享锁(IS 锁(Intention Share Lock)) 事务准备给行加共享锁之前，要先能获取 IS 锁
    • 意向排他锁(IX 锁(Intention Exclusive Lock)) 事务准备给行加排他锁之前，要先能获取 IX 锁
  • 一般情况下执行的写操作(没有begin)delete/update/insert都会触发一次写锁
  • 一般情况下执行的select不会触发读锁或写锁，只会读最新已提交的

根据操作范围分为：

  • 表锁 对一张表加锁。如 MyISAM 存储引擎使用表锁，开销小、加锁快、无死锁；锁范围大，容易发生冲突，并发度低。
  • 行锁 对一条数据加锁。如 InnoDB 存储引擎默认使用行锁，开销大、加锁慢、容易发生死锁；锁范围较小，不易发生锁冲突，并发度高。
    • InnoDB 也有表锁

  • 页锁

  • 操作行锁要通过主键或唯一索引，否则会退化为间隙锁
  • 如果没有索引，行锁会转为表锁。例如，在索引失效时，行锁会退化为表锁。

根据算法(锁区间)分为：

  • 记录锁(Record Lock)
    • 具体锁定某行，如 1 5 10
    • 等值查询，精准匹配
    • select * from ... where id=1 for update;
    • 记录锁要命中主键或唯一索引
  • 间隙锁(Gap Lock)
    • Gap Lock 只存在与 RR(Repeatable Read)隔离级别
    • 锁定具体行之间的开区间，如 (-∞, 1) (5, 10) (10, +∞)，不包含记录本身
    • select * from ... where id>5 and id<10 for update;
    • 间隙锁是以当前数据库状态划分间隙的，所以锁定的实际范围可能大于 SQL 语句范围。 比如数据库里有两条数据 1 10，那么会自动划为三段间隙：(-∞, 1)、(1, 10)、(10, +∞)。 如果执行 SQLselect * from ... where id>15，这时 id==12 的元素虽然不在id>15范围，但是也被锁住了，真正锁住的间隙是(10, +∞)

  • 临键锁(Next-Key Lock)

    • 锁定左开右闭区间，如 (-∞, 1] (5, 10] (10, +∞)，其中闭区间端是一个记录
    • 临键锁的功能是在 RR 级别防止幻读
    • 当间隙锁范围内命中了记录时，就会触发临键锁
    • 假设有数据 1 5 10 15，执行 SQLselect * from .. where id>1 and id<8。这时由于 5 在间隙锁内被命中，实际锁住的区间是：(1,10]，注意 10 也被锁住了
  • 只使用唯一索引查询，并且只锁定一条记录时，innoDB 会使用行锁
  • 只使用唯一索引查询，但是检索条件是范围检索，或者是唯一检索然而检索结果不存在（试图锁住不存在的数据）时，会产生 Next-Key Lock。
  • 使用普通索引检索时，不管是何种查询，只要加锁，都会产生间隙锁。
  • 同时使用唯一索引和普通索引时，由于数据行是优先根据普通索引排序，再根据唯一索引排序，所以也会产生间隙锁。

MVCC 机制

当输入begin后第一次执行select时，对数据库进行一个”快照”。类似乐观锁的执行过程。

  • “快照”的实现机制

    1. InnoDB 在每个事务第一次select时生成一个”事务 Id”(transaction_id)，transaction_id 保证严格自增
    2. 数据库中每行数据有多个版本，每次更新都会生成一个版本，并且版本号(row_trx_id)与事务 Id(transaction_id) 是对应的
    3. 根据当前的隔离级别，在 select 时读取不同的版本号的数据
    4. 默认情况下会读取最后提交的一个版本，但是对于事务来说，他会信自己最后提交的值，否则信第一次快照时的值

  • 每个事务有三种相关的 Id:

    • transaction_id 事务中第一次select时由数据库上一个 id 自增产生唯一 id，表示这次事务的唯一标识
    • row_trx_id 一行数据可以同时有多个修改记录，每个记录有一个row_trx_id，表示哪个transaction_idcommit 的数据。
    • up_limit_id 在事务进行中，每条本次事务相关记录会有一个up_limit_id，表示本条记录应该信哪个row_trx_id。up_limit_id的默认值是快照时，最后一个transaction_id(未创建当前事务 id 前)

  • 数据的读取方式，有两种：

    • 快照读 读取数据时，按照事务起始(第一次 select)的up_limit_id读数据，就是快照读。 如果事务操作过程中，读取数据之前没有更新数据，则能够保证都是快照读，保证不会发生幻读
    • 当前读 如果事务过程中本次事务对其做过修改，则对应数据的up_limit_id会被更新为当前transaction_id，之后会读取自己最新的修改结果(自己的修改可能是基于别人已提交修改的)，这叫做当前读

  • RR 隔离级别下

    • 如果在 begin 后，第一次读取某行值的时候会记录一个快照，之后如果没有更新该字段，则一直保持同一个值。
    • 当中间发生更新动作时，会自动获取结合了别人已提交的最新值(up_limit_id 更新)。
      • 在更新过程中，尽量使用原有变量名进行增、减操作，避免将数据取到变量里再写入，可能引起数据不一致问题。

加锁 SQL

表锁

  • lock table t1 read/write 加一个表级的读/写锁
  • unlock tables 释放锁，也可以用事务解锁
  • show open tables 查看表的加锁数量
  • show status like '%Table%' 查看表锁情况，其中Table_locks_immediate是立即可加锁的表数量，Table_locks_waited是需要等待的表锁数。
    • 通常用Table_locks_immediate/Table_locks_waited作为参考数据，如果 > 5000 建议采用 InnoDB，否则用 MyISAM 引擎。

行锁

  • 通常增删改是通过事务来加锁的
  • 可以在查询语句后面加for update开启一个行写锁，如:select * from t1 where id = 1 for update;
  • 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 等待次数

锁的效果

表锁

  • 如果某 session 对 A 表加读锁，则可以对 A 表进行读操作、不可以写操作，当前会话不可以对其他表进行读/写操作。
    • 这时另一个 session 对 A 表可以读，写时会等待，对其他表可以读/写操作。
  • 如果某 session 对 A 表加写锁，则可以对 A 表进行任何操作，但是不可以对其他表进行操作。
    • 这时另一个 session 对 A 表任何操作将等待。

锁的流程

MyISAM 在执行查询语句(select)之前，会自动给涉及的所有表加读锁；在执行增删改之前，会自动给涉及的表加写锁。

  • 如果发生死锁，可以手动解除锁或者设置锁超时时限，之后再去分析解锁 log 就可以知道死锁原因

死锁的解除

  1. 生死锁时，调用者线程会被卡住，新的请求会被返回Deadlock found when trying to get lock错误。
  2. 这时，可以用show processlist;列出请求进程列表，然后找到sleep状态的进程用kill 123;杀掉即可
  3. 之后分析死锁日志就可以查找死锁直接原因

mysql 8.0 新特性

NOWAIT 以及

NOWAIT 表示当无法获取到锁时直接返回错误，而不是等待

select * from ... for update nowait;

  • 在已被别人锁时会立刻返回错误

SKIP LOCKED

SKIP LOCKED 表示忽略那些已经被其他 session 占有行锁的记录。

最佳实践

  • 关于乐观锁的用法 对于”超卖”问题，如果用互斥锁将商品数量完全锁住再操作，实际上是串行的，效率太低。 可以在执行写入后，做一次判断，如果发生”超卖”了(商品数为负数)，则回滚当前操作即可。