开发者学堂课程【数据库核心概念:锁(二)】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/63/detail/1145
锁(二)
内容介绍
一、简介
二、总结
一、简介
表锁和行锁在企业当中使用较多,页锁介于二者之间,了解即可。表锁是将整张表都锁住,偏向 MyISAM 存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
将整张表锁住,发生锁冲突的概率低。例如将学校的大门关闭,整个学校一人独享,那么别人就不能与之产生冲突,但并发度最低。进行加锁和演示:
由于此时要分为读写所,所以需要开另外一个 session 进行演示操作。
首先进行建表,此时引擎为 MyISAM,默认是 INNODB,直接输入以下代码:
create table mylock(
id int not null primary key auto_ increment,
name varchar(20)
)engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values("b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
select * from mylock;
进行建表、插入数据、查询。此时 my lock 表如下:
手动进行锁表。假设此时需要进行更新,数据需要备份,希望记录不变。
此时建立了 session1 和 Session2 两个终端,连接到数据库:
首先手动增加一个表锁。命令如下:
lock table 表名字 read(write),表名字2 read(write),其它;
如果要建表就需要 create table,如果要锁表就是 lock table。在该数据库当中还建立过其他表,通过如下命令查询拥有的表格:
Show tables;
查询结果如下:
article
book
Class
dept
emp
my1ock
phone
staffs
tb1A
tb1_ _dept
tb1_ emp
tb1_ _user
test03
此时要将 book 表和 my lock 表加入读锁或写锁。首先通过如下命令查看现在拥有的表格是否加过锁:
Show open tables;
在调 my SQL 时,如果认为 SQL 数据库表可能会被锁,也会有相应查看锁的命令,首先是如下命令:
Show open tables;
存在 db0629 库的表。但 in use 全部为零,证明不存在锁,假设需要将 my Lock 上读锁,给 book 上写锁。命令如下:
Lock table my lock read.book write;
My lock 可共享,book 表不能共享。运行之后结果如下:
Query OK,0 rows affected (0.07 sec )
证明成功。此时执行如下代码:
show open tables;
此时 in_use 当中存在1,对应的表格就是 db0629 的表被加上锁。db0629 当中的book 表也为1。既然存在加锁,也存在解锁。示范表是 unlock tables。数据库当中1越多,运行速度越慢。执行如下代码:
unlock tables;
结果全部还原成为0。上一波 my lock 是1,这一波 my lock 变为0,说明已经解锁。研究读锁写锁会对数据操作和系统性能会产生哪些影响和伤害:
首先加一把读锁,输入以下代码
lock table my lock read;
回车之后,结果如下:
Query OK,0 rows affected (0.00 sec )
加锁成功。执行如下语句:
select * from my lock;
用户对表加了一把读锁,session1 的作用者能够查看。Session2 也能够查看,因为读锁是共享所。
此时执行如下代码:
Update mylock set name=’a2’ where ID=1;
此时 my lock 加入了读锁,读锁是共享锁。执行如上代码。但并不能执行成功。因为Select 是读操作,Update 是写操作。可以读自己,但不能修改自己,此时 session1锁了 my lock,Session2 也能够读。在同样的库下读 book 也能够执行,session2 的读没有受到任何干扰操作,因为读共享。Session1 可以读自己,但不可以改自己。Session1 的锁锁了 mylock,但没有操作过 book。本数据库的其他表没有进行过操作。
如果执行如下代码:
Select * from book;
结果是不能执行。在 Redis 当中存在主从复制,从机在 set 时,不能进行写操作,概念相通。My SQL 出于自我保护,需要用户给出结论性事务:需要锁多长时间,需要将当前操作完成之后才能操作其他事务,否则无法加锁读锁,加锁是为了保证数据不变,比如需要先备份,数据备份完成之后需要解锁。
如果程序导致了锁表,session1 可以读自己,但是不能够修改自己的表,也不能够读其他表。结论就是 session1 只要加了锁之后,需要先将锁解开,才能够操作其他语句。Session2 可以查被锁的表,可以查本库的其他表,如果要更改 Session1 锁过之后的表 mylock,是不能进行的。
书写以下代码:
update mylock Set name=‘a3’where ID=1;
Session1 加完读锁之后的三种操作执行结果已经清楚。session1 为 my lock 表加入读锁之后,在 session2 当中,可以查询my lock,可以查询 book 等其他没有加锁的表。但此时 Session2 不能操作 my lock 进行写操作,session2 想对加了读锁的mylock 进行操作,回车执行如下代码:
update mylock Set name=‘a3’where ID=1;
产生阻塞。在系统当中有多个 session,如果将表锁了,只能堵塞,系统性能变慢。所以此时只能解锁。
执行如下代码:
Unlock tables;
执行完成代码之后,session2 当中也立即执行,但等待了45秒,正常系统当中,如果存在 lock 之后不解锁产生阻塞的情况,等待时间较长,系统较慢。而此时仅仅只存在两个 session,如果有更多 session,系统等待时间更长。加了读锁之后不能够进行书写,只要不解锁就一直阻塞,session2 如果想更改,就必须在 session1 当中将锁解除。
当前 Session 下可以查询该表记录:
其他 session 也能查:
当前 Session 不能查询其他没有锁定的表:
其他 session 可以查询或者更新其他未锁定的表:
更新锁定的表都会提示错误:
其他 Session 插入或更新锁定表会一直等待:
Unlock table 之后 session2 获得锁,插入操作才能够完成:
所以加了锁之后,自己可以读,但是不能进行更改或读其他没有加锁的表。Session2可以查看锁定的表,也可以查看其他未锁定的表。但是如果要操作 session1 所过的表,只能是阻塞等待。由于阻塞等待,系统性能也会下降。以上就是读锁的例子。
读锁可共享,写的时候阻塞。此时演示 session1 加入了写锁,在 session2 当中如何面对。首先,解除锁:
Unlock tables;
其次在 session1 当中为 my lock 加入写锁:
Lock table mylock write;
加锁完成之后,首先查看自己是否能够查看自己锁过的表。输入以下语句:
Select * from my lock;
执行成功,证明自己能够查看自己锁过的表。
查看是否能够修改锁过的表,执行如下代码:
Update mylock set name=‘a6’where ID=1;
执行成功,证明能够修改锁过的表。
查看是否能够查询没有锁过的表:
Select * from book;
此时为 my lock 表加入了写锁。回车之后无法执行,表示在 session1 中,需要将先将锁解除之后,才可以查看其他没有锁过的表。
查看在 session2 当中是否能够查询没有锁过的表,输入以下代码:
Select from book;
执行成功,表示能够查询没有锁过的表。
查看是否能在 session2 当中查询锁过的表。执行如下代码:
Select * from my lock;
回车之后,产生阻塞。在 session1 当中输入以下代码解锁:
Unlock tables;
回车之后,在 session2 当立即执行查询语句。证明加入写锁之后,只能自己读写,在其他 session 当中不能查询,也不能进行修改。当前 session 对锁定表的查询,更新,插入都可以执行,加了写锁 my lock 可以自由执行:
但是其他 session 被锁定阻塞,等待锁的释放:
如果没有发生阻塞,是因为查询次数较多,缓存起来,加载的是缓存的内容。解决问题需要将 ID 进行更换,属于客户端的问题。从理论上,查询应该被阻塞。
释放锁:
session2 获得锁,查询返回:
以上就是读锁写锁的演示。
二、总结
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。MySQL 的表级锁有两种模式::
1.表共享读锁(Table Read Lock)
2.表独占写锁(Table Write Lock)
锁类型 |
可否兼容 |
读锁 |
写锁 |
读锁 |
是 |
是 |
否 |
写锁 |
是 |
否 |
否 |
结论:
结合上表,所以对 MylSAM 表进行操作,会有以下情况:
1、对 MyISAM 表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
2、对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞。笔试时可作为读锁写锁的区别而使用。
加入表锁之后,如果存在变量能够让用户知道锁了多长时间,就能够帮助用户更好的定位及优化 MySQL。可以通过如下代码查看哪些表被加锁:
show open tables;
有1就是被锁,0就是安全。
可以通过检查 table_Locks_waited 和 table_Locks_immediate 状态变量来分析系统上的表锁定:
SQL: show status like‘table%’;
与之前不同。在命令行中输入以下代码:
show status like‘table%’;
加了解锁之后,进行过等待。存在两个状态变量记录 MySQL 内部表级锁定的情况,两个变量说明如下:
Table_locks_immediate
:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1 ;
Table_ocks_waited
:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况;
对于 Myisam 引擎的特点,如果更新之后,session2 经常阻塞是不便于操作的。
此外,Myisam 的读写锁调度是写优先,这也是 myisam 不适合做写为主表的引擎的原因。例如淘宝卖家库和买家库分开,卖家库更多偏向于写,买家库更多偏向于查询浏览商品,卖家更关心上货及编排,所以二者分开。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。以上是 Myisam 偏读不偏写的原因,加锁之后其他人查询该表时,就会阻塞,更新时就会更加堵塞。
以上是表锁的案例结论及表锁分析。
只需要查看 Table_ocks_waited,数据越高,存在较严重的所竞争情况。