SQL优化之锁机制

锁机制

解决因资源共享造成的并发问题

示例：某网购平台平台仅剩一件衣服X
两个Women ：WA、 WB
WA ： X 抢购 X加锁===>下单===>付款===>卖家收到订单===>抢购完成
WB ： X 抢购 X已经加锁===>等待阻塞中===>WA付款之后===>提示已经售空

锁分类

按照操作类型分

1. 读锁（共享锁）===>对于同一个数据（衣服X），多个操作可以同时进行，互不干扰。
2. 写锁（互斥锁）===>若当亲操作没有完成（WA准备对X下手），WB不能进行读写操作。（甚至不能看了）

按照操作范围分

1. 表锁（一次操作锁一张表）MyISAM存储引擎用的是表锁
	1.1. 优势分析： 开销小，加锁快，无死锁.
	1.2. 不足分析：粒度粗,容易放生锁的冲突，并发度低
2. 行锁（一次操作锁一条数据）InnoDB存储引擎使用行锁（MySQL默认）
	2.1. 优势分析：锁的使用粒度细，不容易放生锁冲突，并发度高
	2.2.不足分析：开销大，加锁慢，易死锁。
	2.3 小概率问题：很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题
3. 页锁

数据准备

表锁

读锁特性

create table tablelock
(
id int primary key auto_increment , 
name varchar(20)
)engine myisam;


insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');
commit;
-- 增加所的语法
lock table 表1 read/write ,表2 read/write;

--查看加锁的表
show open tables;

	lock table tablelock read;-- 加锁

看看我们加了一个读锁，还能不能增删改了？

会话0（当前本地会话）

		lock table  tablelock read ;
		select * from tablelock; --读（查），可以
		delete from tablelock where id =1 ; --写（增删改），不可以
		select * from emp ; --读，不可以
		delete from emp where eid = 1; --写，不可以

结论1：

		1.如果这个会话0 对A表加了read锁，则该会话 可以对A表进行读操作、不能进行写操作； （出了对自己查之外，其他操作一律不行）
		2.且该会话不能对其他表进行读、写操作。（对其他表甚至不能查了）

其他会话

		select * from tablelock;   --读（查），可以
		delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放
		select * from emp ;  --读（查），可以
		delete from emp where eno = 1; --写，可以	

结论2

	若本地会话（会话0）给A表加了读锁，其他会话想要访问的话会得到如下结论：
	1.这个会话（指代其他会话）可以对其他表进行读写操作（会话0不可以操作其他表）
	2. 对于当前加锁的表A
		读===> 可以读。（跟会话0一样）
		写===> 也可以，但是需要会话0释放锁

	unlock tables ;-- 释放锁

写锁特性

会话0（目前会话）

	lock table tablelock write;-- 加写锁指令

结论

	1. 当前会话（会话0）可以对加了写锁的表进行CRUD操作，但不可对其他表进行操作
	2. 其他会话可以进行增删改查，前提是：会话0释放锁。

MySQL表级锁的锁模式

• MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁。
• 在执行更新操作（DML）前，会自动给设计的表加写锁
• 对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，
  但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
• 对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，
  只有当写锁释放后，才会执行其它进程的读写操作。

表锁分析

• List item

	--查看哪些表加了锁：   
	show open tables ; --  1代表被加了锁
	--分析表锁定的严重程度： 
	show status like 'table%' ;
	Table_locks_immediate -- 即可能获取到的锁数
	Table_locks_waited  -- 需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
	引擎选用建议：
	Table_locks_immediate/Table_locks_waited > 5000，
	-- 建议采用InnoDB引擎（代表并发高，用行锁，粒度细问题少）
	--否则MyISAM引擎（并发不高，用表锁，内存开销小）

行锁

create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values('1')  ;
insert into linelock(name) values('2')  ;
insert into linelock(name) values('3')  ;
insert into linelock(name) values('4')  ;
insert into linelock(name) values('5')  ;
-- 因为MySQL自动提交 为了研究行锁需要关闭自动提交
set autocommit =0 ;

会话0(当前会话)==>写数据

	 insert into linelock  values(7,'shu');--  写入数据到数据库 ID是7

会话1（其他会话）===>操作数据

update linelock set name='ax' where id = 7;

等待一段时间就超时了

	那么我们将这个数据提交一下，再看看

	显然这个操作成功了

上面我们操作的是同一条数据，那么操作不用数据呢?

行锁，操作不同数据：
	
	会话0： 写操作
	
		insert into linelock values(8,'a8') ;
	会话1： 写操作， 不同的数据
		update linelock set name='ax' where id = 5;
		--行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。

行锁的注意事项

如果没有索引，则行锁会转为表锁

	show index from linelock ;
	alter table linelock add index idx_linelock_name(name);
会话0： 写操作
		update linelock set name = 'ai' where name = '3' ;
		
	会话1： 写操作， 不同的数据
		update linelock set name = 'aiX' where name = '4' ;
	

	
	会话0： 写操作
		update linelock set name = 'ai' where name = 3 ;
		
	会话1： 写操作， 不同的数据
		update linelock set name = 'aiX' where name = 4 ;

结论

• 可以发现，数据被阻塞了（加锁）
• 原因：如果索引类 发生了类型转换，则索引失效。 因此 此次操作，会从行锁 转为表锁。

行锁特殊情况（间隙锁）（本该在范围，实际不在）

 --即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
update linelock set name ='x' where id >1 and id<9 ;  
	间隙：Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁（行锁）。
	行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）
	
	--如何仅仅是查询数据，能否加锁？ 可以通过
	   for update 
	
	研究学习时，将自动提交关闭：
		set autocommit =0 ;
		start transaction ;
		begin ;
	 select * from linelock where id =2 for update ;

	-- 通过for update对query语句进行加锁。

行锁结论

InnoDB默认采用行锁
缺点：相比表锁性能损耗大，因为粒度更细
有点：并发能力强，效率高
建议：高并发使用InnoDB，否则用MyISAM

行锁分析

	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 ：	等待次数。从系统启到现在一共等待的次数