MySQL 当中的各种锁（中级篇）

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

共享锁与排他锁

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁） ：其他事务不能读取，也不能写。

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：
1.MyISAM 存储引擎采用的是表级锁（table-level locking）
2.InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁
3.默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

MyISAM表级锁模式：
表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。
这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）
可以设置改变读锁和写锁的优先级：
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MyISAM加锁方法：
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作
（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

查看表级锁争用情况：
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

Table_locks_immediate：能够立即获得表级锁的锁请求次数
Table_locks_waited：不能立即获取表级锁而需要等待的锁请求次数
如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，
因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些！

MySQL的InnoDB锁机制
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，innodb正常的select ID from table where id=1；不会上任何锁，接下来详细讨论InnoDB的锁问题;

一：InnoDB行锁的介绍
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁,也就是我读取的行，你不能修改，但是大家都可以读这一行，其他人也可以读表里面的其他数据或者操作其他的行数据；
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也就是我更新的行，不允许其他的事务读取和更新相同的行；
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；

关于innodb锁机制，实现原理：
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 索引分为主键索引和二级索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了二级索引，MySQL会先锁定该二级索引，再锁定相关的主键索引InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 索引分为主键索引和二级索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了二级索引，MySQL会先锁定该二级索引，再锁定相关的主键索引

关于innodb锁机制需要注意的是：
1）InnoDB行锁是通过给索引项加锁实现的，如果没有索引，InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。
2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。说白了就是，where id=1 for update 会锁定所有id=1的数据行，如果是where id=1 and name=’liuwenhe’ for update,这样会把所有 id=1以及所有name=’liuwenhe’的行都上排它锁；
3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL优化器通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，或者饮食转换，或者like百分号在前等等，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL优化器通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，或者饮食转换，或者like百分号在前等等，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前锁等待的数量
Innodb_row_lock_time：自系统启动到现在，锁定的总时间，单位：毫秒 ms。
Innodb_row_lock_time_avg：平均锁定的时间，单位：毫秒 ms。
Innodb_row_lock_time_max：最大锁定时间，单位：毫秒 ms。
Innodb_row_lock_waits：自系统启动到现在，锁等待次数，即锁定的总次数。
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

本作品采用《CC 协议》，转载必须注明作者和本文链接