mysql 行级锁深入分析
2017-12-15 本文已影响69人
freelands
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 = 传入的值，因此联合索引优势大大的
