mysql for update 锁类型探索

通过几个实验探索一下mysql for update锁的类型

表结构

CREATE TABLE `test_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login_name` varchar(20) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_login_name` (`login_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

记录

引用

insert into `test_user` (`id`, `login_name`, `name`) values('1','zhangsan','张三');
insert into `test_user` (`id`, `login_name`, `name`) values('2','lisi','李四');

实验1
session_1

begin;
select * from test_user where login_name = 'zhangsan' for update;

+----+------------+--------+
| id | login_name | name   |
+----+------------+--------+
|  1 | zhangsan   | 张三   |
+----+------------+--------+

session_2

begin;
select * from test_user where login_name = 'zhangsan' for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

实验2
session_1

begin;
select * from test_user where login_name = 'zhangsan' for update;
+----+------------+--------+
| id | login_name | name   |
+----+------------+--------+
|  1 | zhangsan   | 张三   |
+----+------------+--------+
1 row in set (0.00 sec)

session_2

select * from test_user where login_name = 'lisi' for update;
+----+------------+--------+
| id | login_name | name   |
+----+------------+--------+
|  2 | lisi       | 李四   |
+----+------------+--------+
1 row in set (0.00 sec)

从实验1和实验2可以看出，对于有唯一索引的列，select for update 加的是排他锁，行级锁。锁定后，可以对其他行进行for update的查询

实验3
session_1

begin;
select * from test_user where login_name = 'bucunzai' for update;
Empty set (0.00 sec)

session_2

begin;
select * from test_user where login_name = 'bu4' for update;
Empty set (0.00 sec)

insert into test_user (login_name,name) values ('bu4','bu2name');
阻塞等待

session_1

commit;
提交

session_2

Query OK, 1 row affected (20.62 sec)
执行成功

实验4
session_1

begin;
select * from test_user where login_name = 'bucunzai' for update;
Empty set (0.00 sec)

session_2

select * from test_user where login_name = 'zhangsan' for update;

+----+------------+--------+
| id | login_name | name   |
+----+------------+--------+
|  1 | zhangsan   | 张三   |
+----+------------+--------+
1 row in set (0.00 sec)

update test_user set name = '张三丰' where login_name = 'zhangsan'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

从实验3和实验4可以看出，for update 语句对于空记录加共享锁，此时其他线程不能插入，可以进行查询操作。 对于已有记录没有加锁，其他线程可以对记录进行修改。