MySQL 的锁 – day day up

一般使用“已提交读”（oracle）、“可重复读”（mysql）。

表级锁 & 行级锁

表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度
低。
行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并
发度高。

排它锁和共享锁

排它锁（Exclusive）：X 锁，写锁。
共享锁（Shared）：S 锁，读锁。

SS 兼容，XS、XX互斥

显示加锁：select ... lock in share mode强制获取共享锁，select ... for update获取排它锁

A获取一把X锁

mysql> select * from user where id=7 for update;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  7 | zhang ai |  21 | W   |
+----+----------+-----+-----+
1 row in set (0.00 sec)

B尝试获得该行的读写锁发生死锁

mysql> select * from user where id=7 for update;

mysql> select * from user where id=7 lock in share mode;

InnoDB的行锁是加在索引上的，是给索引在加锁，并不是给单纯的行记录在加锁；如果过滤条件没有索引的话，是表锁而不是行锁。

mysql> select * from user where name='li si' for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行级锁

间隙锁（gap lock）

条件检索数据，并请求共享或排他锁时，InnoDB会对这个范围内的所有数据加锁。左开右闭

InnoDB使用间隙锁的目的：为了防止幻读，以满足串行化隔离级别的要求

主键索引

实验，都把隔离级别设置为“串行化”

mysql> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

这样是OK的：

A
mysql> select * from user where id < 10;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  4 | zhang san   |  21 | M   |
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
|  7 | zhang ai    |  21 | W   |
|  8 | wu liu      |  21 | M   |
|  9 | wu qi       |  22 | M   |
+----+-------------+-----+-----+
6 rows in set (0.00 sec)

B
mysql> insert into user(name,age,sex) values ('can2',22,'W');
Query OK, 1 row affected (0.00 sec)

这样就触发了间隙锁

A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 10;
+----+--------+-----+-----+
| id | name   | age | sex |
+----+--------+-----+-----+
| 11 | wu jiu |  23 | M   |
| 12 | tt     |  21 | W   |
| 13 | can    |  22 | W   |
+----+--------+-----+-----+
3 rows in set (0.00 sec)

B
mysql> insert into user(name,age,sex) values ('can2',22,'W');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

辅助索引

需要注意的是，辅助索引树上有主键，两个组合形成间隙锁。

下面的这个例子不符合预想

A 的间隙锁包含 id
mysql> select * from user where age > 20;

B 虽然age满足条件，id也满足条件，但是为什么任然插不进去呢？
mysql> insert into user(name, age, sex) values('t2', 19, 'M');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

理论上要能够插入age<20的数据，可是我无论如何也插不进去，终于，找到了原因！

因为我的数据age几乎都大于20，explain后发现，MySQL并没有使用索引！

mysql> explain select * from user where age > 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: age_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 18
     filtered: 66.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

试试age > 23的情况？

mysql> explain select * from user where age > 23\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: age_idx
          key: age_idx
      key_len: 1
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

用到了索引！实验可以继续下去了！

实验如下，

A 范围搜索 + age有辅助索引树。所以它会产生双重组合而成的间隙锁
mysql> select * from user where age > 23;
+----+-------+-----+-----+
| id | name  | age | sex |
+----+-------+-----+-----+
| 10 | wu ba |  24 | M   |
+----+-------+-----+-----+
1 row in set (0.00 sec)

B 24>age:不能插入  22<age:能直接插入  23虽然等于age,但是id>辅助索引树左端点的id
mysql> insert into user(name,age,sex) values('xx',24,'M');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into user(name,age,sex) values('xx',22,'M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(name,age,sex) values('xx',23,'M');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

左边的间隙也有锁。下图是我表里的数据分布，select * from user where age>26;
间隙锁一直到了前面的24

mysql> insert into user(name,age,sex) values('xx',25,'M');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into user(name,age,sex) values('xx',23,'M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(name,age,sex) values('xx',24,'M');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

Post Views: 127