MySQL 第16天 锁问题

锁问题

锁是计算机协调多个进程或线程并发访问某一资源的机制

MySQL 锁概述

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)

BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;

InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁

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

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web 应用;

行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统

MyISAM表锁

1  查询标记锁的争用情况

show status like 'table%';

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

2  MySQL 表级锁的锁模式

MySQL 的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock

当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、
写操作都会等待,直到锁被释放为止。

3  MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作
(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干
预

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

需要对别名分别锁定

例如

mysql> lock table actor as a read,actor as b read;

4  并发插入

在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值
分别可以为0、1或2。
 当concurrent_insert设置为0时,不允许并发插入。
 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的
行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL
的默认设置。
当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入
记录。

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。
例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲
时段执行OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。

5   MyISAM 的锁调度

MyISAM 存储引擎的读锁和写锁是互斥的,读写操作是串行的。

写进程先获得锁  因为 MySQL 认为写请求一般比读请求要重要

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，

因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。

可以通过一些设置来调节MyISAM的调度问题

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

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

这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!

因此,应用中应尽量避免出现长时间运行的查

询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较
复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分
解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽
量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

InnoDB 锁问题

InnoDB 与 MyISAM 的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

背景知识

1  事务及其属性

 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,
要么全都不执行。
 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着
所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,
所有的内部数据结构(如 B 树索引或双向链表)也都必须是正确的。
 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操
作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见
的,反之亦然。
 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统
故障也能够保持。

并发事务处理带来的问题

 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的
值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题
--最后的更新覆盖了由其他事务所做的更新

 脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交
前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,
如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就

会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。

 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,
再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已
经被删除了!这种现象就叫做“不可重复读”。
 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,
却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”

事务隔离级别

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

 一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
 另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快
照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。
从用户的角度来看,好象是数据库可以提供同一数据的多个版本,因此,这种技
术叫做数据多版本并发控制(MultiVersion Concurrency Control,简称 MVCC 或
MCC),也经常称为多版本数据库。

各具体数据库并不一定完全实现了上述 4 个隔离级别

获取 InnoDB 行锁争用情况

show status like 'innodb_row_lock%';

或者详细的使用方法，创建监视器

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

mysql> Show innodb status\G;

通过下列语句停止查看，记得删除

DROP TABLE innodb_monitor;

InnoDB 的行锁模式 加锁方法

两种类型的行锁

 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享
读锁和排他写锁

内部使用的意向锁

 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前
必须先取得该表的 IS 锁。
 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前

必须先取得该表的 IX 锁。

如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务;反之,

如果两者不兼容,该事务就要等待锁释放

事务可以通过以下语句显示给记录集加共享锁或排他锁。

 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的

InnoDB 这种行锁实现特点意味着:只有通过
索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!

(1)在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。

(2)由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行
的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。

(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论
是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。

(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同
执行计划的代价来决定的,如果 MySQL 认为全表扫描效率更高,比如对一些很小的表,它
就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。

间隙锁

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

目的

InnoDB 使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求

另外一方面,是为了满足其恢复和复制的需要

因此,在实际应用开发中,尤其是并发插入
比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范
围条件。

注意：如果使用相等条件请
求给一个不存在的记录加锁,InnoDB 也会使用间隙锁!

恢复和复制的需要,对 InnoDB 锁机制的影响

MySQL 的恢复

机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点

 一是 MySQL 的恢复是 SQL 语句级的,也就是重新执行 BINLOG 中的 SQL 语句。这与
Oracle 数据库不同,Oracle 是基于数据库文件块的。

 二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的。
这点也与 Oralce 不同,Oracle 是按照系统更新号(System Change Number,SCN)来恢
复数据的,每个事务开始时,Oracle 都会分配一个全局唯一的 SCN,SCN 的顺序与事务

开始的时间顺序是一致的。

从上面两点可知,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不
能插入满足其锁定条件的任何记录,也就是不允许出现幻读,这已经超过了 ISO/ANSI
SQL92“可重复读”隔离级别的要求,实际上是要求事务要串行化。这也是许多情况下,
InnoDB 要用到间隙锁的原因,比如在用范围条件更新记录时,无论在 Read Commited 或
是 Repeatable Read 隔离级别下,InnoDB 都要使用间隙锁,但这并不是隔离级别要求的

MySQL 在处理“Insert into target_tab select *
from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”
时要给 source_tab 加锁,而不是使用对并发影响最小的多版本数据来实现一致性读。还要特
别说明的是,
如果上述语句的 SELECT 是范围条件, InnoDB 还会给源表加间隙锁(Next-Lock)。

如果应用中一定要用这种 SQL 来实现业务逻辑,又不希望对源表的并发更新产生影响,
可以采取以下两种措施:
 一是采取上面示例中的做法,将 innodb_locks_unsafe_for_binlog 的值设置为“on”,
强制 MySQL 使用多版本数据一致性读。但付出的代价是可能无法用 binlog 正确地恢复或复
制数据,因此,不推荐使用这种方式。
 二是通过使用“select * from source_tab ... Into outfile”和“load data infile ...”语句组
合来间接实现,采用这种方式 MySQL 不会给 source_tab 加锁。

InnoDB 在不同隔离级别下的一致性读及锁的差异

从表 20-16 可以看出:对于许多 SQL,隔离级别越高,InnoDB 给记录集加的锁就越严格

产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大

实际上,通过优化事务逻辑,大部分应用使用 Read Commited 隔离级别就足够了。对于一些
确实需要更高隔离级别的事务,可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION
LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变

隔离级别的方式满足需求。

什么时候使用表锁

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

 第一种情况是:事务需要更新大部分或全部数据,表又比较大

 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚

在 InnoDB 下,使用表锁要注意以下两点

(1)使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁,但必须说明的是,表锁不是由 InnoDB
存储引擎层管理的,而是由其上一层──MySQL Server 负责的仅当 autocommit=0、
innodb_table_locks=1(默认设置)时,InnoDB 层才能知道 MySQL 加的表锁,MySQL Server
也才能感知 InnoDB 加的行锁,这种情况下, InnoDB 才能自动识别涉及表级锁的死锁

(2)在用 LOCK TABLES 对 InnoDB 表加锁时要注意,要将 AUTOCOMMIT 设为 0,否则
MySQL 不会给表加锁。事务结束前,不要用 UNLOCK TABLES 释放表锁,因为 UNLOCK TABLES
会隐含地提交事务;COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁,必须用
UNLOCK TABLES 释放表锁

正确语法

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

死锁

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

个事务都需要获得对方持有的排他锁才能继续完成事务。循环锁等待就是典型的死锁

死锁一般能自动检测到，并释放

但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁

需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。在并发访问比较高的情况下,如果大量事务因无法立
即获得所需的锁而挂起，也可以使用这个方法

避免死锁的方法

(1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问

表,这样可以大大降低产生死锁的机会

(2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定
的顺序来处理记录,也可以大大降低出现死锁的可能。

(3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请
共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同
记录的共享锁,从而造成锁冲突,甚至死锁

(4)前面讲过,在 REPEATABLE-READ 隔离级别下,如果两个线程同时对相同条件记录
用 SELECT...FOR UPDATE 加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。
程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。

这种情况下,将隔离级别改成 READ COMMITTED

(5)当隔离级别为 READ COMMITTED 时,如果两个线程都先执行 SELECT...FOR UPDATE,判
断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另
一个线程会出现锁等待,当第 1 个线程提交后,第 2 个线程会因主键重出错,但虽然这个线
程出错了,却会获得一个排他锁!这时如果有第 3 个线程又来申请排他锁,也会出现死锁。
对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误

时,总是执行 ROLLBACK 释放获得的排他锁

在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯

mysql> show innodb status \G   获取最后一个死锁产生的原因

小结

对于 MyISAM 的表锁,主要讨论了以下几点:

(1)共享读锁(S)之间是兼容的,但共享读锁(S)与排他写锁(X)之间,以及排他
写锁(X)之间是互斥的,也就是说读和写是串行的。
(2)在一定条件下,MyISAM 允许查询和插入并发执行,我们可以利用这一点来解决
应用中对同一表查询和插入的锁争用问题。
(3)MyISAM 默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过
设 置 LOW_PRIORITY_UPDATES 参 数 , 或 在 INSERT 、 UPDATE 、 DELETE 语 句 中 指 定
LOW_PRIORITY 选项来调节读写锁的争用。
(4)由于表锁的锁定粒度大,读写之间又是串行的,因此,如果更新操作较多, MyISAM
表可能会出现严重的锁等待,可以考虑采用 InnoDB 表来减少锁冲突。

对于 InnoDB 表,本章主要讨论了以下几项内容。
 InnoDB 的行锁是基于锁引实现的,如果不通过索引访问数据, InnoDB 会使用表锁。
 介绍了 InnoDB 间隙锁(Next-key)机制,以及 InnoDB 使用间隙锁的原因。
 在不同的隔离级别下,InnoDB 的锁机制和一致性读策略不同。
 MySQL 的恢复和复制对 InnoDB 锁机制和一致性读策略也有较大影响。
 锁冲突甚至死锁很难完全避免。
在了解 InnoDB 锁特性后,用户可以通过设计和 SQL 调整等措施减少锁冲突和死锁,包
括:
 尽量使用较低的隔离级别;
 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
 选择合理的事务大小,小事务发生锁冲突的几率也更小;
 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好
直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;
 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽
可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;
 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;
 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。