系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

【MySQL】锁之MyISAM

时间:2020-06-11来源:www.pcxitongcheng.com作者:电脑系统城

锁的分类#

从对数据操作的粒度分 :

  1. 表锁:操作时,会锁定整个表。
  2. 行锁:操作时,会锁定当前操作行。

从对数据操作的类型分:

  1. 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
  2. 写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。

MySQL锁#

MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况:

存储引擎 表级锁 行级锁 页面锁
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

特性:

锁类型 特点
表级锁 偏向MyISAM 存储引擎,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁 偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

MyISAM表锁#

如何加表锁#

MyISAM 在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行更新操作UPDATE、DELETE、INSERT前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法:


 
Copy
加读锁 : lock table table_name read; 加写锁 : lock table table_name write;

虽然MyIASM默认加了表锁,但是我们仍然可以手动加锁,具体通过案例来了解一下加锁之后,我们操作数据库会有什么影响。

读写锁案例#

在进行测试之前我们需要准备测试环境和相关数据库以及表,并且把MySQL的存储引擎指定为MyISAM,如下先创建一个数据库并切换到数据库:


 
Copy
create database demo2 default charset=utf8;

接着创建表,本次我们测试的是MyISAM存储引擎,所以要创建表时要指定存储引擎(说明存储引擎是作用于表,而不是作用于数据库,也就是说一个数据库有多个表,而多个表可以使用不同的存储引擎)


 
Copy
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,'高性能MySQL','2088-08-01','1'); INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'MySQL技术内幕','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,'李四');

读锁

数据准备完成接下来我们进行测试,使用两个客户端连接数据库。

  • 查询数据

客户端一:

  1. 获取tb_book表的读锁

 
Copy
lock table tb_book read;
  1. 执行查询语句

 
Copy
select * from tb_book; mysql> select * from tb_book; +----+-------------------+--------------+--------+ | id | name | publish_time | status | +----+-------------------+--------------+--------+ | 1 | 高性能MySQL | 2088-08-01 | 1 | | 2 | MySQL技术内幕 | 2088-08-08 | 0 | +----+-------------------+--------------+--------+ 2 rows in set (0.00 sec)

可以正常执行,查询出数据。

客户端二:

  1. 执行查询操作

 
Copy
select * from tb_book; mysql> select * from tb_book; +----+-------------------+--------------+--------+ | id | name | publish_time | status | +----+-------------------+--------------+--------+ | 1 | 高性能MySQL | 2088-08-01 | 1 | | 2 | MySQL技术内幕 | 2088-08-08 | 0 | +----+-------------------+--------------+--------+ 2 rows in set (0.00 sec)

也可以正常查询数据

客户端一:

  1. 查询未锁定的表

 
Copy
select * from tb_user; mysql> select * from tb_user; ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

提示tb_user没有加锁,无法获取数据

客户端二:

  1. 查询未锁定的表

 
Copy
select * from tb_user; mysql> select * from tb_user; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 李四 | +----+--------+ 2 rows in set (0.00 sec)

可以正常查询出未锁定的表

  • 插入数据

客户端一:

  1. 执行插入数据

 
Copy
insert into tb_book values(null,'Mysql高级','2088-01-01','1'); mysql> insert into tb_book values(null,'Mysql高级','2088-01-01','1'); ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated

执行插入, 直接报错,由于当前tb_book 获得的是读锁,不能执行更新操作。

客户端二:

  1. 执行插入数据

 
Copy
insert into tb_book values(null,'Mysql高级','2088-01-01','1');

执行语句阻塞,等待中。

当在客户端一中释放锁指令unlock tables后 , 客户端二中的 inesrt 语句立即执行。

写锁

  • 查询数据

客户端一:

  1. 获取tb_book表的写锁

 
Copy
lock table tb_book write;
  1. 执行查询数据

 
Copy
select * from tb_book; mysql> select * from tb_book; +----+-------------------+--------------+--------+ | id | name | publish_time | status | +----+-------------------+--------------+--------+ | 1 | 高性能MySQL | 2088-08-01 | 1 | | 2 | MySQL技术内幕 | 2088-08-08 | 0 | | 3 | Mysql高级 | 2088-01-01 | 1 | +----+-------------------+--------------+--------+ 3 rows in set (0.00 sec)
  1. 执行update操作

 
Copy
mysql> update tb_book set name = 'MySQL' where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
  1. 执行insert操作

 
Copy
mysql> insert into tb_book values(null,'Python','2088-01-01','1'); Query OK, 1 row affected (0.00 sec)
  1. 执行delete操作

 
Copy
mysql> delete from tb_book where id = 4; Query OK, 1 row affected (0.00 sec)

当前客户端处于write lock,其增删改查都正常执行

客户端二:

  1. 执行查询操作

 
Copy
select * from tb_book;

此时查询操作处于阻塞状态。

客户端三:

  1. 执行更新操作

 
Copy
update tb_book set name = 'Java' where id = 3;

也处于阻塞状态。

当我们把客户端一的表解锁,则客户端二的查询操作和客户端三的更新操作立刻执行。

锁上加锁#

  • 不同客户端加锁

客户端一:

  1. 获取tb_book表的读锁

 
Copy
lock table tb_book read;

客户端二:

  1. 获取tb_book表的读锁

 
Copy
lock table tb_book read;

此时客户端一,只能读取加锁的表,客户端二同样只能读取加锁的表,都无法更新表。

  1. 获取tb_book表的写锁

 
Copy
lock table tb_book wtite;

由于客户端一已经上了读锁,此时客户端二上写锁会阻塞。

  • 同一客户端多次加锁

客户端一:

  1. 执行tb_book表的读锁,然后继续执行写锁

 
Copy
lock table tb_book read; lock table tb_book write;

此时客户端一可以对加锁的表执行增删改查,客户端二对加锁的表增删改查阻塞。

总结#

  • MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求
  • MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作

​简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。

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

查询锁的争用情况#


 
Copy
show open tables;

In_user:表当前被查询使用的次数。如果该数为零,则表是打开的,但是当前没有被使用。
Name_locked:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。


 
Copy
show status like 'Table_locks%'; mysql> show status like 'Table_locks%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 139 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec)

Table_locks_immediate:指的是能够立即获得表级锁的次数,每立即获取锁,值加1。
Table_locks_waited:指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加1,此值高说明存在着较为严重的表级锁争用情况。

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载