InnoDB锁问题

一、InnoDB 行锁

​ InnoDB 行锁是通过给索引上的索引项加锁来实现的，如果没有索引， InnoDB 将通过隐藏的聚簇索引来对记录加锁。 InnoDB 行锁分为 3 种情况。

1) Record lock ：对索引项加锁
2) Gap lock ：对索引项之间的”间隙“、第一条记录前的”间隙“或最后一条记录后的”间隙“加锁。
3) Next-key lock ：前两种的组合，对记录及其前面的间隙加锁

注意：
	1) 在不通过索引条件的查询时， InnoDB 会锁定表中的所以记录，实际效果跟表锁一样。
	2) MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但如果是使用相同的索引键，是会出现锁冲突。
	3)当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引还是普通索引， InnoDB 都会使用行锁来对数据加锁。
	4)即便在条件中使用了索引字段，但是是否使用索引来检索数据是由 MySQL 通过判断不同的执行计划的代价来决定的。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确定是否真正使用了索引。
			

二、Next-key 锁

​ Next-key 锁，当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时， InnoDB 会给符合条件的已有数据记录的索引加锁；对于在条件范围内但并不存在的记录（叫做间隙（GAP））也会加锁。

​ 例如：actor 表中只用 202 条记录，其中 actor_id 的值分别是 1、2、…、201、202

select * from actor where actor_id > 201 for update;

这是范围条件的检索， InnoDB 不仅会对符合条件的 actor_id 的值的记录加锁，也会对大于 202（不存在的记录）的“间隙”加锁。

​ 提醒：在实际的应用开发中，尤其是并发插入比较多的应用，要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免范围条件。
​ 注意：InnoDB 除了通过范围条件加锁时使用 Next-key 锁；如果使用相等条件请求一个不存在的记录加锁，也会使用 Next-key 锁。

三、CTAS操作

​ MySQL InnoDB会给INSERT…SELECT…和 CREATE TABLE…SELECT（CTAS）语句源表加上共享锁，这可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的新能问题，在应用中尽量避免使用。MySQL将这种 SQL 称为不确定（non-deterministic）的SQL，属于“Unsafe SQL”,不推荐使用。

​

​ 如果应用要使用这种SQL来实现业务逻辑，又不希望对源表并发更新产生影响，可以采取如下 3 种措施。

	1)将 innodb_locks_unsafe_for_binlog 的值设置为 on ，强制 MySQL 使用多版本数据一致性读。但付出的代价是可能无法用 BINLOG	正确地恢复或复制数据，因此，不推荐使用这种方式。
	
	2)通过使用 "select * from source_tab ... into outfile" 和 "load data infile ... "语句组合来间接实现，采用这种方式 MySQL 不会给 source_tab 加锁。
	
	3)使用基于行的 BINLOG 格式和基于行数据的复制。

四、使用表锁的情况

​ InnoDB 表，在大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择的 InnoDB 表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

	1)事务需要更新大部分或全部数据，表有比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务的执行速度。
	
	2)事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况下可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

​ 注意：应用中这两种事务不能太多，否则，就应该考虑使用 MyISAM 表了。在 InnoDB 下，使用表锁要注意一下两点。

	1)使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，但表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层 MySQL Server 负责的，仅当 autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB 层才能知道 MySQL 加的表锁，MySQL Server 也才能感知 InnoDB 加的行锁，这种情况下，InnoDB 才能自动识别涉及表级锁的死锁；否则，InnoDB 将无法自动检测并处理这种死锁。
	
	2)在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设置为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES 会隐式的提交事务; COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁。

五、避免死锁的常用方法

	1)在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
	
	2)在程序已批处理方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，可以大大降低出现死锁的可能。
	
	3)在事务中，如果要更新记录，应该直接申请足够级别的锁，即排它锁，而不应先申请共享锁，更新是再申请排它锁，因为当用户申请排他锁时，其他事务可能有已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
	
	4)在 repetable-read 隔离级别下，如果两个线程同时对相同条件记录用 SELECT...FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 read-committed ,就可以避免问题。

	5)当隔离级别为 read-committed 时，如果两个线程都先执行 SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因主键重复出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第三个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后在捕获主键重复异常，或者在遇到主键重复错误是，总是执行 ROLLBACK 释放获得的排他锁。