17.MySQL锁

1.mysql锁分类介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）

2.锁的分类

(1)表锁 table lock : 锁定了整张表,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
(2)行锁 row lock : 锁定了需要的行,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
(3)页锁 page lock : 开销和加锁时间界于表锁和行锁之间;会岀现死锁;锁定粒度界于表锁和行锁之间并发度一般

下面是不同存储引擎所支持的锁

存储引擎                行锁            表锁            页锁
--------------------------------------------------------------
mysisam                                支持         
--------------------------------------------------------------
bdb                                    支持            支持
--------------------------------------------------------------
innodb                  支持           支持
--------------------------------------------------------------

加锁模式
读锁：又称共享锁，多个读操作可以同时施加，非阻塞；
写锁：又称独占锁或者排他锁，阻塞；

3.mysql各种锁
(1)共享锁 （S） shared lock
允许一个事务去读行,阻止其他事务获得相同数据集的排他锁;

Select * from xx where a = 1 lock in share mode;

(2)排他锁 （X） exclusive lock
允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁;

Select * from xx where a = 1 for update;

(3)意向锁 （intention locks）
为了允许行锁和表锁共存,实现多粒度锁机制, Innodb还有两种内部使用的意向锁,这两种意向锁都是表锁。
意向共享锁（IS）
事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的s锁,加到表上的。

table lock table <table_name> trx id 6686 lock mode is;

意向排他锁（IX）
事务打算给数据行加行排他锁,事务在给个数据行加排他锁前必须先取得该表的Ⅸ锁,加到表上的。

table lock table <table_name> trx id 6686 lock mode ix;

(4)行记录锁 （Record locks）
记录锁锁定的是索引记录,而不是行数据,也就是说锁定的是key,该锁是加在索引上的

(5)间隔锁 （Record locks）
Gap锁(间隙锁),锁住的不是记录,而是范围,一般是针对非唯一索引而言的,确保索引记录的间隙不变,间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
间隔锁的出现主要集中在同一个事务中先 delete后 inserte的情况下,当去删除条记录的时候,如果这个记录存在,那么这个时产生普通行锁,锁住这个记录,然后删除再释放锁

(6)行记录锁 + 间隔锁 NeXt-Key locks 
行记录锁和间隙锁组合起来就叫 Next-key Lock,不仅仅锁住记录,还会锁住间隙。
默认情况下, Innodb工作在可重复读隔离级别下,并且会以 Next-key Lock的方式对数据行进行加锁,这样可以有效防止幻读的发生。

(7)metadata lock 元数据锁,数据字典锁 
当你执行 select的时候,如果这时候有ddl语句,那么ddl会被阻塞,因为 select语句拥有 metadata lock,防止元数据被改掉。

(8)死锁 dead lock 
所谓死锁,是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象若无外力作用它们都将无法推进下去。
此时系统处于死锁状态或系统产生了死锁,这些在互相等待的进程称为死锁进程。
表级锁不会产生死锁,所以解决死锁主要还是针对于最常用的INNODB

4.mysql查看锁状态、锁相关参数
(1)手动生成一个排他锁（x）

select * from scott.s1 where id = 1 for update;

(2)使用命令查看锁的状态

set global innodb_status_output_locks=1;
show engine innodb status\G;

(3)锁相关参数

show variables like '%innodb%lock%';
innodb_print_all_deadlocks=on            --打开会记录死锁到 error-Log 
innodb_lock_wait_timeout=5                 --锁超时5秒 
innodb_deadlock_detect=on                 --开启死锁检査,数据库自动回滚
innodb_status_output_locks=on            --锁状态输出、默认关闭,建议开启 

5.mysql常见锁问题解决
有多少线程正在使用某张表 

show open tables where in_use > 0;

是否有被锁 

show open tables where name_locked > 0;

查线程，找到有锁的线程ID,杀掉:

show processlist;
show full processlist;
kill <id>;

查看正在锁的事务:

select * from information_schema.innodb_trx; 
select * from information_schema.innodb_locks;

查看等待锁的事务

select * from information_schema.innodb_lock_waits;

如果有很多线程，找到有锁的线程ID的源头,杀掉:

SELECT CONCAT('kill ',trx_mysql_thread_id) FROM 
information_schema.innodb_trx trx 
INNER JOIN information_schema.innodb_locks 
loc ON trx.trx_id = loc. lock_trx_id
WHERE trx_state = 'RUNNING';