MySql锁机制

锁的分类
按操作分：读锁/写锁
按粒度分：表锁/行锁

---

表锁

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

读锁

//建表
mysql> create table mylock(
    -> id int not null primary key auto_increment,
    -> name varchar(20))engine myisam;
mysql> create table book(
    -> id int primary key auto_increment,
    -> name varchar(20))engine myisam;
//插入数据
mysql> insert into mylock(name) values('a');
mysql> insert into mylock(name) values('b');
mysql> insert into mylock(name) values('c');
mysql> insert into mylock(name) values('d');
mysql> insert into mylock(name) values('e');

//查看锁的情况
mysql> show open tables;

//给mylock加读锁，book加写锁
mysql> lock table mylock read, book write;

//查看锁情况
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| mylock             | mylock                                               |      1 |           0 |
| mylock             | book                                                 |      1 |           0 |

//解锁
mysql> unlock tables;

//查看锁
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| mylock             | mylock                                               |      0 |           0 |
| mylock             | book                                                 |      0 |           0 |

//给mylock加读锁
mysql> lock table mylock read;
//用加锁的连接尝试读取
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
//用加锁的连接尝试修改
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
//用加锁的连接尝试读别的表，同样报错
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

//另一条连接
//读mylock
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+

//更新mylock，此时这条连接被阻塞，直到加锁的那条连接解锁
mysql> update mylock set name = 'a2' where id = 1;

写锁

//加写锁
mysql> lock table mylock write;
//自己读 ok
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
//自己写 ok
mysql> update mylock set name = 'a3' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
//自己读其他表，报错
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

//另一条连接
//读取别的表
mysql> select * from book;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
+----+------+
//读取，阻塞，直到锁释放
mysql> select * from mylock;

读锁会阻塞写，写锁会同时阻塞读和写

分析表锁

mysql> show status like "table%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 318   |
| Table_locks_waited         | 0     |

//Table_locks_immediate表示产生表级锁定的次数
//Table_locks_waited表示出现表级锁定竞争而发生等待的次数

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

---

行锁

偏向innodb存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高

//建表
mysql> create table test_innodb_lock(
    -> a int(11),
    -> b varchar(16))engine=innodb;
//建索引
mysql> create index test_innodb_a_ind on test_innodb_lock(a);
mysql> create index test_innodb_lock_b_ind on test_innodb_lock(b);

//下面2条连接，成为A和B

//A,B连接关闭自动提交
mysql> set autocommit = 0;
//A连接进行修改
mysql>  update test_innodb_lock set b = 1111 where a = 3;
//A连接查看数据
+------+----------+
| a    | b        |
+------+----------+
|    3 | 1111     |
//B连接连接查看数据,发现数据并未修改，
+------+----------+
| a    | b        |
+------+----------+
|    3 | a4     |
//A连接提交
mysql> commit;
//B连接还是看不到数据，因为B连接还在事务里，没有提交，保证可重复读
//B连接提交
mysql> commit;
//B连接查看数据，发现已经被修改
+------+----------+
| a    | b        |
+------+----------+
|    3 | 1111     |
//A连接进行修改
mysql>  update test_innodb_lock set b = 520 where a = 3;
//B连接进行修改，被阻塞（注意，是对同一行进行修改，若不是同一行，不会阻塞）
mysql> update test_innodb_lock set b = 521 where a = 3;
//A连接commit后，B成功执行
+------+----------+
| a    | b        |
+------+----------+
|    3 | 521     |

---

索引失效，行锁变表锁

//A连接进行更新操作，但是由于where后条件是varchar但是没加引号，导致索引失效，造成锁表
mysql> update test_innodb_lock set a = 8888 where b = 22;
//B连接也进行更新，不和A更新同一行，但是也被阻塞了，因为此时是表锁
mysql> update test_innodb_lock set a = 6666 where b = 33;

---

间隙锁

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)

InnoDB也会对这个间隙加锁，这就是间隙锁

//查询记录，可以看到，a为5的这一项是没有的
mysql>  select * from test_innodb_lock;

+---+------+
| a | b    |
+---+------+
| 1 | 11   |
| 2 | 22   |
| 3 | 33   |
| 4 | 44   |
| 6 | 66   |
| 7 | 77   |
| 8 | 88   |
+---+------+

//A连接现在进行范围操作，从3到6
mysql> delete from test_innodb_lock where a > 3 and a < 6;
//B连接进行插入操作，插入a为5的一行，发现被阻塞了，说明5虽然没有，但是也被加锁了
mysql> insert into test_innodb_lock values(5,'55');

---

如何给一行加锁

//在事务中，后面加上for update即可锁住改行直到commit
mysql> select * from test_innodb_lock where a = 1 for update;

查看行锁数据

mysql> show status like 'innodb_row_lock%';

// Innodb_row_lock_waits 为等待锁次数


+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 208725 |
| Innodb_row_lock_time_avg      | 34787  |
| Innodb_row_lock_time_max      | 51025  |
| Innodb_row_lock_waits         | 6      |
+-------------------------------+--------+