MySQL锁机制

锁机制 ：解决因资源共享 而造成的并发问题。
示例：买最后一件衣服X
A: X 买 ： X加锁 ->试衣服…下单…付款…打包 ->X解锁
B: X 买：发现X已被加锁，等待X解锁， X已售空

1、锁的分类

操作类型：
a.读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。
b.写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作

操作范围：
a.表锁：一次性对一张表整体加锁。
如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。
b.行锁：一次性对一条数据加锁。
如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
c.页锁

2、表锁 （MyIsam）

建表：

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;

数据库中此时含有的表：

1、加读锁：

增加锁：

​	lock table 表1  read/write  ,表2  read/write   ,...

​查看加锁的表：

​	show open tables ;

​会话：session :每一个访问数据的dos命令行、数据库客户端工具  都是一个会话

会话0：

lock table  tablelock read ;-- 对tablelock表加读锁

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

-- 其他未加表锁的表
select * from book ; -- 读，不可以
delete from book where bid = 1; -- 写，不可以

结论1：-- 如果某一个会话 对A表加了read锁，则该会话：
    *  -- 可以对A表进行读操作、不能进行写操作； 且该会话不能对其他表进行读、写操作。
    * -- 即如果给A表加了读锁，则当前会话只能对A表进行读操作

会话1（其他会话）：

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

结论2：会话0给A表加了锁，其他会话：
	可以对其他表（A表以外的表）进行读、写操作
	对A表：读-可以；写-需要等待释放锁。

释放锁: unlock tables ;会话0释放锁后，会话1的tablelock就可以写操作了

会话0给tablelock表加了锁，其他会话1:

2、加写锁：

会话0：

lock table tablelock write ;
当前会话（会话0）：
   可以对加了写锁的A表进行任何操作（读写：增删改查）；但是不能操作（增删改查）其他表(除了A表以为的表)读写
其他会话：
		对会话0中加写锁的A表可以进行增删改查(读写)的前提是：等待会话0释放写锁
		对可以对其他表（A表以外的表）进行读、写操作

当前会话（会话0）：

会话1：

MySQL表级锁的锁模式：

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

分析表锁定：
查看哪些表加了锁： show open tables ; 1代表被加了锁
分析表锁定的严重程度： show status like ‘table%’ ;
Table_locks_immediate :即可能获取到的锁数
Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
一般建议：
Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎

3、行锁（InnoDB）

InnoDB默认使用行锁，行锁是锁住某一行数据。在DML时(update,insert,delete)InnoDB会默认给我们加行锁。
行锁在索引失效的情况下，没有索引，会转化为表锁。

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默认自动commit; oracle默认不会自动commit ;

为了研究行锁，暂时将自动commit关闭; set autocommit =0 ; 以后需要通过commit

-- 行锁：操作相同的数据：

会话0： 写操作（增删改都叫写操作）
	update linelock set name='ay' where id = 6;
会话1： 写操作 同样的数据
	update linelock set name='ax' where id = 6;

-- 对行锁情况：
	1.如果会话0对某条数据a进行DML操作（研究时：关闭了自动commit的情况下），
		则其他会话必须等待会话0结束事务(commit/rollback)后才能对数据a进行操作。
	2.表锁是通过unlock tables，也可以通过事务解锁 ; 
	3.行锁是通过事务解锁（commit/rollback提交数据到库里/回滚到上一个库的数据）。
	
-- 行锁，操作不同数据：

会话0： 写操作
	update linelock set name='a8' where id = 8;
会话1： 写操作， 不同的数据
	update linelock set name='ax' where id = 5;

	-- 行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。	

​

行锁的注意事项：

a.如果没有索引，则行锁会转为表锁（前面where后面是id是主键,会自动加索引）
	show index from linelock ;
	alter table linelock add index idx_linelock_name(name);	--给name加索引

-- 即使没有事务提交，操作不同数据，互不干扰(操作完数据，最好commit,因为我们关闭了自动提交)
会话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 ;
	
--可以发现，数据被阻塞了（加锁）
-- 原因：如果索引类 发生了类型转换，则索引失效。 因此此次操作，会从行锁转为表锁。因为会话1写操作的数据还是同一张表，不是其他表，所以表锁写操作同一张表，需要等待会话0释放锁(事务解锁)

b.行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
 --此时linelock表中 没有id=7的数据
 update linelock set name ='x' where id >1 and id<9 ;   
 --即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
 
间隙：Mysql会自动给间隙加索 ->间隙锁。即本题会自动给id=7的数据加间隙锁（行锁）。

行锁：如果有where，则实际加锁的范围 就是where后面的范围（不是实际的值）

​ 如何仅仅是查询数据(select语句)，能否加锁？ 可以 for update
​
​ 研究学习时，将自动提交关闭：
​ set autocommit =0 ;
​ start transaction ;
​ begin ;
​ select * from linelock where id =2 for update ;
​
​ 通过for update对select语句进行加锁。
​
​ 行锁：
​ 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 ： 等待次数。从系统启到现在一共等待的次数