面试系列-mysql锁机制及死锁排查
多事务更新同一行数据时加独占锁避免脏写
如果有事务在表里执行增删改操作，那在行级会加独占锁，此时其实同时会在表级加一个意向独占锁；如果有事务在表里执行查询操作，那么会在表级加一个意向共享锁。其实平时操作数据库，比较常见的两种表锁，反而是更新和查询操作加的意向独占锁和意向共享锁，但是可以忽略这个意向独占锁和意向共享锁，因为两种意向锁根本不会互斥；
锁的类型
表锁
读锁（read lock）
也叫共享锁（shared lock） 针对同一份数据，多个读操作可以同时进行而不会互相影响（select）;
写锁（write lock）
也叫排他锁（exclusive lock） 当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）;
默认存储引擎：MyISAM
特点
1. 读锁会阻塞写操作，不会阻塞读操作；2. 写锁会阻塞读和写操作；
1. 对整张表加锁；2. 开销小；3. 加锁快；4. 无死锁；5. 锁粒度大，发生锁冲突概率大，并发性低；
MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁以后，其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞；
上锁
隐式上锁（默认，自动加锁自动释放）：
select //上读锁
insert、update、delete //上写锁显式上锁（手动）：
lock table tableName read;//读锁
lock table tableName write;//写锁解锁
unlock tables;//所有锁表查看表锁情况
show open tables;
表锁分析：
show status like 'table%';
1. table_locks_waited
出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），
此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1
行锁
读锁（read lock）
也叫共享锁（shared lock） 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
写锁（write lock）
也叫排他锁（exclusive lock） 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁；
为什么上了写锁，别的事务还可以读操作？因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。
意向共享锁（IS）
一个事务给一个数据行加共享锁时，必须先获得表的IS锁；
意向排它锁（IX）
一个事务给一个数据行加排他锁时，必须先获得该表的IX锁；
默认存储引擎：InnoDB
特点
1. 对一行数据加锁；2. 开销大；3. 加锁慢；4. 会出现死锁；5. 锁粒度小，发生锁冲突概率最低，并发性高；
上锁
隐式上锁（默认，自动加锁自动释放）：
select //不会上锁
insert、update、delete //上写锁显式上锁：
select * from tableName lock in share mode;//读锁
select * from tableName for update;//写锁解锁（手动）
1. 提交事务（commit） 2. 回滚事务（rollback） 3. kill 阻塞进程查看行锁情况
show status like 'innodb_row_lock%';
1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数
页锁
开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般；
行锁的实现算法
Record Lock 锁
单个行记录上的锁 Record Lock总是会去锁住索引记录，
如果InnoDB存储引擎表建立的时候没有设置任何一个索引，
这时InnoDB存储引擎会使用隐式的主键来进行锁定；Gap Lock（间隙） 锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的
已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。
优点：解决了事务并发的幻读问题
不足：因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，
即使这个键值并不存在。
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，
而造成锁定的时候无法插入锁定键值范围内任何数据。
在某些场景下这可能会对性能造成很大的危害。Next-key Lock
同时锁住数据+间隙锁
在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。InnoDB对行的查询默认采用Next-key算法。然而，当查询条件为等值时，且索引有唯一属性时（就是只锁定一条记录），InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是一个范围，因为此时不会产生重复读问题；
行锁特点
1. 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)；
2. 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突；
3. 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行；
死锁
指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象；
1. 查看死锁：show engine innodb status \G，只能看到最近一次的死锁信息；
2. 自动检测机制，超时自动回滚代价较小的事务（innodb_lock_wait_timeout 默认50s）;
3. 人为解决，kill阻塞进程（show processlist）;
4. wait for graph 等待图（主动检测）;
5. 访问INNODB_LOCKS查看当前存在的锁情况：SELECT * FROM.INNODB_LOCKS\G;
1. row
lock_id: 16219:56:4:5
lock_trx_id: 16219
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
2. row
lock_id: 16218:56:4:5
: 16218
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
2 rows in set, 1 warning (0.00 sec)- 通过表INNODB_LOCK_WAITS，可以很直观的反应当前事务的等待
mysql> SELECT * FROM.INNODB_LOCK_WAITS\G;
*****************************1.row************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE
blocking_lock_id: 730FEE:96:3:2- information_schema 库
1. innodb_lock_waits表;
2. innodb_locks表;
3. innodb_trx表;如何避免死锁
1. 加锁顺序一致，尽可能一次性锁定所需的数据行
2. 尽量基于primary（主键）或unique key更新数据
3. 单次操作数据量不宜过多，涉及表尽量少
4. 减少表上索引，减少锁定资源
5. 尽量使用较低的隔离级别
6. 尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
7. 精心设计索引，尽量使用索引访问数据
8. 借助相关工具：pt-deadlock-logger