Mysql锁
Mysql锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。传统的计算机资源（CPU、RAM、I/O）的争用，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。锁冲突也是影响数据库并发访问性能的一个而重要因素。
MySIAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，但也支持表级锁；Innodb存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁。
Mysql的三种锁特性大致如下：
1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度最低
2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突概率最低，并发度也高
3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁粒度界于表锁和行锁之间，并发度一般
应用场景
1. 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如web应用
2. 行级锁则更适合于大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
MyISAM锁
MyISAM存储引擎只支持表锁
MyISAM表锁模式（What）
1. 对于MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
2. 对于MyISAM表的写操作，则会阻塞其他用户对同一表的读写操作
3. MyISAM表的读与写操作之间，以及写与写操作之间是串行的
如何加表锁（How）
MyISAM在执行查询语句前，会自动给涉及的所有表加表读锁；在执行更新（Insert、Update、Delete等）前，会自动给涉及的表加写锁，这个过程中不需用户干预
并发插入（Why-并发读写）
一定条件下，MyISAM表也支持查询和插入操作的并发进行（Read、Insert），不允许Update、Delete
MyISAM存储引擎有一个系统变量concurrent_insert，专门用于控制其并发插入的行为：
1. concurrent_insert = 0，不允许并发插入
2. concurrent_insert = 1（默认设置），如果MyISAM表中没有空洞（表的中间没有被删除的行，空缺行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录
3. concurrent_insert = 2，无论表有没有空洞，都允许在尾部并发插入
> 利用并发插入来解决应用中同一表查询和插入的锁争用。同时通过定期在系统空闲时间执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空间
MyISAM锁调度
当一个进程请求MySIAM表的读锁，同时另一个进程也请求同一个表的写锁，Mysql会如何处理？MyISAM会让写锁进程先执行，不仅如此，即使读请求先到锁队列，写请求后到，写锁也会插入到读锁请求之前。这是因为Mysql认为写请求一般比读请求更重要。这也是MySIAM表不太适合大量更新操作和查询操作的原因，因为大量更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
可以通过设置MySIAM来调节行为：
1. 通过指定启动参数low-priority-updates，使MySIAM引擎默认给予读请求优先写请求
2. 通过执行命令set low_priority_updates=1，使该链接发出的更新请求优先度降低
3. 通过指定INSERT、UPDATE、DELETE语句的low_priority属性，降低该语句的优先级
Innodb锁
并发事务带来的问题
更新丢失：
多个事务选择同一行，然后对这一行数据进行更新，由于每个事务不知道彼此的存在，所以存发生更新丢失问题（覆盖）。例如腾讯文档的多人编辑，但是不知道彼此存在
解决办法：程序加锁
脏读：
多个事务读取同一行，其中一个事务对这个事务的变动后为提交，另一个事务读取的这一行数据不是最新数据
解决办法：读前加锁、多版本并发控制
本质：读取数据的一致性，数据被变动
不可重复读
同一个事务读取同一波数据，不同时间内，数据发生变化，即数据可能被修改或删除。例如：where id = 2，这次读取 status=1，第二次读取 status = 0 或数据已被删除
解决办法：读前加锁、多版本并发控制
本质：读的数据被改动
幻读
同一个事务以相同查询条件读取一波数据后，第二次读取发现数据被其他数据插入了满足该查询条件的数据。例如：查询status = 0的数据，这次读取数据只有10条，下次读取数据有11条
本质：读的数据范围内新增了其他数据
更新丢失的解决办法：
解决办法：程序加锁
本质：并发串行化
脏读、不可重复读、幻读
解决办法：读前加锁、多版本并发控制
本质：读取数据的一致性
Innodb的行锁模式及加锁方法
Innodb实现了一下两种类型行锁
共享锁（S）：允许一个事务读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X）：允许获得排他锁的事务更新数据，组织其他事务获得相同数据的共享锁或排他锁
Innodb为了实现行锁和表锁共存，实现了多粒度（表锁行锁）锁机制，Innodb内部还实现了意向锁，这两种锁都是表锁
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在此之前必须获得该表的IS锁
意向排他锁（IX）：事务打算给数据行更新数据加排他锁，事务在此之前必须获得该表的IX锁
Innodb行锁模式兼容性列表
X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容
1. 兼容锁会授权给该事务，冲突则需等待
2. 意向锁是Innodb自动加的，不需用户干预
3. 对于Insert、Update、Delete，mysql会自动给数据集加上排他锁， 对于Select，mysql不会加任何锁
4. mysql8.0对显示加锁发生调整，S锁，where...FOR SHARE、X锁，where...FOR UPDATE[NOWAIT|SKIP]。nowait则表示如果有其他事务正在使用加排他锁，则返回错误，skip表示如果有其他事务正在加排他锁，则跳过，更新其他数据。（往往不符合应用场景）
Innodb行锁实现方式
Inndob行锁是通过给索引上的索引项加锁来实现的，如果没有索引，Inndob将通过隐藏的聚簇索引来记录加锁。
（聚簇索引，如果没有主键，则内部会选择非null列作为聚簇索引列，如果没有，则内部会选择隐藏的索引列）
Innodb行锁分为三种情况
1. Record lock：对索引项加锁
2. Gap lock：对索引项之间的“间隙”。第一条记录前的“间隙”或最后一条记录后的“间隙”加锁
3. Next-key lock：前两种的组合，对记录及前面的间隙加锁
> Innodb这种行锁实现特点意味着：如果不通过索引条件检索数据，那么Innodb将表中的所有记录，实际效果跟表锁一样。实际应用中要注意Innodb行数的这一特性，否则可能导致大量的锁冲突，从而影响并发性能。
1. 在不通过索引条件查询时候，Innodb会索引表中的所有记录
2. 由于Mysql的行锁是针对索引加的锁， 不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同行，不论是使用主键索引、唯一索引或普通索引，Innodb都会使用行锁来对数据加锁
4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由Mysql通过判断不同执行计划的代价来决定的。如果Mysql认为全表扫描效率高，比如小表，它就不会使用索引，会对所有记录加锁
5. Mysql能够进行类型转换，却不会使用索引，从而导致Innodb对所有记录加锁。 where name = 1。name 是varchar类型，1是整型，则不会使用到索引，会对整记录加锁
什么时候使用表锁
1. 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度
2. 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
注意：
1. 使用LOCK TABLES虽然可以给Innodb加表级锁，但是表锁不是Innodb引擎管理的，而是MysqlServer负责的，仅当autocommit=0、innodb_table_locks=1（默认），innodb层才能知道Mysql加的表锁，MysqlServer才能感知Innodb加的行锁，这种情况下Innodb才能自动识别涉及表锁的死锁，否则，Innodb将无法自动检测并处理这种死锁
2. 将autocommit=0，否则Mysql不会给表加锁，事务结束前，不要使用Unlock tables释放表，因为unlock tables会隐含地提交事务，commit或rollback并不能释放lock tables加的表级锁，必须用unlock tables释放表锁，如果遇到异常需要释放，则在异常出现回滚事务rollback，再释放锁，unlock tables；
死锁
MyISAM总是一次获得所需的全部锁，要么全满足，要么等待，因此不会出现死锁。但在Innodb中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了Innodb中发生死锁是可能的。
死锁出现典型场景
两个事务都需要获得对方持有的排他锁才能继续完成事务。 where id =1 for update。一个事务先A表，加锁，再B表在加锁，另个事务先B表加锁，在A表加锁
发生死锁后
Innodb一般都会自动检测到，并使一个事务释放锁并回退，另一个事务获得锁完成事务。但在涉及外部锁或表锁的情况下，Innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数并不是只用来解决死锁的问题的，在并发访问比较高的情况下，如果大量事务因无法立即获得锁需的锁挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库，设置合适的锁等待超时阀值，可以避免这种情况发生。
下面几个实例介绍几种避免死锁的常用方法：
1. 不同程序会并发存取多个表，应尽量约定以相同的顺序访问，这样子可以大大降低产生死锁的机会。事务1：查询A表加锁，加入B表，事务2：插入B表，查询A表加锁。
2. 程序以批量处理数据的时候，事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁。事务1：查询A表 where id = 1，where id = 3，事务2：查询A表 where id = 3 ，where id = 1。
3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁。不应先申请共享锁，更新时在申请排他锁。
4. 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用了排他锁，在么有复合该条件记录的情况下，两个线程都会加锁成功，程序发现记录尚不存在，就试图插入一条记录，如果两个线程都这么做，就会出现死锁，这种情况下，将隔离级别改成READ COMMITED就可避免问题（设置为READ COMMIT是，两个线程都会执行排他锁，判断记录是否凑整，如果没有插入，此时只有一个线程插入成功，另一个线程出现锁等待，当第一个线程插入成功后，第二个线程会出现主键重复错误，这个错误虽然是线程错误，但是却获得一个排他锁，如果这个时候第三个线程去申请排他锁，则会出现死锁，因为第二个线程未解锁，此时需要在程序捕获第二个线程插入的主键冲突的错误，回滚事务并释放锁，这样子第三个线程则可以成功申请排他锁）
1、3的情况是因为，防止事务1在处理程序的过程中，已经在处理第n的程序的时候，才发现正要处理的数据已经加锁，冲突，导致后续的程序无法处理，两个事物都卡顿了，如果按照顺序，这个时候，在刚处理的时候已经发现锁以被占用，则不需要花更多的时间去处理可能需要回滚的程序。 事务1已经处理到了，where id = 3，这个时候事务2刚开始就使用where id =3 加锁，因为锁是行锁索引实现的，所以这个时候就冲突了。出入数据也同理。
关于MySIAM锁
1. 共享锁S之间是兼容的，但共享锁与排他锁是互斥的，读写是串行的
2. 一定条件下，MySIAM允许查询和插入并发执行，可以利用这一点解决应用中对同一表查询和插入的锁争用问题，但不能更新或删除
3. MySIAM默认的锁调度机制是写有限，这并不是适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用
4. 由于表锁的粒度大，读写之间又是串行的，因此，如果更新操作很多，MySIAM可能会出现严重的锁等待，可以考虑用Innodb表来减少锁冲突
关于Innodb锁
1. Innodb的行锁是基于索引实现的，如果不通过索引访问数据，Innodb会对所有数据加锁
2. 介绍Inndob Next-key锁机制，Innodb的锁机制和一致性读策略不同
3. 在不同隔离级别下，Innodb的锁机制和一致性读策略不同
4. 锁冲突甚至死锁很难完全避免
了解Innodb锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁
1. 尽量使用较低的隔离级别
2. 精心设计索引，尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会
3. 选择合理的事务大小，小事务发生锁冲突的概率也越小
4. 给记录集显示加锁时，最好一次性请求足够级别的锁。比如修改数据，最好直接申请排他锁，不要先申请分享锁，再申请排他锁
5. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会
6. 尽量用相同的条件访问数据，这样可以避免Next-key锁对并发插入的影响
7. 不要申请超过实际需要的锁级别，读取申请写锁
8. 对于一些特定的事务，可以使用表锁来提高速度或减少死锁的概率
