MySQL高级学习之表锁
参考文章:
MySQL高级知识(十三)——表锁
1.概述
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中,除传统的计算资源(如 CPU、RAM、I/O 等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
2.分类
从对数据操作的粒度分 :
1) 表锁:操作时,会锁定整个表。
2) 行锁:操作时,会锁定当前操作行。
从对数据操作的类型分:
1) 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
2) 写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。
3.表锁
MyISAM 存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。
3.1如何加表锁
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会
自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
显式加锁语法:
加读锁 : lock table table_name read;
加写锁 : lock table table_name write;
3.2查看表是否被加锁以及释放锁
# 查看表是否被加锁
show open tables;
# 释放锁
unlock tables;
3.2读锁案例
CREATE TABLE `tb_book` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
`publish_time` DATE DEFAULT NULL,
`status` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,‘java编程思想‘,‘2088-08-01‘,‘1‘);
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,‘solr编程思想‘,‘2088-08-08‘,‘0‘);
CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_user (id, name) VALUES(NULL,‘令狐冲‘);
INSERT INTO tb_user (id, name) VALUES(NULL,‘田伯光‘);
会话A:
1)给tb_book加读锁
lock table tb_book read;
2)执行查询操作
可以正常查询,获取数据
会话B:
3)执行查询操作
select * from tb_book;
会话A:
4)查询未锁定的表tb_user
select * from tb_user;
无法查询。。。
会话B:
5)查询未锁定的表tb_user
select * from tb_user;
可以正常查询
会话A:
6)执行插入操作
insert into tb_book values(null,‘Mysql高级‘,‘2088-01-01‘,‘1‘);
执行插入报错,因为当前tb_book获得的是读锁,不能执行更新操作
会话B:
7)执行插入操作
insert into tb_book values(null,‘Mysql高级‘,‘2088-01-01‘,‘1‘);
阻塞,当在会话A中释放锁后,会话B中的insert语句立即执行。
3.3.写锁案例
会话A:
1)获得tb_book的写锁
lock table tb_book write ;
2)执行查询操作
select * from tb_book;
查询操作执行成功
3)执行更新操作
update tb_book set name = ‘java编程思想(第二版)‘ where id = 1;
更新成功
会话B:
4)执行查询操作
select * from tb_book;
? 阻塞,当在会话A中释放写锁后,会话B中的select语句立即执行。
3.4结论
对MyISAM 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
对MyISAM 表的写操作,则会阻塞其他用户对同一表的读和写操作;
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。
以上结论都是在MyISAM引擎下得出的。
3.5 表锁定分析
3.5.1 使用如下命令查看是否有表被锁定
show open tables where In_use>0;
3.5.2 使用如下命令分析表锁
show status like ‘table%‘;
注意两个变量的值:
-
Table_locks_immediate:产生表级锁定的次数,表示可立即获取锁的查询次数,每立即获取锁一次该值加1。
-
Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁该值加1),此值高则说明存在较
严重的表级锁争用情况。