锤爆MySQL之锁

锁的分类

按锁的粒度来分：

• 全局锁：作用于整个database，由sql layer层实现

• 表级锁：作用于整个table，由sql layer 层实现。开销小，加锁快；不会出现死锁；锁定粒度大，发省锁冲突的概率最高，并发度最低；

• 行级锁：作用于某行，或者行间隙，由某层存储引擎实现。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
  按功能分：

• 共享读锁

• 排他写锁
  按实现方式分：

• 1悲观锁

  • 1. 表级锁
    • 表锁（MySQL layer）手动加
      • read lock (加读锁以后可以加读锁，不可加写锁)
      • write lock (加写锁后，不可再加读锁和写锁)
    • 元数据锁（MySQL layer）自动加
      • CURD 加读锁
      • DDL加写锁
    • 意向锁（InnoDB）内部使用
      • 共享读锁（IS）
      • 排他写锁（IX）
    • 2. 行级锁
      • 共享读锁（S） 手动加
        • select …lock in share mode
      • 排他写锁（X） 自动加
        • DML(insert 、updata、delet)
        • select … for update

• 2乐观锁

  • 程序实现

MySQL表级锁

查看表级锁定争用状态变量：

 show status like 'table%';
 - table_locks_immediate：产生表级锁定的次数；
- table_locks_waited：出现表级锁定争争用而发生等待的次数；

手动增加表锁

lock table 'tablename1' read(write),'tablename2' read(write);

查看表锁情况

show open tables;

删除表锁

unlock tables;

1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session2：select * from mylock; -- 可以查询 没有锁
5、session2：update mylock set name='x' where id=2; -- 修改阻塞,自动动加行写锁
6、session1：unlock tables; -- 释放表锁
7、session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成
8、session1：select * from tdep; --可以访问

1、session1: lock table mylock write; -- 给mylock表加写锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session1：update mylock set name='y' where id=2; --可以执行
5、session2：select * from mylock; -- 查询阻塞
6、session1：unlock tables; -- 释放表锁
7、session2：4 rows in set (22.57 sec) -- 查询执行完成
8、session1：select * from tdep; --可以访问

元数据锁
MDL不需要显式使用，在访问一个表的时候会被自动动加上。MDL的作用是，保证读写的正确性。
在 MySQL 5.5 版本中引用了 MDL，当对一个个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执型。

1、session1: begin;--开启事务
 select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改，被阻塞
3、session1：commit; --提交事务 或者 rollback 释放读锁
4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成
 Records: 0 Duplicates: 0 Warnings: 0

begin 开始一个事务
commit 提交一个事务

行级锁

按照范围分

• 记录锁（Record Locks）:锁定索引中一条记录。 id=1
• 间隙锁（Gap Locks） ：要么锁住索引记录中间的值，要么锁住第一个索引记录前一的值或者最后一个索引记录后一的值。
• Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

按功能分：

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

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
自动添加共享锁（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

自动添加排他锁（x）：

SELECT * FROM table_name WHERE ... FOR UPDATE

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

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

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

	共享锁（s）	排他锁（x）	意向共享锁（IS）	意向排他锁（IX）
共享锁（s）	JR	CT	JR	CT
排他锁（x）	CT	CT	CT	CT
意向共享锁（IS）	JR	CT	JR	JR
意向排他锁（IX）	CT	CT	JR	JR

InnoDB 行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种⾏锁实现特点意味着：只
有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

Innodb所使用的行级锁定争用状态查看：

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：系统启动后到现在总共等待的次数；

两阶段锁

传统RDBMS加锁的一个原则，就是2PL (Two-Phase Locking，二阶段锁)。即锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

加锁阶段：只加锁，不放锁。（insert ,update delete 加锁）
解锁阶段：只放锁，不加锁。（释放掉所有操作增加的锁）

#查看行锁状态 
show STATUS like 'innodb_row_lock%'; 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
注：使用索引加行锁 ，未锁定的行可以访问

行读锁升级为表锁

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

行写锁

1、session1: begin;--开启事务未提交

 select * from mylock where id=1 for update;
 
2、session2：select * from mylock where id=2 ; -- 可以访问
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
 4、session2: select * from mylock where id=1 lock in share mode ; -- 加读
锁被阻塞
5、session1：commit; -- 提交事务 或者 rollback 释放写锁
5、session2：执行成功
主键索引产生记录锁

间隙锁
1、防止插入间隙内的数据
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 ;
			#select * from news where id>1 and id < 8 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，无穷大）
注：非主键索引产生间隙锁，作用于主键范围

死锁

两个会话session等待对方资源释放，才能释放自己的资源造成的死锁.

session1:
begin;  开启事务
update mylock set name="m" where id =1; 手动加锁（索引）
sesssion2:
begin;
update mylock set name="m" where id =2; 手动加锁（索引）
session1:   update mylock set name="nn" where id =2;
加写锁被堵塞
session2:    update mylock set name="nn" where id =1;
死锁，不允许操作

ERROR 1213(40001):DEADLOCK FOUND  WHEN TRYING TO GET LOCK;TRY RESTARTING TRANSACTION