深入研究MySQL（一）、锁机制

MySQL中的锁锁

熟悉Java的同学应该知道有很多关于锁的东西，比如synchronized关键字、lock接口等，但是我们也不能忽略数据库中的锁以及关于数据库的知识，所以，预计后面十余篇会记录关于数据库的知识。

在MySQL中，不同的存储引擎支持不同的锁，InnoDB支持行锁、表锁，MyISAM只支持表锁，MySQL默认情况下是使用行级锁的，他们的区别如下：

1. 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

2. 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

需要注意的是数据库的锁往往是基于索引来实现的，InnoDB也是，如果SQL语句没有走索引，则会全表扫描，锁定所有的行（不是表锁），此时其它事务无法对当前表进行更新或插入操作。

共享锁和排它锁

在InnoDB中，有两种标准的行级锁，共享锁（读锁）和独占锁（写锁），读锁也就是如果一个事务获取了一个数据行的读锁，那么在这个事务读取数据行的时候，其他事务也可以读取，但是不能对该数据行进行增删改操作。

共享锁

下面进行测试，首先新建一张表，结构、内容如下：

记得要关闭mysql数据库的自动提交。

在SQL最后加上lock in share mode，代表在被读取的行记录范围上加一个读锁，其他事务也可以读，但不可以写，否则会被阻塞，如果等待的时间过长，就会报错，会提示Lock wait timeout exceeded; try restarting transaction，或者在超时前加锁的事务进行commit，更新语句就会成功，加上共享锁后，对于update，insert，delete语句会自动加排它锁。

另外这个超时时间也是可以设置的，在文章最后有说明。

begin;
select * from book where bookid =1 lock in share mode;

那么在另个会话中更新的话，就会被阻塞，如下动画，左面是加锁的会话，右面进行更改。

但是update也只有在被锁住的行上才发生阻塞，在更新其他bookid时，不会被阻塞，如果select全表的话，那么全表的数据都会被锁，其他会话对每一行都不能进行更改。

排它锁

而写锁就是如果一个会话获取了数据行的写锁，其他事务就不能再获取该行的其他锁，写锁优先级最高。

begin;
select * from book where bookid =1 for update;

可以看到右面在进行加锁会阻塞，但是给别的bookid进行加锁会成功，另外其他会话是可以对该记录查询。

行锁分析

show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1      |
| Innodb_row_lock_time          | 214525 |
| Innodb_row_lock_time_avg      | 9751   |
| Innodb_row_lock_time_max      | 51008  |
| Innodb_row_lock_waits         | 22     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数

表锁

我们可以使用以下语句显示的获取一个表锁。

LOCK TABLES table_name [READ | WRITE]

锁的的类型有READ或WRITE，下面会对两种区别进行演示。

READ

READ锁有以下特点:

1. READ可以有多个会话获取，其他会话也可以在不获取锁的情况下从表中读取数据。
2. 持有READ锁的会话只能从表中读取数据，而不能写入。在READ释放锁之前，其他会话无法将数据写入表，另一个会话写操作将进入等待状态，直到READ释放锁定。
3. 如果会话正常或异常终止，MySQL将隐式释放所有锁。

好了，我们知道了READ锁的特点，下面在进行一下测试，首先使用READ锁锁住book表。

lock tables book read;

然后在其他客户端下进行修改，但是你会发现，这个命令被阻塞了，直到book表被unlock。

update book set author = "未知" where bookid =1;

解锁的命令如下:

unlock tables;

需要注意的是，一旦READ获得了锁，就无法在同一会话中修改数据，将会抛出异常。

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

mysql> update book set author ="未知" where bookid =1;
ERROR 1099 (HY000): Table 'book' was locked with a READ lock and can't be updated
mysql> 

WRITE

WRITE锁具有如下特点：

1. 持有锁的会话可以读取和写入表。
2. 只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。
3. 保持锁定状态时，其他会话对表的锁定请求将阻塞WRITE。

需要注意的是，在lock tables语句执行时，由于任何其他表上的其他会话持有的锁而必须等待，它将阻塞直到可以获取所有锁为止，如一个会话先执行了lock tables book write;，另一个会话在执行lock tables book read;，他不会因为等待时间长了而抛出错误，而是一直阻塞直到可以获取为止。

在lock tables锁后，会话也只能访问锁定的表，如下操作会报错，因为bookType表不在范围内。

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

mysql> select count(*) from book;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

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

使用write方式锁表。

lock tables book write

此时其他会话就不能对这个表进行加READ锁、WRITE锁，否则会被阻塞，同时也不能对这个表进行select、update、delete、insert。

MDL锁

MDL全称是meta data lock，也就是元数据锁，用于保证表中元数据的信息，比如在会话A中，表开启查询事务后，就会自动获取MDL锁，会话B就不可以执行任何DDL语句。

begin;
select * from book;

然后执行修改表语句时就会被阻塞。

 alter  table book change author book_author varchar(100) ;

意向锁

意向锁是表级锁，有两种类型，分别为意向共享锁和意向排他锁。
意向共享锁（IS）：指的是在给一个数据行加共享锁前必须先取得改表的IS锁。
意向排他锁（IX）：指的是在给一个数据行加排他锁前必须先取得改表的IX锁。

意向锁的作用和MDL类似，都是防止在事务进行过程中，执行DDL语句的操作而导致数据不一致。

等待超时

主要就是innodb_lock_wait_timeout参数，这个参数代表InnoDB事务在放弃之前等待行锁定的时间（以秒为单位），默认值为50秒，查看超时sql如下，

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

修改超时时间，修改后需要重新建立连接。

SET GLOBAL innodb_lock_wait_timeout=5;