mysql各种锁解释

mysql锁有行锁，表锁，间隙锁，乐观锁，下面我用实际操作来解释下这几种锁的区别

首先建一个innodb的表，并初始化

CREATE TABLE `user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT '0',
	`status` VARCHAR(50) NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
INSERT INTO `user` (`id`, `name`, `status`) VALUES (1, '张三', '0');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (2, '李四', '0');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (3, '王五', '0');

1，行级锁

首先打开一个mysql命令窗口开启一个新事务锁定数据（先别急着commit）

begin;
select * from user where id=3 for update;

然后在另一个mysql命令窗口修改数据

update user set name='jack' where id=3;

这时候会发现update操作会卡住，直到在第一个命令窗commit释放事务才能执行完成，这就是行级锁。

不过如果等待锁时间过长，update操作会自动失败：

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

2，表锁

在第一个命令窗查询张三这条数据（不提交事务）

begin;
select * from user where name='张三' for update;

在第一个窗户执行修改王五数据

update user set status='2' where name='王五';

会发现修改操作又卡死了，明明修改的是其他数据，因为where条件name不是索引字典，如果where条件里都是索引字段，就是行级锁，否则就是表锁，会让整个表不可修改。

3，间隙锁（mysql默认隔离级别是可重复读，所以会默认加间隙锁）

间隙锁锁定的索引的范围，我们给表加个索引，再初始化点数据

ALTER TABLE `user`
	ADD INDEX `status` (`status`);
INSERT INTO `user` (`id`, `name`, `status`) VALUES (4, 'q', '1');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (5, 'w', '2');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (6, 'e', '4');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (7, 'r', '6');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (8, 't', '7');
INSERT INTO `user` (`id`, `name`, `status`) VALUES (9, 'y', '8');

在第一个命令窗查询两条数据（不提交事务）

begin;
delete from user where status='4';

另一个命令窗开始修改(也不提交事务）

begin;
INSERT INTO `user` (`id`, `name`, `status`) VALUES (10, 'u', '3');

发现插入卡住了，为毛在一个事务里删除了status为4的数据会导致另一个事务里的数据无法插入呢？

是因为status是有索引的，对status为4的数据做修改后间隙锁会把2<status<6这块数据锁住。如下图：

此时任何insert或者update在这个范围的都会等待，直到上个事务提交释放这个锁。

4，乐观锁

这个锁就是业务概念

命令行一

update user set status ='5' where id=5 and status='2';

执行结果：影响条数1

命令行二

update user set status ='5' where id=5 and status='2';

执行结果：影响条数0

原因就是把要修改数据的旧值作为条件，如果不满足就不修改。

tips:即使多请求并发执行，开启事务后，也只有一个请求会处理，其他阻塞，然后只有一个请求影响条数大于0。等因为开了事务会根据条件找到row，然后锁索引，无索引就锁聚族索引（也就是主键）

备注：除此以外mysql还有next key lock，Insert Intention lock,AUTO-INC Lock