mysql 行级锁深入分析

locking.png

• schema
  主要是user_id 和 phone 用的是联合索引

CREATE TABLE `user_contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_user_id_phone` (`user_id`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

• 测试的数据

mysql> select * from user_contact;
+----+---------+------+-------------+------+---------------------+
| id | user_id | name | phone       | age  | created_time        |
+----+---------+------+-------------+------+---------------------+
|  1 |       1 | test | 13127578831 |   12 | 2017-12-15 17:23:09 |
|  2 |       1 | test | 13127578832 |   13 | 2017-12-15 17:23:09 |
|  3 |       1 | test | 13127578890 |   14 | 2017-12-15 17:23:09 |
+----+---------+------+-------------+------+---------------------+
3 rows in set (0.00 sec)

• 查看mysql innodb 事务获取锁的等待超时时间
  下面可以看到innodb默认事务获取锁的等待超时时间是50秒，我们手动设置成5秒，方便我们排查，实际在互联网公司这个超时时间也不宜设置过长。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql> set innodb_lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 5     |
+--------------------------+-------+
1 row in set (0.01 sec)

• 设置数据库事务隔离级别为读已提交(read-committed)，默认为Repeatable Read 可重复读

set tx_isolation='read-committed';

验证1

• 事务1 执行更新不提交
  注意这里要关闭自动提交,需要我们显示的调用commit命令才提交事务，下面的事务也是一样。

mysql> SET AUTOCOMMIT=0;#禁止自动提交
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> update user_contact set name = 'test' where user_id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0
执行到这的实时 这个事务1获取了`user_id`为1的这些记录的写锁，
其实mysql锁是通过索引加的，此时事务1还没提交所以一直占有着锁

• 事务2 执行更新

mysql> SET AUTOCOMMIT=0;#禁止自动提交
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> update user_contact set name = 'test' where user_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
执行事务2的时候，因为事务1已结占有了`user_id`为1的这些记录的写锁
所以事务2需要等待，但是事务1一直没有释放写锁，所以事务2等待5秒后
超时，抛出一个异常

验证2

注意：进行验证2之前先把之前的事务commit掉，因为我们已经关闭掉自动提交了，所以需要手动提交，避免上次事务对本次事务测试有影响

• 事务1 执行更新 不提交

mysql> update user_contact set name = 'test1' where user_id = 1 and phone = '13127578831';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
事务1执行这这里时候其实是把 user_id = 1的 phone= '13127578831'
这条记录加了行锁，此时还没显示commit所以事务1任然持有这把锁

• 事务2 执行更新 不提交

mysql> update user_contact set name = 'test1' where user_id = 1 and phone = '13127578832';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
事务2需要更新的是user_id = 1 and phone = '13127578832'
和事务1不是同一条记录所以不会有问题

• 事务3 执行更新

mysql> mysql> update user_contact set name = 'test1' where user_id = 1 and phone = '13127578831';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
再起一个窗口去执行事务3，和事务1的语句一样，发现获取锁超时
因为事务1还没有释放这条记录的写锁

• 事务4 执行查询

mysql> select * from user_contact where user_id = 1 and phone = '13127578831';
+----+---------+------+-------------+------+---------------------+
| id | user_id | name | phone       | age  | created_time        |
+----+---------+------+-------------+------+---------------------+
|  1 |       1 | test | 13127578831 |   12 | 2017-12-15 17:37:08 |
+----+---------+------+-------------+------+---------------------+
1 row in set (0.00 sec)
执行事务4去查询事务1更新的那条记录，发现可以查询成功
这是因为事务1获取的是写锁，而此时事务4是进行读操作
所以不会对读有影响，事务1把name更新成`test1`，但是
在事务4看不到，这是因为事务1没有提交，这又验证了
事务之间的隔离性

• 事务1 提交

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
此时在事务1的终端commit 提交事务

• 事务4 再次查询

mysql> select * from user_contact where user_id = 1 and phone = '13127578831';
+----+---------+-------+-------------+------+---------------------+
| id | user_id | name  | phone       | age  | created_time        |
+----+---------+-------+-------------+------+---------------------+
|  1 |       1 | test1 | 13127578831 |   12 | 2017-12-15 17:53:24 |
+----+---------+-------+-------------+------+---------------------+
1 row in set (0.00 sec)
由于事务1已经提交，此时对应name应该更新，事务4可以看到效果

验证innodb锁加在索引上

• 事务1 执行更新不提交

mysql> update user_contact set name = 'test2' where age = 12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
事务1更新年龄为12的记录，可以看到更新的其实是
第一条记录

• 事务2 执行更新不提交

mysql> update user_contact set name = 'test2' where age = 13;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
事务2更新年龄为13的记录，可以看到更新的其实是
第二条记录，但是此时事务2超时，其实是age字段
没有索引，mysql锁是在索引上的，age没有索引，
事务1执行了更新操作其实是锁表的

-事务3 执行更新不提交

mysql> update user_contact set name = 'test2' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
其实事务3这个更新操作和事务2做的是同样的事情
只是这里where 条件是id 走的索引，但是我上面分析了，
此时事务1锁住了这表格表所以导致了所有更新都要等待

• 给age字段添加索引

mysql> alter table user_contact add index idx_age(age);
Query OK, 0 rows affected (0.04 sec)

• 事务1commit后再次执行

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

mysql> update user_contact set name = 'test2' where age = 12;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

• 事务2commit后再次执行

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

mysql> update user_contact set name = 'test2' where age = 13;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

可以看到age字段加了索引后事务1和事务2都可以执行了，不会导致整张表了

有几点需要注意的地方:

• 锁是加在索引上的
• 我们这里 KEY idx_user_id_phone (user_id,phone) 是联合索引，所以只会锁user_id 和 phone的对应记录，即，user_id等于传入的值且phone等于传入的值，如果是分开的索引，会锁所有user_id = 传入的值，和所有phone = 传入的值，因此联合索引优势大大的