网站开发中 视频播放卡,做soho建立网站,公司网络推广网站,建站公司费用MySQL锁#xff08;二#xff09;表锁与行锁测试 上篇文章我们简单的了解了一大堆锁相关的概念#xff0c;然后只是简单的演示了一下 InnoDB 和 MyISAM 之间 表锁 与 行锁 的差别。相信大家还是意犹未尽的#xff0c;今天我们就来用代码说话#xff0c;实际地操作一下二表锁与行锁测试 上篇文章我们简单的了解了一大堆锁相关的概念然后只是简单的演示了一下 InnoDB 和 MyISAM 之间 表锁 与 行锁 的差别。相信大家还是意犹未尽的今天我们就来用代码说话实际地操作一下看看如何进行手动的加 表锁 与 行锁 并进行一些相关的实验测试。 手动锁表 首先来看 锁表 相关的操作。一般来说我们手动锁表大部分情况下是为了增加从库或者进行数据迁移的时候来使用。在这些业务场景中我们要保证从库在建立同步的时候主库不会出现新的数据因此往往用得最多的就是直接 读锁 。这样就可以保证在主库可读的情况下不会有新的数据写入。 -- 客户端1
mysql LOCK TABLES test_user2 READ;
Query OK, 0 rows affected (0.00 sec)-- 客户端2
mysql LOCK TABLES test_user2 READ;
Query OK, 0 rows affected (0.00 sec)-- 客户端2
mysql LOCK TABLES test_user2 WRITE;
-- 等待 在上面的测试语句中我们让 客户端1 锁住了一张表这个时候客户端2 也是可以再加一个读锁的还记得之前讲过的吗S 锁是可以共享的。但是接着我们又让 客户端2 加一个 写锁 这个时候就无法正常加了。也就是说S 与 X 是互斥的有一个拿到读锁之后写锁就没办法再加上了只能等 客户端1 的锁释放之后才能进行操作。这时你也可以试试更新、删除、插入一条数据看看能不能成功。要注意我们现在是锁的整表哦。 接下来我们就来试试为整张表锁上 写锁 。 -- 客户端1
mysql LOCK TABLES test_user2 WRITE;
Query OK, 0 rows affected (0.00 sec)-- 客户端2
UPDATE test_user2 SET username fff WHERE id 1212121;
-- 等待
mysql LOCK TABLES test_user2 READ;
-- 等待 当我们上了 写锁 之后更新肯定是不行了然后再尝试上一个 读锁 也是不行的哦。要查看表上锁的情况我们可以通过下面这个命令查看 mysql SHOW OPEN TABLES WHERE In_use 0;
--------------------------------------------
| Database | Table | In_use | Name_locked |
--------------------------------------------
| blog_test | test_user2 | 1 | 0 |
--------------------------------------------
1 row in set (0.00 sec) In_user 字段大于 1 的表示的就是这张表正在使用也就是有事务或者客户端锁定了这张表。解锁语句就不用我多说了吧把 LOCK 换成 UNLOCK 就可以啦。但是 UNLOCK 不能针对某一张表而是使用 UNLOCK TABLES; MySQL 会自动进行解锁释放。 全局锁 除了单独锁一张表之外我们还可以锁一个库中所有的表。很简单就是上面锁表的语句不加表名即可。这个大家可以自己尝试一下我们接着说另一个全局锁的功能它锁的是整个 MySQL 实例也就是说连库都包进去了。 FLUSH TABLES WITH READ LOCK; 一般这种锁就是做全量的数据备份或者迁移时会使用。不过在备份的时候我们其实还可以通过别的方式不用加锁来实现这个我们将来学习备份相关的内容时再说。 行锁及意向锁 上篇文章中我们已经介绍过 意向锁 相关的知识也了解到在加 行锁 的时候也会为整个表加一个 意向锁 真实情况是怎样的呢我们用例子来看下。 -- 共享锁及意向共享锁
mysql begin;
mysql SELECT * FROM test_user2 WHERE id 1212121 LOCK IN SHARE MODE;-- 查看锁信息
mysql SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| blog_test | test_user2 | NULL | TABLE | IS | NULL |
| blog_test | test_user2 | PRIMARY | RECORD | S,REC_NOT_GAP | 1212121 |
----------------------------------------------------------------------------- 在这里为了演示方便我们直接上了一个 读锁 也就是使用 LOCK IN SHARE MODE 来在事务中启用一个 共享锁 。然后我们就可以查询 performance_schema.data_locks 这个系统表中相关的信息。可以看到返回的信息中有两行数据第一条数据中的 lock_mode 字段显示的是就是一个 IS lock_type 字段显示的是 TABLE 也就是表级别的一个 意向共享锁 。没错吧确实是在上锁的时候会加 意向共享锁 吧。 第二条数据中lock_type 的 RECORD 表示的这是一条记录锁也就是 行锁 后面的 lock_mode 中有两个内容S 表示共享锁REC_NOT_GAP 表示是没有 GAP 锁这个东西我们放到 间隙锁 的文章中再进行说明。 对于上面的 意向共享 S 锁 来说我们可以继续加表锁不过只能加 读锁 无法加 写锁 。 -- 可以加读锁
mysql LOCK TABLES test_user2 READ;
Query OK, 0 rows affected (0.00 sec)-- 无法加写锁等待
mysql LOCK TABLES test_user2 WRITE; 接下来我们再看看 排它锁 的加锁情况你可以继续使用 SELECT ... FROM FOR UPDATE 这种形式也可以直接使用 UPDATE 语句在这里我们就使用 UPDATE 语句来演示。 -- 排它锁及意向排它锁
mysql begin;
mysql UPDATE test_user2 SET name fff WHERE id 1212121;-- 锁情况
mysql SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;
-----------------------------------------------------------------------------
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------------
| blog_test | test_user2 | NULL | TABLE | IX | NULL |
| blog_test | test_user2 | PRIMARY | RECORD | X,REC_NOT_GAP | 1212121 |
------------------------------------------------------------------------------- 无法加锁
mysql LOCK TABLES test_user2 READ; 很明显加了 排它锁 之后意向锁也就变成了 IX 行锁也是显示为 X 锁了。这个时候给整个表加任何锁都不行了。 行锁更新两条不同的数据 行锁的优势是什么当然就是可以同步地更新不同的行记录这一点也是比 MyISAM 之类的表锁引擎强大的地方。我们先来看看更新同一条数据会怎么样。 -- 事务1
mysql begin;
update test_user2 set name fff where id 1212121;-- 事务2
mysql begin;
mysql update test_user2 set name fff where id 1212121;
-- 阻塞 在两个事务中更新同一条数据就会遇到锁的情况这是因为什么呢事务隔离级别的自动加锁呀相信大家还没有忘掉事务隔离级别吧。对于 UPDATE 语句来说都会自动加上 排它锁 同时更新一行当然是不可以的咯但是我们可以同时更新不同的行数据。 --1
mysql begin;
mysql update test_user2 set name fff where id 1212121;--2
mysql begin;
mysql update test_user2 set name fff where id 1212122;
-- 正常 行锁升级到表锁 之前我们提到过InnoDB 的行锁是在一些情况下会升级到表锁的除了 DDL 时会加的 元数据锁 之外下回我们讲它还有一种情况就是如果不走索引也会让行锁变成表锁。 -- 事务1
mysql begin;
mysql update test_user2 set username ffff where username fff;-- 事务2
mysql update test_user2 set username gggg where id 1212122;
-- 阻塞 在上面的测试代码中我们更新时的条件是 username 这个字段并没有索引在这种情况下整个更新语句会扫全表同时锁也会变成 表锁 因此下面针对某条单行数据的更新语句就会阻塞。这就是 行锁 升级或者说是退化为 表锁 的情况。 你可以尝试为 username 加上一个索引之后再试试上面的效果就会发现 行锁 生效了。 总结 通过今天的学习相信大家对锁的概念会有更深的理解了吧。不过上一篇文章中的概念性的内容真的非常重要否则看今天的内容也会是一脸懵逼的。在文章中我可能有时候会说 读锁 有时候会说 共享锁 这么做的目的也是为了能够加深大家对这些名词的印象。这样在面试的时候不管面试官问的是 读锁 还是 共享锁 或者 S 锁你都能很快明白它们是一个意思。 下篇文章我们将继续学习 元数据锁 以及 间隙锁 相关的知识和概念关于锁的内容知识点非常密集千万别错过哦
