MySQL锁机制

一、MySQL锁机制概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是也是影响数据库并发访问性能的一个重要因素。而所有的锁都可以分为悲观锁和乐观锁这两种。

1.1 悲观锁和乐观锁的概念

• 悲观锁
  总是假设最坏的情况，每次去拿数据的时候都认为别人会随时修改数据，所以每次数据处理时需要先将数据加锁。而悲观锁一般都是依靠关系数据库提供的锁机制的。事实上关系数据库中的行锁，表锁不论是读锁还是写锁都是悲观锁。

• 乐观锁
  总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁。但是在更新的时候会去判断此期间数据有没有被修改。乐观锁需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

• 悲观锁，乐观锁的使用场景
  乐观锁适用于写操作比较少的情况（多读场景），即冲突真的很少发生的时候，这样可以省去锁的开销，加大系统的整个吞吐量。
  如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁了，它会增加复杂度，并带来业务额外的风险的。所以一般写操作比较多的场景下适合使用悲观锁。

1.2 锁的分类

按操作分	按粒度分	按算法分
读锁(共享锁)：Shared Locks（S）	表锁：Table Locks	记录锁：Record Locks
写锁(排它锁)：Exclusive Locks（X）	行锁：Row Locks	间隙锁：Gap Locks
意向共享锁：Intention Shared Locks（IS）	页锁：Page Locks	临键锁：Next-key Locks
意向排他锁：Intention Exclusive Locks（IX）
自增锁：（AUTO-INC Locks）

注：上述所有锁，都是悲观锁

1.3 按粒度分类锁的区别

锁类型	开销	加锁	是否会出现死锁	锁定粒度	发生锁冲突的概率	并发度
表级锁	小	快	不会	大	最高	最低
行级锁	大	慢	会	小	最低	最高
页面锁	介于表锁和行锁之间	介于表锁和行锁之间	会	介于表锁和行锁之间		一般

1.4 锁算法

锁算法	中文名称	描述
Record Lock	行锁，也叫记录锁	总是会去锁住索引记录
Gap Lock	间隙锁	当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁
Next-Key Lock	临键锁	当SQL语句按照索引进行数据的检索时，锁定一个范围，并且锁定记录本身；它是InnoDB默认隔离级别的锁算法，其设计的目的是为了解决Phantom Problem(幻读)，临键锁锁住索引的记录+区间（左开右闭）

二、引擎锁机制(共享锁、排他锁)

MySQL中的锁其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

2.1 MyISAM引擎锁

MyISAM引擎默认使用的是表锁。对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的

2.1.1 MySQL的表级锁有两种模式：

• 表共享读锁（读锁）（Table Read Lock）：允许本线程/进程读、不允许本线程写，其他线程可读，不可写
• 表独占写锁（写锁）（Table Write Lock）：允许本线程读、写操作，其他线程不可读、不可写

2.1.2 加锁和解锁

• 查看表是否被上锁了

show open tables;

结果如下：

• 对表加锁

lock table student read;   # 读锁
lock table student write;  # 写锁

结果如下：

• 对表解锁

unlock tables;

结果如下：

2.1.3 MyISAM的并发插入

MyISAM表的读写操作之间是串行的，在一定的条件下，MyISAM表也支持查询和插入操作的并发进行。

• 查询当前concurrent_insert的值：

show variables like 'concurrent_insert';

结果如下：

注：1）concurrent_insert为0时，为NEVER状态，不允许并发插入；
2） concurrent_insert为1时，为AUTO状态，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；
3）concurrent_insert为2时，为ALWAYS状态，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

2.1.4 MyISAM的锁调度

• 问 ：如果一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

调节MyISAM的调度行为：

• 通过指定启动参数low_priority_updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令set low_priority_updates=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

注：主要调度行为是降低读请求或更新语句的优先级

2.2 InnoDB 引擎锁

2.2.1 InnoDB与MyISAM的最大不同有两点

1）InnoDB支持事务、外键；
2）InnoDB虽然也支持表级锁，但它默认采用行级锁。

2.2.2 MySQL的行级锁也有两种模式

• 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获取该行的排它锁。
• 排他锁（x）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务获取排它锁和共享锁。

2.2.3 InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

• 在操作时锁定一行操作：
  在查询之后添加for update,其它操作会被阻塞，直到锁定的行提交commit；

# eg:在事务中获取某条记录的排它锁
select * from t_orders where id=1 for update;

• 查看行锁的使用信息：

show status like 'innodb_row_lock%';

结果如下：

1）Innodb_row_lock_current_waits`：当前有多少线程正在等待行锁；

2）Innodb_row_lock_time`：行锁等待时间；

3）Innodb_row_lock_time_avg`：行锁平均等待时间；

4）Innodb_row_lock_time_max`：行锁最大等待时间；

5）Innodb_row_lock_waits`：总的行锁等待数。

三、意向锁

3.1 意向锁概述

意向锁的存在是为了调节行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。意向锁是有InnoDB在操作数据之前自动加的，不需要用户干预。

• 意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
• 意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

我们知道，在MySQL中行锁和表锁是可以并存的（即使阻塞也是并存），而之所以能并存时因为意向锁的存在

3.2 意向锁的作用

当一个事务在需要获取资源的锁时，如果该资源已经被排他锁占用，则该事件将无法获取到资源的意向锁（就是无法操作资源）。如果自己需要一个共享锁，就申请一个意向共享锁。如果需要的是牟星（或某些行）的排他锁，则申请一个意向排他锁。

3.3 意向锁的功能

解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗

3.4 意向锁是如何让表锁和行锁并存的

事务A在申请表锁（写锁）之前，数据库会自动的给事务A申请的表加上意向排它锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。

3.5 意向锁是表锁还是行锁

答案：意向锁是表级别锁

当我们需要加一个排它锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁），这样就会出现两种情况
（1）如果意向锁是行锁，这需要遍历每一行数据去确认；
（2）如果意向锁是表锁，这只需要判断一次即可知道有没有数据行被锁定，提升性能。

所以依上述两种情况比较，在确保数据库高性能的条件下，意向锁是表级别锁

四、自增锁

MySQL的自增锁是针对自增列增长的一个特殊的表级别锁。

4.1 自增锁的参数设置

MySQL在5.1.22版本之后加入了一个和自增锁相关的参数，为innodb_autoinc_lock_mode；

show variables like 'innodb_autoinc_lock_mode';

当innodb_autoinc_lock_mode的值为：

• 0：traditional （每次都会产生表锁）
• 1：consecutive （会产生一个轻量锁，insert会获得批量的锁，保证连续插入），事务未提交ID永久丢失，默认值
• 2：interleaved （不会锁表，来一个处理一个，并发最高）