MyISAM存储引擎的表级锁

前言

如果了解过文件锁的用法，那理解数据库锁就简单了。锁其实就协调多个进程或线程并发时，处理访问同一个资源的机制。在项目开发中，表锁是MySQL中作用范围较大的一种锁，它锁定的是用户操作的整张表，可以有效避免死锁的情况，且加锁速度快，消耗资源小。事情总有两面性，也就是因为表锁是锁了整张表，在并发的时候锁冲突更大，并发能力也就差

一、表锁的分类

表锁根据操作的不同分为表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

1.读锁

读锁表示用户读取数据（select）时添加的锁，此时不会阻塞其他用户对同一张表的读操作，但是会阻塞对同一张表的写请求

2.写锁

写锁表示用户对数据资源执行写（insert、update、delete）操作时添加的锁，此时除了当前添加写锁的用户外，其他用户都不能对表进行读、写操作，也就是说会阻塞其他用户对同一张表的读、写操作

MyISAM表的读操作与写操作，以及写操作之间是串行的

二、隐式的加锁

当用户操作select查询数据表时（MyISAM表），MySQL服务器会自动地为查询的表添加一个表读锁；当执行增删改（insert、delete、update）操作时，同样MySQL服务器也会自动地为操作的表添加一个表写锁；直到操作结束完毕，服务器为其自动解锁。

mysql> select * from order_step; 

查询一张7万记录的表，注意表的存储引擎为MyISAM; 查询时间差不多3-5秒。此时新开一个终端，执行更新操

mysql> update order_step set statusinner=2001 where stepid=75584;
// 等待上一个终端执行结束

隐式加锁的时候，表的更新操作优先级高于表的查询操作。在添加写锁时，若表中没有任何锁则添加，否则排队等待。在添加读锁时，若表中没有写锁则添加，否则也将排队等待

三、显示加锁

用户一般不需要直接用LOCK TABLE命令给MyISAM表显示加锁。我们为了学习，显示加锁来在一定程度模拟事务操作

LOCK TABLES 数据表名 READ [LOCAL] WRITE,....

1.创建MyISAM表，准备实验环境

CREATE TABLE `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(50),
`money` DECIMAL(7,2)
)ENGINE=myisam CHARSET=utf8; 

INSERT INTO user(`username`,`money`) VALUES('乔峰',5);

2.显式加表锁

终端A：在自己操作表的时候，不希望其他用户去修改这这个表的数据

mysql> lock tables user read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+-----------+--------+
| id | username  | money  |
+----+-----------+--------+
|  1 | 乔峰      |   5.00 |
+----+-----------+--------+
1 rows in set (0.00 sec)

终端B:

mysql> update user set money=money+5 where id=1;
// 等待终端A 释放锁

终端A:释放锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3.支持并发时插入

在上面例子中，当终端A没有释放锁的时候，此时终端是不能update的，同时也不能insert。

mysql> insert into user(username,money) values('张三','100');
// 插入等待

考虑到并发的情况，我们在加锁的时候添加local选项。让其支持并发时插入

mysql> lock tables user read local;
Query OK, 0 rows affected (0.00 sec)

四、查询表级锁争用情况

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 86    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 2     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)