<深入浅出MySQL>读书笔记之锁

锁类型

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁

查看表级锁的争夺情况

mysql> show global status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 201   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.01 sec)

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

表级锁的模式

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的!.

如何加表锁

MySIAM 在select前, 会自动给所涉及的表加上读锁, 在执行更新操作时(update, delete, insert)前,会自动给涉及的表加写锁.

注意 : 当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！

在LOCK TABLES时加了local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录.

MyISAM 的锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为.

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低.

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会.

InnoDB锁(行锁)

并发事务带来的问题

更新丢失（Lost Update）
脏读（Dirty Reads）
不可重复读（Non-Repeatable Reads）
幻读（Phantom Reads）

事务隔离级别

数据库实现事务隔离的方式, 基本上可以分为两种方式:

第一种: 在读取数据前, 对其加锁, 阻止其他事务对数据进行修改.
第二种: 不用加任何锁, 通过一定机制生成一个数据请求时间点的一致性数据快照.并用这个快照来提供一定级别(语句级或事务级)的一致性读取.从用户的角度来看, 好像数据库可以提供同一数据的多个版本, 因此, 这种技术叫做数据多版本并发控制(MVCC).

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行.

InnoDB行锁竞争情况

mysql> show global status like '%innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过查询 information_schema 数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

查看innodb锁信息: select * from information_schema.innodb_locks\G
通过innodb monitors查看:

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB

然后使用以下命令来查看:

show engine innodb status\G

停止监视器:drop table innodb_monitor;

设置监视器后，在SHOW INNODB STATUS的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“–console”选项来启动服务器以关闭写日志文件.

InnoDB 的行锁及加锁方法

行锁类型

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于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。

用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁. 对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB实现行锁的方式

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

行锁分三种情况:

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

注意 :

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样!
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有记录加锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引.

Next-Key锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如emp表中只有101条记录，其empid的值分别是1、2、…、100、101，下面的SQL：

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁.

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要.

注意

很显然，在使用范围条件检索并锁定记录时，InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件.
还要特别说明的是，InnoDB除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！.

恢复和复制对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制.

MySQL 5.6支持 3种日志格式，即基于语句的日志格式SBL、基于行的日志格式RBL和混合格式.

基于SQL语句的复制SBR.
基于行数据的复制RBR.
混合复制模式
使用全局事务ID（GTIDs）的复制：主要是解决主从自动同步一致问题

对于insert into target_tab select * from source_tab where ... 和 create table new_tab...select ...from source_tab where ..(CTAS)这种SQL, 用户并没有对source_tab做任何更新操作, 但MySQL对这种SQL语句做了特别处理. 它会对source_tab加表锁.

可以通过设置innodb_locks_unsafe_for_binlog为on,这样就不会对source_tab加表锁了.注意如果设置为了on, 就有可能导致进行复制时, 主从不一致. (详细看<深入浅出MySQL>第二版, 20.3.7节)

注意 : 如果上述的where是范围查询的话, 还会加上Next-key锁!

因此，INSERT…SELECT…和CREATE TABLE…SELECT…语句，可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的性能问题，读者在应用中应尽量避免使用。实际上，MySQL将这种 SQL叫做不确定（non-deterministic）的 SQL，属于“Unsafe SQL”，不推荐使用.

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

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

什么时候使用表锁

对InnoDB表, 绝大部分都应该使用行级锁. 但在个别特殊事务中，也可以考虑使用表级锁。

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

使用表锁要注意:

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

关于死锁

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生.

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免.

避免死锁的常用方法

在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能.
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题.
当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁. 因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯.
InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会对所有数据加锁。
给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
尽量用相等条件访问数据，这样可以避免Next-Key锁对并发插入的影响；
不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
对于一些特定的事务，可以使用表锁来提高处理速度或减少发生死锁的几率。

Contents

锁类型

表级锁