MySQL的锁问题

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁的分类：

从对数据操作的类型来分：读锁（共享锁）和写锁（排它锁）

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

从对数据操作的粒度来分：表锁和行锁

表锁

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

创建表锁

首先创建一个mylock表，使用MyIsam引擎。

drop table if exists mylock;
CREATE TABLE mylock (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR (20) NOT NULL
) ENGINE MyISAM DEFAULT charset = utf8;
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');

手动增加表锁

加读锁或写锁：

mysql> lock table mylock read, account write;
Query OK, 0 rows affected (0.02 sec)

显示表是否被加锁：

show open tables;

释放表锁：

unlock tables；

表共享读锁

创建2个session，在session1上加读锁：

mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

查询

对于session1，对mylock表的读操作可以正常进行：

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.01 sec)

对于session2，对mylock表的读操作也可以正常进行

写操作

对于session1，写操作都无法执行

mysql> update mylock set name = 'a2' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

对于session2，写操作没有报错，但被阻塞。 由于A会话对mylock表加锁，在锁未释放时，其他会话是不能对mylock表进行更新操作的。

读其它表

session1的mylock表的读锁，并不影响session2对mylock表和其他表的读操作，但session1无法读写其他表。

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

解锁

session1解锁mysql> unlock tables;，session2被阻塞的写操作被释放，被阻塞了2分25秒：

mysql> update mylock set name = 'a2' where id = 1;
Query OK, 1 row affected (2 min 24.95 sec)
Rows matched: 1  Changed: 1  Warnings: 0

表独占写锁

对session1添加写锁：lock table mylock write;

对mylock表进行读写操作，由于session1对mylock表加的写锁，所以读写操作都执行正常。

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> update mylock set name = 'a2' where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

在session1中对其他表进行读写操作都失败，因为mylock表的写锁并未被释放

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

由于mylock表已经加写锁，而写锁为排它锁，因此在session2中对mylock表进行读写操作都会阻塞。

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (24.22 sec)

表锁定分析

使用如下命令分析表锁：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 64    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 12    |
| Table_open_cache_misses    | 19    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

主要注意两个变量的值：

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

MDL

**另一类表级的锁是MDL（metadata lock)。**MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

总结

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

对MyISAM表加读锁，不会阻塞其他进程对同一表（mylock）的读操作，但是会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。
对MyISAM表加写锁，会阻塞其他进程对同一表（mylock）的读和写操作，只有当读锁释放后，才会执行其他进程的读写操作。

简而言之：读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的引擎（例如淘宝卖家），因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成长时间阻塞。

行锁

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，但并发度高。其中InnoDB存储引擎支持事务。

创建行锁

创建相关测试表tb_innodb_lock，注意数据库引擎为InnoDB：

drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
    a INT (11),
    b VARCHAR (20) 
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1,'a');
insert into test_innodb_lock values (2,'b');
insert into test_innodb_lock values (3,'c');
insert into test_innodb_lock values (4,'d');
insert into test_innodb_lock values (5,'e');
create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);

行锁定

打开A、B两个会话，并关闭数据库的自动提交：set autocommit = 0;

在A会话中做更新操作：

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

mysql> update test_innodb_lock set b = 'b1' where a = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b1   |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

在未提交的情况下，在A会话中更新数据成功，但在B会话中查询更新数据没有成功，即读己知所写：自己更改的数据自己知道，但是如果未提交，其他人是不知道的。

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

在A会话中执行commit命令（脏读），然后在B会话中也提交commit命令（不可重复读）再次查询，数据也更新了。

同时更新的情况：
在A会话中做更新操作，然后在B会话中也对同一字段做更新操作，这时B会话会阻塞。接着在A会话中commit操作，可看到B会话中发生了更新操作。

我们操作的同一行数据，而由于InnoDB为行锁，在A会话未提交时，B会话只有阻塞等待。如果操作不同行，则不会出现阻塞情况。

索引失效导致行锁升级为表锁

在A会话中执行如下更新语句导致索引失效(由于类型转换)：

mysql> update test_innodb_lock set b = 'b3' where a = '2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

发生了自动转换导致索引失效，从而使锁的级别从行锁升级为表锁，因此B会话中操作数据出现阻塞的情况：

mysql> update test_innodb_lock set b = 'b4' where a = '2';

间隙锁的危害

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但不存在的记录，叫作“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。（Next-Key锁）

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据。在某些场景下这个可能会对性能造成很大的危害。

锁定单行

利用for update

下面这两个select语句，就是分别加了读锁（S锁，共享锁）和写锁（X锁，排他锁）：

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

行锁分析

show status like 'innodb_row_lock%';
Innodb_row_lock_waits：系统启动后到现在总共等待锁的次数。

总结

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

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

InnoDB的行锁定同样尤其脆弱的一面（间隙锁危害），当使用不当时可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能更差。

优化建议

1. 尽可能让所有数据都通过索引来完成，避免无索引行升级为表锁。
2. 合理设计索引，尽量缩小锁的范围。
3. 尽可能使用较少的检索条件，避免间隙锁。
4. 尽量控制事务大小，减少锁定资源量和时间长度。
5. 尽可能降低事务隔离级别。
6. 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。

当数据库支持事务时，也可以不加锁进行备份：

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务(可重复读)，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。