表结构如下:
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`user_name` varchar(50) NOT NULL COMMENT '用户名',
PRIMARY KEY (`user_id`),
KEY `user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf
user_name为非唯一索引。
为了解决user_name高并发时候同名的情况,程序员在一个事务中执行以下sql语句:
select * from t_user where user_name = 'oliver' for update;
insert into t_user(user_name,register_type) values('oliver',1);
在测试的时候有死锁现象发生,测试的时候关掉自动提交标志位,即执行sql语句如下:
按这个顺序
事务1 事务2
selelct for update... select for update...
insert into ...
insert into ...
结果必然死锁
究其原因,
执行这句:select * from t_user where user_name = 'oliver' for update;
会对user_name这个索引值为oliver的记录加一个意向独占锁,
执行这句:insert into t_user(user_name,register_type) values('oliver',1);
首先向聚集索引还有加数据,两个线程都是成功的。
然后向user_name索引上添加数据,这个时候因为两个线程对user_name索引值为oliver的记录进行意向独占锁了,与新插入时要求的独占锁相冲突,所以会互相等待对方的user_name索引上oliver这条记录锁被释放。
这个过程是很快的,只有在高并发的时候,才有可能会出现这种情况,建议需要唯一索引的,还是单独建一张表,建立唯一索引