数据库锁定机制是为了保持数据库在并发条件下数据库一致性，使各种资源在被并发访问变得有序所设计的一种规则。
MySQL数据库锁定机制从整体上，按锁的类型从全局到细节可划分为：
- 全局锁
- 表级锁：普通表锁、MDL（元数据锁 meta data lock）
- 行级锁：读锁（共享锁）、写锁（排他锁、叉锁）
- 间隙锁：Next-key lock
MySQL数据库由于自身架构的特点存在多种数据库存储引擎，每种存储引擎所针对的应用场景特点都不一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。
| 存储引擎 | 锁机制 |
|---|---|
| MyISAM | 表级锁（Table-Level Locking） |
| MEMORY | 表级锁（Table-Level Locking） |
| BDB | 默认页面锁（Page-Level Locking）、支持表级锁（Table-Level Locking） |
| InnoDB | 默认行级锁（Row-Level Locking）、支持表级锁（Table-Level Locking） |
各种锁机制的优缺对比
| 锁机制 | 存储引擎 | 事务 | 粒度 | 读锁 | 开销 | 加锁 | 并发 | 锁冲突 |
|---|---|---|---|---|---|---|---|---|
| 表级锁 | MyISAM | 不支持 | 大 | 无 | 小 | 快 | 低 | 高 |
| 行级锁 | InnoDB | 支持 | 小 | 有 | 大 | 慢 | 高 | 低 |
| 页级锁 | - | - | 适中 | 有 | 适中 | 适中 | 适中 | 适中 |
- 表级锁：更加适合以查询为主，只有少量按索引条件更新数据的应用，如Web应用程序。
- 行级锁：更加适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统（OLTP）。
表级锁
表级锁是MySQL中力度最大的一种锁定机制，最大特点是实现逻辑简单，系统负面影响最小，获取锁和释放锁的速度很快。由于表级锁会一次性将整个表给锁定，所以可以很好地避免死锁问题。
表级锁锁定粒度大带来最大的负面影响是出现锁定资源争用的概率也会最高，致使并发度大打折扣。使用表级锁锁定的数据库引擎主要是非事务性存储引擎，比如MyISAM、MEMORY、CSV等。
表级锁两种模式
| 模式 | 描述 |
|---|---|
| S锁 | 读锁、共享锁（Share Lock） |
| X锁 | 写锁、独占锁（Exclusive Lock） |
锁模式的兼容性
- 对MyISAM表的读操作是不会阻塞其它用户对同一表的读请求，但会阻塞对同一表的写请求。
- 对MyISAM表的写操作则会阻塞其它用户对同一表的读写请求
请求锁模式矩阵结果，表示是否兼容当前锁模式。
| 表锁兼容性 | None | S锁 | X锁 |
|---|---|---|---|
| S锁 | Y | Y | N |
| X锁 | Y | N | N |
X锁模式
通过LOCK TABLES tbl WRITE将表锁住后，其他用户对该表操作时都会被阻塞。
| # | SessionA | SessionB | 描述 |
|---|---|---|---|
| 1 | LOCK TABLES tbl WRITE; | 会话A获取表的写锁定 | |
| - | SELECT * FROM tbl; | 会话A读取数据 | |
| - | INSERT INTO tbl(...) VALUES(...); | 会话A写入数据 | |
| 2 | SELECT * FROM tbl\G; | 会话B被阻塞一直卡住没有返回结果 | |
| 3 | UNLOCK TABLES; | 会话B等待 | |
| 4 | SELECT * FROM tbl\G; | - |
S锁模式
使用 LOCK TABLES 显式添加表锁时，必须同时取得所有涉及到表得锁，MySQL不支持锁升级。在执行 LOCK TABLES 后，只能访问显式加锁的表，不能访问未加锁的表。如果添加的是S锁，只能执行查询操作不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是会一次性获得SQL语句锁需要的全部锁，这也正是MyISAM表不会出现死锁的原因。
| - | SessionA | SessionB | 描述 |
|---|---|---|---|
| 1 | LOCK TABLE user READ; | 会话A获取user表的读锁定 | |
| 2 | SELECT * FROM user WHERE id=1\G; | 会话B未被阻塞 | |
| 3 | SELECT * FROM order; | SELECT * FROM order; | 会话A未获取order表的读锁定不能执行查询，会话B未阻塞可访问order表。 |
| 4 | UPDATE user SET name="jc" WHERE id=1; | UPDATE user SET name="jun" WHERE id=1; | 会话A获得读锁定时不能执行写操作，其他会话执行更新时被阻塞。 |
| 5 | UNLOCK TABLES; | 会话A释放锁，会话B等待。 | |
| 6 | UPDATE user SET name="junchow" WHERE id=1; | - |
MyISAM在执行查询语句SELECT前会自动给涉及的所有表添加读锁，即允许多个线程同时读取数据，但禁止对其进行更新、修改、删除操作。在执行更新操作如UPDATE、DELETE、INSERT等之前，会自动给涉及的表添加写锁，即只允许获得锁的线程进行增删改查操作，这个过程无需用户干扰。因此，用户一般无需直接使用LOCK TABLE命令给MyISAM表显式加锁。
对于MyISAM存储引擎，虽然使用表级锁定在锁定实现过程中比实现行级锁定 或页级锁定带来的附加成本都要小，锁定本身所消耗的资源也最少。但由于锁定颗粒度大所以造成锁定资源的争用情况也会比其他锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化MyISAM存储引擎锁定问题的时候，最关键的问题是如何让其提高并发度。由于锁定级别是不可能改变的，所以需要尽可能让锁定的时间变短，让可能并发进行的操作尽可能的并发。
表级锁定争用
查询表级锁定争用情况，MySQL内部提供两组专门状态变量记录系统内部锁资源争用情况。
mysql> SHOW STATUS LIKE "table_locks%";
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 4001322 |
| Table_locks_waited | 0 |
+-----------------------+---------+
2 rows in set
| 状态变量 | 描述 |
|---|---|
| Table_locks_immediate | 产生表级锁定的次数 |
| Table_locks_waited | 出现表级锁定争用而发生等待的次数 |
这两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。若Table_locks_waited比较高说明系统中表级锁定争用现象严重，需要进一步分析原因。
缩短锁定时间唯一的方法是让Query执行时间尽可能的短
- 尽量量少大且复杂的Query，将复杂的Query分拆为小的Query分步执行。
- 尽可能建立高效的索引，让数据检索更加迅速。
- 尽量让MyISAM存储引擎表只存放必要的信息以控制字段类型
- 利用合适的机会优化MyISAM表数据文件
MyISAM 并发插入 - 分离能并行的操作
MyISAM表锁是读写互相阻塞的，在MyISAM存储引擎的表上并非完全是串行化的，也可以进行并行操作。MyISAM存储引擎提供了ConcurrentInsert并发插入的特性。
MyISAM存储引擎有一个控制是否打开ConcurrentInsert功能的参数选项concurrent_insert可设置为0、1、2.
| 值 | 含义 |
|---|---|
| 0 | 禁止并发插入 |
| 1 | 若MyISAM表中无空洞（表中间没有被删除的行）则允许在一个进程读表的同时。另一个进程从表尾插入记录。 |
| 2 | 无论MyISAM表有无空洞，都允许在表尾并发插入记录。 |
MyISAM锁调度
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的，一般认为写锁的优先级比读锁高，所以即使读请求先到锁等待队列，写请求后到写锁也会插到读锁请求之前。这也正是MyISAM不适合有大量更新操作和查询操作的原因。因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
可以通过设置来调节MyISAM的调度行为
- 通过指定启动参数
low-priority-updates使MyISAM引擎默认给与读请求以优先的权利 - 通过执行命令
SET LOW_PRIORITY_UPDATES=1使该连接发出的更新请求优先级降低 - 通过指定
INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性降低该语句的优先级
以上三种方法都使要么更新优先要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中读锁等待严重的问题。
MySQL提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值以后，MySQL就会暂时将些请求的优先级降低，给读今晨给一定获得锁的机会。
一些需要长时间运行的查询操作也会使写进程饿死，因此应用中应尽量避免出现长时间运行的查询操作。
行级锁
行级锁的粒度很小，能在并发处理上有较大优势，由于力度小每次获取锁和释放锁所需做的事情也更多，带来的消耗自然也更大，此外行级锁容易发生死锁，使用行级锁主要是InnoDB存储引擎。
InnoDB的行级锁定分为共享锁和排他锁两种类型。在锁定机制实现过程中，为了让行锁和表锁共存，InnoDB同样使用了意向锁（表级锁顶）的概念，也就有了意向共享锁和意向排他锁这两种。
| InnoDB | 读锁 | 写锁 |
|---|---|---|
| 行锁 | 共享锁 | 排他锁 |
| 表锁 | 意向共享锁 | 意向排他锁 |
InnoDB与MyISAM最大不同之处在于支持事务（TRANSACTION）并采用行级锁。
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加上一个共享锁，不过不能添加排他锁。如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。
意向锁的作用是当一个事务在需要获取资源锁定的时候，若遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么在表上添加一个意向共享锁，如果自己需要的是某行或某些行上面添加一个排他锁，则先会在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但意向排他锁同时只能有一个存在。
所以，InnoDB的锁定模式实际上分四种：共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）
| InnoDB | 共享锁 | 排他锁 | 意向共享锁 | 意向排他锁 |
|---|---|---|---|---|
| 共享锁 | 兼容 | 冲突 | 兼容 | 冲突 |
| 排他锁 | 冲突 | 冲突 | 冲突 | 冲突 |
| 意向共享锁 | 兼容 | 冲突 | 兼容 | 兼容 |
| 意向排他锁 | 冲突 | 冲突 | 兼容 | 兼容 |
意向锁是InnoDB自动添加的，无需用户干预。对于UPDATE、DELETE、INSERT操作InnoDB会自动给涉及的数据集添加排他锁（X），对于普通SELECT查询读操作InnoDB不会添加任何锁。
事务可以通过以下语句显式给记录集添加的共享锁和排他锁
-- 共享锁(S)
SELECT * FROM table_name WHERE 1=1 LOCK IN SHARE MODE
使用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或DELETE操作。
-- 排他锁（X）
SELECT * FROM table_name WHERE 1=1 FOR UPDATE
如果当前事务需要对该记录进行更新操作，则很可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT ... FOR UPDATE方式获得排他锁。
页级锁
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。页级锁定的特点是锁定粒度介于行级锁定和表级锁定之间，所以获取锁定所需的资源开销，以及所能提供的并发处理能力页同样是介于二者之间的。另外，页级锁定和行级锁定一样会发生死锁。
-- 测试数据
DROP TABLE IF EXISTS accounts;
CREATE TABLE IF NOT EXISTS accounts(
id INT(11) unsigned NOT NULL AUTO_INCREMENT,
account VARCHAR(32) NOT NULL DEFAULT "" COMMENT "账号",
balance INT(11) NOT NULL DEFAULT 0 COMMENT "余额",
PRIMARY KEY(id),
KEY(account)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '账户';
INSERT INTO accounts(account, balance) VALUES (
FLOOR(RAND()*(1000000-100000)+100000),
FLOOR(RAND()*(10000-1000)+1000)
);
