64 mysql 的 表锁

前言

我们这里来说的就是 我们在 mysql 这边常见的 几种锁

行共享锁, 行排他锁, 表意向共享锁, 表意向排他锁, 表共享锁, 表排他锁

我们前面了解了行共享锁, 行排他锁, 表意向共享锁, 表意向排他锁 等等相关

我们这里 来看一下 表共享锁, 表排他锁 的获取, 以及 和 其他表级别锁的冲突的处理

 

需要设置 auto_commit 为 0, 否则不会走 表锁 的相关业务流程

 

 

表排他锁

我们这里调试 sql 如下 这里会先获取 t_user_02 的表排他锁, 然后再释放掉 

begin;
lock tables t_user_02 write;
unlock tables;
commit;

 

我们这里重点关注 “lock tables t_user_02 write;” 的执行流程 

 

表锁 mysql层 到 innodb引擎层 的适配是这里的 handler.cc 中进行适配的 

到下图断点这里是 innodb 的处理, 验证了 lock 命令, autocommit 为 false 以及一些其他的条件, 然后 row_lock_table_for_mysql 是整体的获取表锁的业务流程, 诸如 获取到锁之后继续往下走, 没有获取到锁之后 wait, 重试 等等

 

这里就是获取表锁的宏观流程了, 如果获取到了 则直接返回 

如果没有获取到, 则挂起当前线程, 等待唤醒, 唤醒之后 进行重试

 

获取表锁的具体实现如下, 和之前的获取 表意向共享锁, 表意向排他锁 的情况类似

我们这里重点看一下 表级别锁的兼容机制, 这是影响到能否获取到锁的关键 

 

我们这里将问题简单化, 暂时不考虑 matrix 中的 自增长锁 的处理

表意向锁 和 表意向锁 之间是相互兼容的 

表意向锁 和 表锁 之间兼容如下, 表意向共享锁 和 表共享锁 兼容, 其他的三种情况互不兼容 

表锁 和 表锁 之间的兼容如下, 表共享锁 和 表共享锁 兼容, 其他的三种情况互不兼容 

 

对于我们这里 获取表排他锁的场景下面, 只有无锁状态, 我们可以获取成功, 但凡有 其他事务持有 表意向共享/排他锁, 表共享/排他锁 当前事务的获取 表排他锁 都会失败

对于获取 表共享锁 的场景下面, 只有无锁状态, 其他事务获取了表意向共享锁 或者 表共享锁 的情况下面, 我们可以获取锁成功, 但凡有其他事务持有 表意向排他锁, 表排他锁, 当前事务的获取 表共享锁 都会失败

 

 

表共享锁

我们这里调试 sql 如下 这里会先获取 t_user_02 的表共享锁, 然后再释放掉 

begin;
lock tables t_user_02 read;
unlock tables;
commit;

 

这里的获取 表共享锁 的流程和前面 获取 表排他锁 的流程一样, 只是 锁的兼容有一些 差异

 

我们这里将问题简单化, 暂时不考虑 matrix 中的 自增长锁 的处理

表意向锁 和 表意向锁 之间是相互兼容的 

表意向锁 和 表锁 之间兼容如下, 表意向共享锁 和 表共享锁 兼容, 其他的三种情况互不兼容 

表锁 和 表锁 之间的兼容如下, 表共享锁 和 表共享锁 兼容, 其他的三种情况互不兼容 

 

对于我们这里 获取表共享锁的场景下面, 只有无锁状态, 其他事务获取了表意向共享锁 或者 表共享锁 的情况下面, 我们可以获取锁成功, 但凡有其他事务持有 表意向排他锁, 表排他锁, 当前事务的获取 表共享锁 都会失败

对于 获取表排他锁的场景下面, 只有无锁状态, 我们可以获取成功, 但凡有 其他事务持有 表意向共享/排他锁, 表共享/排他锁 当前事务的获取 表排他锁 都会失败

 

 

表排他锁阻塞的 N 种方式

表意向排他锁 和 表排他锁 的阻塞方式基本上是一样的, 这里不多赘述 

假设我们这里尝试模拟 各种阻塞的方式, 事务1先进行执行, 然后事务2尝试获取表排他锁, 产生阻塞 

但是这些阻塞 是还没有到获取表锁 之前的目标表的 MDL元数据锁的阻塞 

事务2 这边执行固定的 sql 语句如下 

begin;
lock tables t_user_02 write;
unlock tables;
commit;

 

事务1获取 表意向共享锁 导致 事务2 获取MDL元数据锁 阻塞 

begin;
select * from t_user_02 where id = '1' lock in share mode;
-- sleep 10min
commit;

 

