mysql locked myisam_【MySQL】锁之MyISAM

纸上得来终觉浅，绝知此事要躬行。

锁的分类

从对数据操作的粒度分 ：

表锁：操作时，会锁定整个表。

行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁。

MySQL锁

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

存储引擎

表级锁

行级锁

页面锁

MyISAM

支持

不支持

不支持

InnoDB

支持

支持

不支持

MEMORY

支持

不支持

不支持

BDB

支持

不支持

支持

特性：

锁类型

特点

表级锁

偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁

偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM表锁

如何加表锁

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

显示加表锁语法：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；

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

读写锁案例

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

create database demo2 default charset=utf8;

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

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,"李四");

读锁

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

查询数据

客户端一：

获取tb_book表的读锁

lock table tb_book read;

执行查询语句

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)

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

客户端二：

执行查询操作

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)

也可以正常查询数据

客户端一：

查询未锁定的表

select * from tb_user;

mysql> select * from tb_user;

ERROR 1100 (HY000): Table "tb_user" was not locked with LOCK TABLES

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

客户端二：

查询未锁定的表

select * from tb_user;

mysql> select * from tb_user;

+----+--------+

| id | name |

+----+--------+

| 1 | 张三 |

| 2 | 李四 |

+----+--------+

2 rows in set (0.00 sec)

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

插入数据

客户端一：

执行插入数据

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 获得的是读锁，不能执行更新操作。

客户端二：

执行插入数据

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

执行语句阻塞，等待中。

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

写锁

查询数据

客户端一：

获取tb_book表的写锁

lock table tb_book write;

执行查询数据

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)

执行update操作

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

执行insert操作

mysql> insert into tb_book values(null,"Python","2088-01-01","1");

Query OK, 1 row affected (0.00 sec)

执行delete操作

mysql> delete from tb_book where id = 4;

Query OK, 1 row affected (0.00 sec)

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

客户端二：

执行查询操作

select * from tb_book;

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

客户端三：

执行更新操作

update tb_book set name = "Java" where id = 3;

也处于阻塞状态。

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

锁上加锁

不同客户端加锁

客户端一：

获取tb_book表的读锁

lock table tb_book read;

客户端二：

获取tb_book表的读锁

lock table tb_book read;

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

获取tb_book表的写锁

lock table tb_book wtite;

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

同一客户端多次加锁

客户端一：

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

lock table tb_book read;

lock table tb_book write;

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

总结

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

对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作

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

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

查询锁的争用情况

show open tables；

In_user：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

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，此值高说明存在着较为严重的表级锁争用情况。