MySQL

锁问题

在数据库中，除了传统的计算资源（CPU，RAM，I/O）的争用外，数据也是一种许多用户共享的资源。如何保证数据并发一致性和有效性是数据库必须解决的问题。

MySQL锁概述

MySQL的锁机制因不同引擎而异：

MyISAM和MEMORY存储引擎：采用表级锁。
InnoDB存储引擎：采用行级锁（默认），也支持表级锁。

MySQL的三种锁特性如下：

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

MyISAM表锁

查询表级锁争用情况

1 2	-- 查看锁争夺情况 SHOW STATUS LIKE 'Table%';

MyISAM存储引擎写阻塞的例子

-- 线程1操作
LOCK TABLE film_text WRITE;
-- 查询操作
SELECT file_id, title FROM film_text WHERE film_id = 1001;

-- 线程2操作
SELECT file_id, title FROM film_text WHERE film_id = 1001;
-- 等待

UNLOCK TABLES;

-- 线程2操作
SELECT file_id, title FROM film_text WHERE file_id = 1001;

在用LOCK TABLES显式加锁时，必须同时取得所有涉及的表的锁。MyISAM不支持锁升级，执行LOCK TABLES时，只能访问显式加锁的表，不能访问未加锁的表。如果加的是读锁，只能执行查询操作，不能执行更新操作，这也是MyISAM不会出现死锁的原因。

-- 线程1操作
LOCK TABLE film_text READ;
-- 查询操作
SELECT * FROM film_text WHERE file_id = 1001;
-- 错误：表file未加锁
SELECT * FROM file WHERE file_id = 1001;

MyISAM的锁调度

当一个进程请求读锁，另一个线程请求写锁时，MySQL会优先给予写进程锁。如果读请求先到，写请求后到，写锁也会插入到读锁之前。这是因为MySQL认为写请求比读请求重要。

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

InnoDB的锁问题

背景知识

事务及ACID属性

事务是由一组SQL语句组成的逻辑处理单元，具有以下4个属性（ACID）：
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改要么全都执行，要么全都不执行。
- 一致性（Consistency）：在事务开始和完成时，数据都必须保持一致状态。
- 隔离性（Isolation）：事务在不受外部并发操作影响的“独立”环境执行。
- 持久性（Durability）：事务完成后，其对数据的修改是永久性的。
并发事务带来的问题
- 更新丢失（Lost Update）：多个事务基于最初选定的值更新同一行时，最后的更新覆盖了其他事务的更新。
- 脏读（Dirty Reads）：一个事务读取了另一个未提交事务的修改数据。
- 不可重复读（Non-Repeatable Reads）：一个事务再次读取以前读过的数据时，发现数据已被修改或删除。
- 幻读（Phantom Reads）：一个事务重新读取以前检索过的数据时，发现其他事务插入了新数据。
事务隔离级别
- 读取未提交（Read Uncommitted）：最低级别，允许脏读。
- 读取已提交（Read Committed）：允许不可重复读。
- 可重复读（Repeatable Read）：防止不可重复读，但可能出现幻读。
- 可串行化（Serializable）：最高级别，防止幻读。

获取InnoDB的行锁争用情况

1	SHOW STATUS LIKE 'innodb_row_lock%';

InnoDB的行锁模式及加锁方式

InnoDB实现了以下两种类型的行锁：

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

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks）：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。

-- 共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 排他锁
SELECT * FROM table_name WHERE ... FOR UPDATE;

间隙锁

当用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在这个条件范围内但不存在的记录（间隙），InnoDB也会加锁，这种锁机制称为间隙锁。

1	SELECT * FROM emp WHERE empid > 100 FOR UPDATE;

MySQL什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，但在以下情况下可以考虑使用表级锁：

事务需要更新大部分或全部数据，表较大时。
事务涉及多个表，可能引起死锁时。

关于死锁

在InnoDB中，锁是逐步获得的，因此可能发生死锁。以下是避免死锁的方法：

约定以相同的顺序访问表。
批量处理数据时，按固定顺序处理记录。
更新记录时，直接申请排他锁。
在RR隔离级别下，避免两个线程同时对相同条件记录用SELECT FOR UPDATE加锁。
在READ COMMITTED隔离级别下，直接做插入操作，捕获主键重异常。

1	SHOW INNODB STATUS;

总结

共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的。
MyISAM允许查询和插入并发执行，可以解决查询和插入的锁争用问题。
MyISAM默认的锁调度机制是写优先，可以通过设置LOW_PRIORITY_UPDATES参数或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
表锁的锁定粒度大，读写之间是串行的，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

减少锁冲突的操作：