007 mysql行级锁

step1 行级锁介绍

        行级锁是由存储引擎实现(InnoDB存储引擎实现)，InnoDB的行级锁，按照锁定范围来说，分为三种 ；

1. 记录锁（Record Locks）:锁定索引中一条记录(主键指定where id=3)
2. 间隙锁（Gap Locks）: 锁定记录前、记录中、记录后的行 
3. Next-Key 锁:(只出现在RR隔离级别中) 记录锁 + 间隙锁

step2 行级锁分类

1.共享读锁(S)[手动加]

        允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

--手动添加
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享读锁 手动添加
select * from table -- 无锁

2.排他写锁（X）

        允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是读）和排他写锁。

• 自动加 DML，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

• 手动加排他写锁

3.InnoDB实现的表级锁与行关系

        InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。

• 意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的IX锁。

        意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁。

step3 两阶段锁（2PL）

锁操作分为两个阶段，加锁阶段与解锁阶段，加锁阶段与解锁阶段不相交。

• 加锁阶段：只加锁，不放锁。

• 解锁阶段：只放锁，不加锁。

step4 行锁演示

        InnoDB行锁是通过给索引上的索引项加锁来实现，因此InnoDB这种行锁实现特点意味着只有通过索引条件检索的数据，InnoDB才使用行级锁，否则InnoDB将使用表锁！(where条件必须是索引才能加行锁，否则会使用表锁,锁住表并发无法进入表;)【没索引不支持行锁】

1.行读锁

--session1（mysql终端1）、session2（mysql终端2）
--查看行锁状态 
show STATUS like 'innodb_row_lock%';
--Innodb_row_lock_current_waits：当前正在等待锁定的数量；
--Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
--Innodb_row_lock_time_avg：每次等待所花平均时间；
--Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
--Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
--1、session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --手动加id=1的行读锁,使用索引
--2、session2：
update mylock set name='y' where id=2; -- 未锁定该行可以修改
--3、session2：
update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 锁定超时
--4、session1: 
commit; --提交事务 或者 rollback 释放读锁
--5、session2：
update mylock set name='y' where id=1; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--注：使用索引加行锁 ，未锁定的行可以访问

2.行读锁升级为表锁

 

--session1（mysql终端1）、session2（mysql终端2）
--1、session1: begin;--开启事务未提交
--手动加name='c'的行读锁,未使用索引
select * from mylock where name='c' lock in share mode;
--2、session2：
update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表锁
--3、session1: 
commit; --提交事务 或者 rollback 释放读锁
--4、session2：
update mylock set name='y' where id=2; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--注：未使用索引行锁升级为表锁

3.行写锁

--session1（mysql终端1）、session2（mysql终端2）
--1、session1: begin;--开启事务未提交
--手动加id=1的行写锁,为演示效果使用select...for update(使用update语句事务效果一致，就是为加一个写锁)
select * from mylock where id=1 for update;
--2、session2：
select * from mylock where id=2 ; -- 可以访问
--3、session2: 
select * from mylock where id=1 ; -- 可以读 不加锁(MVCC)
--4、session2: 
select * from mylock where id=1 lock in share mode ; -- 加读锁被阻塞
--5、session1：
commit; -- 提交事务 或者 rollback 释放写锁
--5、session2：执行成功
--主键索引产生记录锁

4.间隙锁

--session1（mysql终端1）、session2（mysql终端2）
create table news (id int, number int,primary key (id));
insert into news values(1,2);
--...
--加非唯一索引
alter table news add index idx_num(number);
--session 1:
start transaction ;
select * from news where number=4 for update ;
--session 2:
start transaction ;
insert into news value(2,4);--（阻塞）
insert into news value(2,2);--（阻塞）
insert into news value(4,4);--（阻塞）
insert into news value(4,5);--（阻塞）
insert into news value(7,5);--（执行成功）
insert into news value(9,5);--（执行成功）
insert into news value(11,5);--（执行成功）
--````
--注：id和number都在间隙内则阻塞。

• 示例：使用不存在的值生产间隙锁

--session 1:
start transaction ;
select * from news where number=13 for update ;

--session 2:
start transaction ;
insert into news value(11,5);--(执行成功)
insert into news value(12,11);--(执行成功)
insert into news value(14,11);--(阻塞)
insert into news value(15,12);--(阻塞)
--检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大）
--注：非主键索引产生间隙锁，主键范围产生间隙锁

• 如果使用主键锁定一行，那么产生的是记录锁只锁定一行，不产生间隙锁；

 select * from news where id=3 for update;

• 使用主键锁定范围，会产生间隙锁；

select * from news where id>1 and id < 4 for update

5.死锁

        两个 session 互相等待对方的资源释放之后，才能释放自己的资源,造成了死锁;

 

--session1（mysql终端1）、session2（mysql终端2）
--1、session1: begin;--开启事务未提交
--手动加行写锁 id=1 ，使用索引
update mylock set name='m' where id=1;
--2、session2：
begin;--开启事务未提交
--手动加行写锁 id=2 ，使用索引
update mylock set name='m' where id=2;
--3、session1: 
update mylock set name='nn' where id=2; -- 加写锁被阻塞
--4、session2：
update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction