mysql中的锁机制之表锁

举报
炒香菇的书呆子 发表于 2022/06/30 23:26:00 2022/06/30
【摘要】 1、概述表锁比较偏向MYISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最底。整张表就只能一个人使用。2、建表语句和数据-- 创建一张MyISAM存储引擎的数据表``CREATE` `TABLE` ``locktest` (`` ```id` ``int``(11) ``NOT` `NULL` `AUTO_INCREMENT,`` `````name``` `...

1、概述

表锁比较偏向MYISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最底。整张表就只能一个人使用。

2、建表语句和数据

-- 创建一张MyISAM存储引擎的数据表``CREATE` `TABLE` ``locktest` (`` ```id` ``int``(11) ``NOT` `NULL` `AUTO_INCREMENT,`` `````name``` ``varchar``(255) ``CHARACTER` `SET` `utf8 ``COLLATE` `utf8_general_ci ``NULL` `DEFAULT` `NULL``,`` ``PRIMARY` `KEY` `(`id`) USING BTREE``) ENGINE = MyISAM AUTO_INCREMENT = 6 ``CHARACTER` `SET` `= utf8 ``COLLATE` `= utf8_general_ci ROW_FORMAT = ``Dynamic``;` `-- 插入测试数据``INSERT` `INTO` ``locktest` ``VALUES` `(6, ``'张无忌'``);

3、查看表是否被锁定

show ``open` `tables ``like` `'%locktest%'

示例截图:

m1.png

上图中的In_use列是0 表示locktest表没有被加锁

4、对表进行加锁

语法:lock table 表名 read|write

也可以锁定多个表,语法是:lock table 表1 read|wirte,表2 read|wirte


示例:

-- 给locktest这张表加上 读锁(也就是 共享锁)``lock ``table` `locktest ``read``;` `-- 查看locktest这张表的状态,是否被锁定``show ``open` `tables ``like` `'%locktest%'

示例截图:

m2.png

上图中的In_use列是1 表示locktest表已经被加锁

5、对表进行解锁

unlock tables


示例:

unlock tables

示例截图:

m1.png

上图中可以看到我们进行了解锁操作,然后再次查看locktest表的锁定状态,发现已经是解锁的状态了。

6、读写锁对操作和性能会产生哪些影响?

先来做一下准备工作

①、继续创建一张表:

CREATE` `TABLE` ``customer` (`` ```id` ``int``(11) ``NOT` `NULL``,`` `````name``` ``varchar``(255) ``CHARACTER` `SET` `utf8 ``COLLATE` `utf8_general_ci ``NULL` `DEFAULT` `NULL``,`` ``PRIMARY` `KEY` `(`id`) USING BTREE``) ENGINE = InnoDB ``CHARACTER` `SET` `= utf8 ``COLLATE` `= utf8_general_ci ROW_FORMAT = ``Dynamic``;` `INSERT` `INTO` ``customer` ``VALUES` `(1, ``'敏敏特穆尔'``);


②、然后使用2个客户端分别连接上mysql,我这里用的是Navicat Premium和mysql命令行版,下面的演示中我会将Navicat Premium称为:会话1。mysql命令行版称为:会话2。


2个mysql客户端示例截图如下:

会话1:

m2.png

会话2:

m3.png

6.1、在会话1中对locktest表添加读锁(读锁也称为:共享锁)lock table locktest read;

m1.png

当前连接(也就是 会话1):

是否可以查看自己?

答:可以

示例截图:

m2.png


是否可以更新数据?

答:不可以

示例截图:

m3.png


能不能读别的表?

答:不可以!!!自己锁定的当前表还没有进行解锁,不能放下当前还未进行解锁的表,操作别的表!

示例截图:

m4.png

另一个连接(也就是 会话2):

是否可以查看被 会话1 中锁定的表?

答:可以

示例截图:

m5.png


能不能读别的表(未被会话1中锁定的表)?

答:可以** **

示例截图:

m6.png


是否可以更新数据(更新被会话1中锁定的locktest表的数据)?

答:可以。但是,当更新时,会出于阻塞状态,只有等待会话1中进行解锁后,此时才会进行更新操作。

示例截图:

m1.png

上图中可以看到,当我们进行更新的时候,mysql会一直处于阻塞等待的状态。


我们在会话1中将locktest表进行解锁,然后看会话2中 mysql的反映,会发现当会话1中将locktest表进行解锁之后,会话2中的更新locktest表的操作会立即执行。

示例截图:

m2.png


6.2、在会话1中对locktest表添加写锁(写锁也称为:排它锁)lock table locktest write;

注意:此时我们的locktest表中没有加任何锁了,上面加的读锁,在最后也被释放了。这里加上写锁之后,locktest表中只处于被 写锁的状态,不存在读锁的状态。

示例截图:

m1.png

当前连接(也就是 会话1):

能否读自己锁过的表?

答:可以

示例截图:就不截取了。。


能否修改自己锁过的表?

答:可以

示例截图:就不截取了。。


能否读取别的表?

答:不可以!因为当前自己锁定的表还没有被释放掉,所以不可以读取别的表。

示例截图:就不截取了。。

另一个连接(也就是 会话2):

能否操作在会话1中没有被加过锁(写锁)的表?

答:可以,无论是读取还是更新数据,都不受影响。

示例截图:就不截取了吧。。


能否对被锁过(写锁)的表进行读取或更新操作?

答:不能,会处于阻塞。等待会话1中解锁时, 才能查到或更新。因为会话1中加的锁是写锁(排它锁),即使在会话2中进行查询操作,也不会立即查询到结果。


示例一个查询操作截图:

m2.png


更新操作的截图就不截取了。。


写锁(排它锁)总结:一旦某一张表被加上写锁,那么不管其它连接到mysql的客户端是查询还是更新被加上写锁的那张表,只要加上写锁的那个mysql客户端连接还没有释放锁,那么其它连接到mysql客户端的 想要操作被加上写锁的那张表 只能处于等待阻塞状态。对应的mysql客户端释放锁之后,被阻塞的操作才会进行查询或更新操作。排它锁排它锁 就是排斥作用比较强。


一句话总结:表加写锁后,则只有当前线程对锁定的表,可以执行任何操作。其它线程的操作会被阻塞。

7、表锁的分析及选择

可以使用如下语句分析:

show status like 'table%';


出现的结果中以下2个值比较重要:

①、Table_locks_immediate:产生表级锁定的次数。表示的是能够立即获得表级锁的锁查询次数。网上普遍说的是:释放表锁数(可以立即释放的数量,其实说的都是一个意思,还没有释放,不要理解成已经释放了。)


②、Table_locks_waited:出现表级锁定争用而发生等待的次数


表锁的选择:

MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写为主表的引擎。因为写锁后,其它线程不能做任何操作,大量更新会使用查询很难得到锁,从而造成永久阻塞。


如果读取的操作比较多,那么就可以使用表锁。如果更新的操作比较多 就不要使用表锁。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。