事务1获取 表意向排他锁 导致 事务2获取MDL元数据锁 阻塞

begin;
select * from t_user_02 where id = '1' for update;
-- sleep 10min
commit;

 

事务1获取 表共享锁锁 导致 事务2获取MDL元数据锁 阻塞

begin;
lock tables t_user_02 read;
-- sleep 10min
unlock tables;
commit;

 

事务1获取 表排他锁锁 导致 事务2获取MDL元数据锁 阻塞

begin;
lock tables t_user_02 write;
-- sleep 10min
unlock tables;
commit;

 

 

表共享锁阻塞的 N 种方式

假设我们这里尝试模拟 各种阻塞的方式, 事务1先进行执行, 然后事务2尝试获取表共享锁, 产生阻塞 

但是这些阻塞 是还没有到获取表锁 之前的目标表的 MDL 元数据锁的阻塞 

事务2 这边执行固定的 sql 语句如下 

begin;
lock tables t_user_02 read;
unlock tables;
commit;

 

事务1获取 表意向排他锁 导致 事务2获取MDL元数据锁 阻塞

begin;
select * from t_user_02 where id = '1' for update;
-- sleep 10min
commit;

 

事务1获取 表排他锁锁 导致 事务2获取MDL元数据锁 阻塞

begin;
lock tables t_user_02 write;
-- sleep 10min
unlock tables;
commit;

 

 

表锁的 阻塞 和 唤醒

假设我们这边 事务1 执行 sql 如下 

begin;
select * from t_user_02 where id = '2' for update;
-- sleep 10min
commit;

 

事务2 执行 sql 如下, 然后到 “select * from t_user_02 where id = '2' for update;

” 的时候, 事务会阻塞, 然后 之后手动 commit 事务1, 可以看到 唤醒的流程

begin;
select * from t_user_02 where id = '2' for update;
-- sleep 10min
commit;

 

这个就是通过线程的相关 api 进行 wait 和 notify 了 

wait 这边这边主要是基于 pthread_cond_wait 来进行线程的挂起处理 

 

手动 commit 事务1, thr 是目标锁 等待队列中取出的一个待唤醒的线程

然后设置会 设置 pthread_cond_wait 中的等待的条件, 以达到唤醒目标线程的效果 

 

来到目标线程这边, 目标线程 被唤醒了, 然后继续走后面的流程 

 

 

行锁, 表锁, 元数据锁 的查看

行锁的阻塞信息, 可以再 INNODB_LOCKS, INNODB_LOCK_WAITS 中查看 

select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS;

 

构造一个 事务1 获取 id 为 2 的记录的行共享锁, 事务2 获取 id 为 2 的记录的行排他锁, 造成的阻塞 

从这里可以看到 两个事务尝试获取 t_user_02 表的 第3条 记录, 一个获取行共享锁, 一个获取行排他锁

结合等待信息来看, 可以看出的是 获取 行排他锁 的事务在等待 持有 行共享锁 的事务

 

show processlist 可以看到等待的线程, 以及阻塞的 sql 

工作线程一般是靠后面的线程, 可以推断出 持有锁的线程是 30号, 阻塞的是 31号 线程

 

 

表锁的阻塞信息, 可以通过如下命令来查看

show open tables where in_use > 0; 

 

执行结果如下, 可以推断的是 t_user_02 的表锁被一个事务持有, 具体的是读锁还是写锁分辨不出来, 只能通过 实际的业务锁获取来判断

如果业务这边获取的是读锁, 则表示持有的是写锁, 如果业务这边获取的是写锁, 则判断不了持有的是读锁还是写锁 

 

show processlist 可以看到等待的线程, 以及阻塞的 sql 

工作线程一般是靠后面的线程, 可以推断出 持有锁的线程是 30号, 阻塞的是 31号 线程

 

 

元数据锁 这边一般只有通过 show processlist 中可以看出了 或者 mysql 服务器的堆栈信息

比如如上 表锁的例子

元数据锁在大多数的命令都会获取, 但是生命周期不太一样 

比如 “lock tables t_user_02 read/write;” 类型的指令, 是先获取 表的元数据锁, 然后再执行业务处理, 再释放 表的元数据锁

比如 “select * from t_user_02 where id = '2' for update;” 类型的指令, 是先获取 表的元数据锁, 然后释放 表的元数据锁, 最后再执行业务处理 

因此 构造元数据锁阻塞的方式可以由 事务1 获取表锁, 事务2 获取表锁, 或者行锁都会阻塞, 事务2 阻塞是阻塞在获取 表元数据锁 的地方

 

 

完