MySQL RR模式下如何加锁

锁的算法有三种，如下：

record lock、gap lock、next_key lock

在不同的隔离级别下，所使用的锁的算法如下：

RC：仅有record 锁

RR：有record和next_key锁

行锁都是基于索引来实现的

现在我们就来讨论在RR模式下，各种SQL语句的锁的记录范围：

create table t1(
id int primary key auto_increment,
col1 int not null default 0,
col2 varchar(20) not null default '',
col3 datetime not null default '2010-01-01 00:00:00',
unique key(col1),
key(col2));

首先写个生产测试数据的存储过程：

delimiter $$
create procedure proc_generate_data(in i_rec_num int,in i_prefix_name varchar(20),in i_is_order tinyint)
begin
    declare rec_num int;
    declare prefix_name varchar(20);
    declare is_order tinyint;
    declare i int;
    set i=0;
    set rec_num=i_rec_num;
    set prefix_name=i_prefix_name;
    set is_order=i_is_order;
    loop1:while (i<rec_num) do
        set @c1=floor(round(rand(),5)*100000);
        set @c2=concat(prefix_name,@c1);
        set @c3=now();
        if is_order = 0 then
            insert into t1(col1,col2,col3)values(@c1,@c2,@c3);
        elseif is_order = 1 then
            set @id= floor(round(rand(),5)*100000);
            select 1 into @if_exist from t1 where id=@id;
            if @if_exist != 1 then
                insert into t1(id,col1,col2,col3)values(@id,@c1,@c2,@c3);
            else
                insert into t1(col1,col2,col3)values(@c1,@c2,@c3);
            end if;
        else
            insert into t1(col1,col2,col3)values(2000,'nihao',@c3);
        end if;
        set i= i+1;
    end while loop1;
end $$
delimiter ;

 生成测试数据：

顺序生成： call proc_generate_data(10000,'first',0);

随机生成： call proc_generate_data(10000,'second',1);

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      628 |
+----------+
1 row in set (0.00 sec)
alter table t1 rename t1_1;
create table t1 like t1_1;

 

测试说明：

间隙锁定的表现是：间隙允许update不允许insert

类目	常见句式	结论
主键|唯一索引	update t1 set col2='OK' where id=1001; update t1 set col2='OK' where col1=9800;	1.若对应的id值存在，锁定primary key& unique key对应的record； 若DML最终涉及了primary key& unique key对应的record，还是会被堵塞； DML走的是全表扫描，会被堵塞； 2.若对应的id值不存在，对primary key&unique key对应的record允许update不允许insert；
	update t1 set  col2='OK' where id<1001;	锁住where范围内的primary key&unique key对应的record
普通索引	update t1 set col2='OK' where  col2='first1234';	1.若对应的col2 record存在，对（col2_last_value,col2_value)及（col2_value,col2_next_value)产生间隙锁定；并对col2_value产生record锁定； 2.若对应的col2 record不存在，对(col2_last_value,col2_next_value)产生间隙锁定；
	update t1 set col2='OK' where  col2>'first1234';	对(负无穷大，col2_value)产生间隙锁定，并对范围内的行的主键进行record锁定；
无索引	update t1 set col2='ERROR' where col3<'2017-09-30 10:00:00';	锁全表；
INSERT	insert into t1 values(650,650,'six650','2017-09-30 15:10:00'); insert into t1 values(),(),(),(),();	锁住primary key& unique key对应的record
	insert into t1 select * from t1_1;	1.事务期间，锁定操作主键，唯一索引对应的record、间隙及以外的部分内容； 2.事务期间，锁定操作主键，唯一索引对应的间隙（间隙锁定）

 

总结：

1.  主键索引有record lock

2.  唯一辅助索引有record lock

3.  非唯一辅助索引有next-key lock

4.  没有索引的话，则是全表范围的next-key lock

5.  RC下只有record lock

6.  RR&innodb_locks_unsafe_for_binlog=1，只有record lock.