MySQL锁机制

1、表锁

表锁特点：表锁偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

1.1、验证准备

# 1、创建表
CREATE TABLE IF NOT EXISTS `book`(
  `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

CREATE TABLE `mylock`(
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='测试表锁';

# 2、插入数据
INSERT INTO `mylock`(`name`) VALUES('ZhangSan');
INSERT INTO `mylock`(`name`) VALUES('LiSi');
INSERT INTO `mylock`(`name`) VALUES('WangWu');
INSERT INTO `mylock`(`name`) VALUES('ZhaoLiu');

1.2、锁表的命令

1. 查看数据库表锁的命令

SHOW OPEN TABLES;		# 查看数据库表锁的命令

2. 给mylock表上读锁，给book表上写锁LOCK TABLES mylock READ, book WRITE;

# 给mylock表上读锁，给book表上写锁
LOCK TABLES `mylock` READ, `book` WRITE;

# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+-------------------------+--------+-------------+
| Database           | Table                   | In_use | Name_locked |
+--------------------+-------------------------+--------+-------------+
| sql_analysis       | book                    |      1 |           0 |
| sql_analysis       | mylock                  |      1 |           0 |
+--------------------+-------------------------+--------+-------------+

3. 释放表锁UNLOCK TABLES;

# 释放给表添加的锁
UNLOCK TABLES;

# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+-------------------------+--------+-------------+
| Database           | Table                   | In_use | Name_locked |
+--------------------+-------------------------+--------+-------------+
| sql_analysis       | book                    |      0 |           0 |
| sql_analysis       | mylock                  |      0 |           0 |
+--------------------+-------------------------+--------+-------------+

1.3、读锁案例

1. 打开两个会话，SESSION1为mylock表添加读锁

LOCK TABLES `mylock` READ;		# 为mylock表添加读锁

2. 打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？

SESSION1

# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE mylock SET name = 'abc' WHERE id = 1;
ERROR 1099 (HY000): Table mylock was locked with a READ lock and cannot be updated

# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

SESSION2

# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，
#        需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

结果：

会话	是否可以读自己锁的表	是否可以修改自己锁的表	是否可以读其他的表
SESSION1	能	不能	不能
SESSION2	能	阻塞	能

1.4、写锁案例

1. 打开两个会话，SESSION1为mylock表添加写锁

# 为mylock表添加写锁
LOCK TABLES mylock WRITE;

2. 打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？ SESSION1

# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

SESSION2

# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

结果：

会话	是否可以读自己锁的表	是否可以修改自己锁的表	是否可以读其他的表
SESSION1	能	能	不能
SESSION2	阻塞	阻塞	能

1.5、案例结论

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

対MyISAM表进行操作，会有以下情况：

• 读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作
• 写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作

结论：读锁会阻塞写，但不会阻塞读。而写锁会把读跟写都阻塞

1.6、表锁分析

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

mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 173   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

可以通过 Table_locks_immediate 和 Table_locks_waited 状态变量来分析系统上的表锁定。

具体说明

• Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
• Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况

2、行锁

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

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁

事务的ACID四个特性：

• 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。换一种方式理解就是：事务按照预期生效，数据的状态是预期的状态。

举例说明：张三向李四转100元，转账前和转账后的数据是正确的状态，这就叫一致性，如果出现张三转出100元，李四账号没有增加100元这就出现了数据错误，就没有达到一致性。

• 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

举例说明：我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

2.1、验证准备

# 建表语句
CREATE TABLE test_innodb_lock(
  `a` INT,
  `b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁'; 

# 插入数据
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(1, 'b2');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(2, '3');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(3, '4000');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(4, '5000');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(5, '6000');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(6, '7000');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(7, '8000');
INSERT INTO test_innodb_lock(`a`, `b`) VALUES(8, '9000');

# 创建索引
CREATE INDEX idx_test_a ON test_innodb_lock(a);
CREATE INDEX idx_test_b ON test_innodb_lock(b);

2.2、行锁案例

1. 打开SESSION1和SESSION2两个会话，都开启手动提交

# 开启MySQL数据库的手动提交
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

2. 读自己所写

# SESSION1 

# SESSION1 对test_innodb_lock表做写操作，但是没有commit
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

# SESSION2 

# SESSION2 这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

3. 行锁两个SESSION同时対一条记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！
# 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了
mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4. 行锁两个SESSION同时对不同记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！
# SESSION1和SESSION2同时对不同的行进行写操作互不影响
mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.3、索引失效行锁变表锁

# SESSION1 执行SQL语句，没有执行commit
# 由于`b`字段是字符串，但是没有加单引号导致索引失效
mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

# SESSION2 和SESSION1操作的并不是同一行，但是也被阻塞了？？？
# 由于SESSION1执行的SQL索引失效，导致行锁升级为表锁
mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.4、间隙锁及其危害

什么是间隙锁?

• 当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范文内但并不存在的记录，叫做"间隙(GAP)"
• InnoDB也会对这个"间隙"加锁，这种锁的机制就是所谓的"间隙锁"

间隙锁的危害

• 因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
• 间隙锁有一个比较致命的缺点，就是当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会対性能造成很大的危害

2.5、如何锁定一行

语法

mysql> BEGIN; # 开启
mysql> SELECT .....FOR UPDATE; # 锁定一行
mysql> COMMIT; # 提交自动解锁

锁定一行

SELECT .....FOR UPDATE在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT

补充

mysql``InnoDB引擎默认的修改数据语句，update``delete``insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制

排他锁

2.6、案例结论

• InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势

• 但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差

2.7、行锁分析

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 124150 |
| Innodb_row_lock_time_avg      | 31037  |
| Innodb_row_lock_time_max      | 51004  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

対各个状态量的说明如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量
• Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）
• Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）
• Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）

3、优化建议

当等待次数很高，且每次等待时长也不小的时候，就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离