MySQL 锁机制（学习）

参考：https://segmentfault.com/a/1190000011164489
https://www.cnblogs.com/y-rong/p/8110596.html
https://www.jianshu.com/p/817fdd7ba8ea （锁分类）

MySQL 两种锁特性归纳 ：
表级锁：开销小，加锁快，不会出现死锁，发生锁冲突概率高，并发程度低。
行级锁：开销大，加锁慢，会出现死锁，发生锁冲突概率低，并发程度高。

MySQL 不同的存储引擎支持不同的锁机制。
myisam 存储引擎采用的是 表级锁； 操作myisam表时 select，update，delete，insert语句都会给表自动加锁
innodb 存储引擎既支持行级锁和事务，也支持表级锁，但默认情况下采用行级锁。

在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。

Innodb 实现了两种类型的行锁：
共享锁（S）：排它锁又叫写锁，如果事务T对A加上排它锁，则其它事务都不能对A加任何类型的锁。获准排它锁的事务既能读数据，又能写数据。
排他锁（X）：共享锁又叫读锁，如果事务T对A加上共享锁，则其它事务只能对A再加共享锁，不能加其它锁。获准共享锁的事务只能读数据，不能写数据。

对于 update、delete 和 insert 语句，innodb 会自动给涉及数据集加排它锁（X）；对于普通 select 语句，innodb 不会加任何锁。
事务可以通过以下语句显式给记录集加共享锁或排它锁。

共享锁（S）：select * from table_name where ... lock in share mode.
排它锁（X）: select * from table_name where ... for update.

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

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

关于死锁：
当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。

myisam 表锁是 deadlock free 的，这是因为 myisam 总是一次获取所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 innodb 中，除单个 sql 组成的事务外，锁是逐步获得的，这就决定了在 innodb 中发生死锁是可能的。

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

如果出现死锁，可以用 show innodb status 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 sql 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。可以据此分析产生死锁的原因。