MySQL的锁

1、MySQL的锁的基本介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

1.1、数据库锁的分类

从对数据操作的类型：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

2.1、表锁的相关SQL操作

2.1、查看哪些表被锁定

可以通过以下命令来查看哪些表被锁定：

show open tables;

结果示例如下：

结果说明如下：

• Database：含有该表的数据库
• Table：表名称
• In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，即当前没有锁表。如果某个表被锁表，则该表的 In_use 字段不会是 0
• Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作

2.2、给表加锁

可以使用以下命令给表加锁：

lock table 表名 read(write), 表名2 read(write), 其他;

-- 示例
lock table lock_table_test read, lock_table_test2 write;

示例：

先创建一个表 mylock：

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) 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');

然后给该表加读锁：

lock table mylock read;

使用 show open tables; 来查看表锁情况：

可以看到，mydbtest.mylock 表已被锁定。

2.3、释放表锁

释放所有表的锁：

unlock tables;

3、表级锁（读写锁）

3.1、读锁

假设一个会话 A 对 mylock 表进行了读锁，则：

• A 会话可以查该表的数据，但是无法增删改该表的数据，并且也无法查询其他表的数据，即使其他表未被锁定也无法查询，直到对该锁表进行释放锁。
• 其他的会话比如新起了一个会话B，可以查询该锁表的数据，也可以查询、增删改其他表的数据。但是在对该已锁的数据进行增删改时，会一直阻塞，导致SQL无法执行结束，直到该表锁被释放后，SQL会自动执行结束。

也就是加锁的会话可以查锁表数据，但无法增删改，也无法查询其他表。新建的会话可以查询任何表的数据，但对锁表进行增删改时会阻塞，直到锁表的锁被释放。