数据库锁定机制是为了保持数据库在并发条件下数据库一致性，使各种资源在被并发访问变得有序所设计的一种规则。
MySQL数据库锁定机制
整体上，MySQL锁的类型从全局到细节包含：
- 全局锁
- 表级锁：普通表锁、MDL（元数据锁 meta data lock）
- 行级锁：读锁（共享锁）、写锁（排他锁、叉锁）
- 间隙锁：Next-key lock
全局锁
全局锁是对整个数据库实例加锁，MySQL提供了一个加锁语句：FTWRL
mysql> FLUSH TABLES WITH READ LOCK
FTWRL能使整个实例上只读，所有的写和更新都会被阻塞。全局锁典型应用场景是做全局的数据备份时使用。
- 全局备份时 InnoDB 存储引擎完全可通过MVCC创建一致性视图来保证不受备份中其他操作的影响
- 对数据库的全局锁，不仅会阻塞DML增删改查，同样对数据库表的的DDL增删改字段也会阻塞。
- 可通过设置全局只读来进行只读性的设置
全局只读
mysql> SET GLOBAL READONLY = true;
设置全局只读与FTWRL的区别在于有些数据库会把SET GLOBAL READONLY设置成备库的只读限制而不是用来做备份，影响面比较大。在异常处理机制上，FTWRL机制在客户端异常断开时数据库会主动释放全局的锁并恢复正常。而SET GLOBAL READONLY在客户端异常断开时不会恢复原状，数据库会一直处于只读状态，影响很大。
表级锁
每次进行SELECT查询操作或DML的时候，对表添加的都是MDL的读锁。而进行DDL操作时，对表添加的则是MDL的写锁。
表级表锁中的普通锁和元数据锁有何不同呢？
普通表锁
普通表锁也是分为读锁和写锁，数据库提供LOCK TABLE ... READ/WRITE用于加锁，使用UNLOCK TABLES进行释放锁。
添加普通表锁后会对当前加锁线程后续的数据库操作产生影响
例如：线程A对t1表加读锁对t2表加写锁后，其他线程写t1和读写t2会被阻塞。
mysql> LOCK TABLES t1 READ, t2 WRITE;
线程A在进行UNLOCK TABLE解锁前，也只能读t1读写t2，不能写t1，也不能访问其他表。
mysql> UNLOCK TABLES t1 READ, t2 WRITE;
在没有出现行锁之前，都是通过表锁进行并发控制，由于表锁影响面太大限制太严格，不便于控制和使用。
元数据锁 MDL
元数据锁不需要主动加锁，每当访问一个数据表时会自动被加上，作用是防止在进行表操作时进行表的变更。
MySQL5.5版本开始：当对一个表进行增删改查时将自动填加MDL的读锁，当对一个表进行结构变更时将自动添加MDL的写锁。
读写锁的MDL之间的互斥关系是
| 互斥关系 | 读锁 | 写锁 |
|---|---|---|
| 读锁 | 不互斥 | 互斥 |
| 写锁 | 互斥 | 互斥 |
例如：多个人同时操作数据库时，某人给表添加字段时整个表挂了，接下来的操作都将失败或不返回。
| # | SessionA | SessionB | SessionC | SessionD |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | SELECT * FROM t LIMIT 1; | |||
| 3 | SELECT * FROM t LIMIT 1; | |||
| 4 | ALTER TABLE t ADD f INT;block | |||
| 5 | SELECT * FROM t LIMIT 1;block |
SessionC的操作为什么会被阻塞呢？
SessionA开启事务并支持查询，SessionB执行查询，SessionC执行修改表操作，此时SessionA的事务并未结束，MDL会随着事务的开启而加锁，事务的结束而释放锁。因此，SessionA此时保持住了MDL的读锁。SessionC想要获取MDL的写的时候，由于读写互斥，SessionC就会阻塞（block）了。
如何安全的对一个表进行添加字段操作呢？
- 在
information_schema库的innodb_tx表中查找到当前正在执行的事务，若是长事务则可以考虑kill掉。 - 若是频繁访问的短事务比较多，可使用
ALTER TABLE table_name WAIT n ADD colname这种类型的操作，若拿不到MDL写锁，一段时间会释放阻塞，不长期影响数据库。
行锁
两阶段加锁策略
MySQL添加行锁会使用两阶段加锁策略，两阶段加锁协议表示整个事务分为两个阶段，前一个阶段为加锁，后一阶段为解锁。在加锁阶段事务只能加锁、可以操作数据、但不能解锁，直到事务释放第一个锁后就进入解锁阶段。解锁极端只能解锁、可以操作数据、但不能加锁。两极端锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。两阶段解锁策略的缺点是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。比如两个事务分别申请了A和B锁，接着又申请对方的锁，此时进入死锁状态。
什么时候添加行锁呢？
正常SELECT是不会添加行锁的，只会加上MDL的读锁，即使SELET语句是全表扫描也不会添加行锁，只不过全表扫描查询较慢，但并不会因为锁的问题而对其他操作进行阻塞。
例如：对主键为1的记录添加行锁（共享锁）
SELECT * FROM t WHERE id=1 IN SHARE MODEL;
例如：对主键为1的记录添加行锁（排他锁，叉锁）
SELECT * FROM t WHERE id=1 FOR UPDATE;
例如：对主键为1的记录添加行锁（排他锁，叉锁）
UPDATE t SET colname=1 WHERE id=1
例如：对非主键的字段所在记录添加行锁
UPDATE t SET fieldname=1 WHERE colname=1
- 若colname没有索引则添加的是普通表锁
- 若colname是非唯一索引则添加行锁
- 若colname是唯一索引则添加行锁的排他锁
需要注意的是在添加行锁之前，对所有列取得操作时首先会添加上MDL得读锁。
添加行锁后会带来什么影响呢？
行锁存在的意义时为了提高并发度，取代以前整表加锁而引起同一时刻只能有一个线程对数据表进行增删改的操作。
例如：事务开始时（begin）没有任何行锁被持有，只有当前具体操作，依次请求MDL读锁。事务B的UPDATE操作会被阻塞，因为ID为1记录的行锁被事务A锁持有。所有当前事务持有的行锁，在语句执行完都不会释放直到COMMIT提交之后才会释放。
| # | TransactionA | TransactionB |
|---|---|---|
| 1 | BEGIN; | |
| 2 | UPDATE tbl SET col = col + 1 WHERE id = 1; | |
| 3 | UPDATE tbl SET col = col + 2 WHERE id = 2; | |
| 4 | BEGIN; | |
| 5 | UPDATE tbl SET col = col + 3 WHERE id = 1; | |
| 6 | COMMIT; |
越是并发度高的数据表，越要靠事务的后面写，因为持有行锁时间短，影响并发的时间阅读。
死锁
经典的死锁场景
| # | TransactionA | TransationB |
|---|---|---|
| 1 | BEGIN; | - |
| 2 | UPDATE tbl SET col = col + 1 WHERE id = 1; | BEGIN; |
| 3 | - | UPDATE tbl SET col = col + 3 WHERE id = 2; |
| 4 | UPDATE tbl SET col = col + 2 WHERE id = 2; | - |
| 5 | - | UPDATE tbl SET col = col + 3 WHERE id = 1; |
- 事务A获取ID为1这一条记录的行锁（排它锁）
UPDATE tbl SET col = col + 1 WHERE id = 1;
- 事务B获取ID为2这一条记录的行锁
UPDATE tbl SET col = col + 3 WHERE id = 2;
- 事务A获取ID为2这一条记录的行锁时获取不到，发生阻塞。
UPDATE tbl SET col = col + 2 WHERE id = 2;
- 事务B获取ID为1这一条记录的行锁时获取不到，发生阻塞。
UPDATE tbl SET col = col + 3 WHERE id = 1;
对于这种死锁，MySQL有两种机制进行处理：
innodb_lock_wait_timeout
设置锁等待时间，若超过这个时间，则阻塞的进程会释放所有持有的锁并回滚。
innodb_lock_wait_timeout 虽然能控制死锁但时间不好设置，比如设置10秒，若一个线程被锁住则需要等待10秒才能进行回滚，并发度自然不高。若设置低了1秒，那么一个正常等待的并非死锁也会回滚。如此依赖得不偿失。
innodb_deadlock_detect
设置是否主动检测死锁，通过回滚死锁联调中的一个事务来解决死锁。设置 innodb_deadlock_detect 为 on 时，MySQL 会主动检测死锁。当一个线程加入时，即将被等待其他线程的锁而堵住，此时会判断当前线程持有的锁是否会阻塞住其他系统中正在运行的线程。若发生堵塞则回滚当前线程的事务。这种做法的代价是每次对比是否当前线程堵住了其他线程，都会对比系统中正在执行的线程，时间复杂度是O(n)。当前执行的线程数少不成问题。若1000个正在执行的线程，就有存在100w次的对比，这个过程极度消耗CPU资源。结果可能检测出没有死锁，而最终CPU会飚的很高。解决的方案：
- 临时关闭 innodb_deadlock_detect，但这样做会有很多超时，不实用。
- 控制数据库的并发度，可以从中间件这一层控制，或者有能力从数据库这一层进行控制。
- 业务字段拆分，比如将一行拆分为多行，让一行的并发度下降。
间隙锁
间隙锁是用来解决幻读的一大手段，通过这种手段，可以没有必要将事务隔离级别调整到序列化这个最严格的级别，而导致并发量大大下降。
存储引擎
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）。
表级锁 table-level
表级锁是MySQL中力度最大的一种锁定机制，最大特点是实现逻辑简单，系统负面影响最小，获取锁和释放锁的速度很快。由于表级锁会一次性将整个表给锁定，所以可以很好地避免死锁问题。
表级锁锁定粒度大带来最大的负面影响是出现锁定资源争用的概率也会最高，致使并发度大打折扣。使用表级锁锁定的数据库引擎主要是非事务性存储引擎，比如MyISAM、MEMORY、CSV等。
MySQL的表级锁有两种模式分别是表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock），锁模式的兼容性，对MyISAM表的读操作是不会阻塞其它用户对同一表的读请求，但会阻塞对同一表的写请求。对MyISAM表的写操作则会阻塞其它用户对同一表的读写请求。
MySQL表锁兼容性
请求锁模式矩阵结果，表示是否兼容当前锁模式。
| - | None | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
MyISAM写锁模式
通过LOCK TABLE tbl WRITE将表锁住后，其他用户对该表操作时都会被阻塞。
| # | SessionA | SessionB | 描述 |
|---|---|---|---|
| 1 | LOCK TABLE 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; | - |
MyISAM读锁
使用 LOCK TABLES 给表显式添加表锁时，必须同时取得所有涉及到表得锁，MySQL不支持锁升级。在执行 LOCK TABLES 后，只能访问显式加锁的表，不能访问未加锁的表。如果添加的是读锁，只能执行查询操作不能执行更新操作。其实，在自动加锁的情况下也基本如此，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就会暂时将些请求的优先级降低，给读今晨给一定获得锁的机会。
一些需要长时间运行的查询操作也会使写进程饿死，因此应用中应尽量避免出现长时间运行的查询操作。
行级锁 row-level
行级锁的粒度很小，能在并发处理上有较大优势，由于力度小每次获取锁和释放锁所需做的事情也更多，带来的消耗自然也更大，此外行级锁容易发生死锁，使用行级锁主要是InnoDB存储引擎。
InnoDB的行级锁定分为共享锁和排他锁两种类型。在锁定机制实现过程中，为了让行锁和表锁共存，InnoDB同样使用了意向锁（表级锁顶）的概念，也就有了意向共享锁和意向排他锁这两种。
| InnoDB | 读锁 | 写锁 |
|---|---|---|
| 行锁 | 共享锁 | 排他锁 |
| 表锁 | 意向共享锁 | 意向排他锁 |
InnoDB与MyISAM最大不同之处在于支持事务（TRANSACTION）并采用行级锁。
事务（TRANSACTION）是由一组SQL语句组成的逻辑处理单元，事务具有四个属性称为ACID属性：
- 原子性（Atomicity）
事务是一个原子操作单元，对数据的修改要么全部执行要么全都不执行。 - 一致性（Consistent）
事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。事务结束时，所有的内部数据结果，比如B树索引或双向链表，也都是必须是正确的。 - 隔离性（Isolation）
数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。 - 持久性（Durable）
事务完成之后，对于数据的修改是永久性的，即使出现系统故障也能保持。
并发事务带来的问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而支持更多的用户，但并发事务也会带来一些列问题。
- 更新丢失（Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不直到其它事务的存在，就会发生丢失更新的问题。最后更新覆盖了由其他事务所作的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所作的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件则可以避免此问题。 - 脏读（Dirty Reads）
一个事务正在对一条记录作修改，在这个事务完成并提交前，这条记录的数据会处于不一致状态。此时另一个事务也来读取同一条记录，如果不加以控制。第二个事务读取了这些脏数据，并据此做进一步的处理，就胡产生未提交的数据依赖关系。这种现象被形象地称为“脏读”。 - 不可重复读（Non-Repeatable Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据据已发生了改变，或某些记录已经被删除了，这种现象叫做不可重复读。 - 幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足查询条件的新数据，这种现象称为幻读。
事务隔离级别
并发事务带来的问题中，“更新丢失”通常是完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对更新的数据添加必要的锁来解决，因此防止“更新丢失”应该是应用程序的责任。
对于“脏读”、“不可重复”、“幻读”其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库实现事务隔离方式基本可划分未两种， 一种是在读取数据前对其加锁以阻止其他事务对数据进行修改，另一种是不添加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照提供一定级别（语句级或事务级）的一致性读取。从用户角度来看，好像是数据库可以提供同一数据的多个版本。因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control， MVCC, MCC），也称为多版本数据库。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上是使事务在一定程度上“串行化”进行，显然与并发是矛盾的。同时，不同应用对读写一致性和事务隔离程度要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问能力。
为了解决隔离和并发的矛盾，ISO/ANSI SQL92定义了四种事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，选择不同的隔离级别来平衡隔离与并发的矛盾。
读数据一致性及允许并发副作用
| 隔离级别 | 数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读 | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读 | 语句级 | 否 | 是 | 是 |
| 可重复度 | 事务级 | 否 | 否 | 是 |
| 可序列化 | 最高级别，事务级 | 否 | 否 | 否 |
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加上一个共享锁，不过不能添加排他锁。如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。
意向锁的作用是当一个事务在需要获取资源锁定的时候，若遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么在表上添加一个意向共享锁，如果自己需要的是某行或某些行上面添加一个排他锁，则先会在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但意向排他锁同时只能有一个存在。
所以，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方式获得排他锁。
页级锁 page-level
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。页级锁定的特点是锁定粒度介于行级锁定和表级锁定之间，所以获取锁定所需的资源开销，以及所能提供的并发处理能力页同样是介于二者之间的。另外，页级锁定和行级锁定一样会发生死锁。
死锁 deadlock
MyISAM存储引擎为什么不存在死锁呢？
MyISAM表锁是deadlock free的，因为MyISAM总是一次获得所需的全部锁，要么全部满足要么等待，因此不会出现死锁。
为什么InnoDB会存在死锁？
InnoDB中除了单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生死锁之后，InnoDB会通过相应的判断来选择这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
什么时候使用表锁？
对于InnoDB表在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由。但在个别特殊事务中也会考虑使用表锁。
事务需要更新大部分或全部数据时，表数据体积较大，如果使用默认行锁不仅事务执行效率低，可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务的执行速度。
事务涉及多个表，比较复杂，可能引起死锁造成大量事务回滚。此时可以考虑一次性锁定事务涉及的表，从而避免死锁以减少数据库因事务回滚带来的开销。
使用LOCK TABLES加锁
当使用LOCK TABLES对InnoDB表添加锁的时候需要注意的是，要将AUTOCOMMIT设置为0，否则MySQL不会给表加锁。当事务结束前，不要使用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务。COMMIT或ROLLBACK并不能释放使用LOCK TABLES添加的表级锁，必须使用UNLOCK TABLES释放表锁。
-- 关闭事务自动提交
SET AUTOCOMMIT=0;
-- 为表添加行锁：READ读锁 / WRITE写锁
LOCK TABLES table_name1 WRITE, table_name2 READ, ...;
-- 业务操作
-- do something with tables table_name1 and table_name2 here
-- 提交事务
COMMIT;
-- 释放表锁
UNLOCK TABLES;
InnoDB锁优化
- 尽可能让所有数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。
- 合理涉及索引，让InnoDB在索引键上加锁时尽可能准确，尽可能缩小锁定范围，避免造成不必要的锁定而影响其它Query的执行。
- 尽量控制事务的大小以减少锁定的资源量和锁定时间长度
行锁争夺
查看当前行锁的竞争情况
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
mysql> SHOW STATUS LIKE "InnoDB_row_lock%";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 73495 |
| Innodb_row_lock_time_avg | 5 |
| Innodb_row_lock_time_max | 258 |
| Innodb_row_lock_waits | 13549 |
+-------------------------------+-------+
5 rows in set
| 参数 | 含义 |
|---|---|
| Innodb_row_lock_current_waits | 当前正在等待锁的数量 |
| Innodb_row_lock_time | 从系统启动到现在锁定总时长 |
| Innodb_row_lock_time_avg | 每次等待所耗费平均时间 |
| Innodb_row_lock_time_max | 从系统启动到现在等待最长一次所耗费的时长 |
| Innodb_row_lock_waits | 系统启动到现在总共等待的次数 |
如果发现锁争用严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值会比较高，可通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
# 设置InnoDB Monitor
mysql> CREATE TABLE innodb_monitor(a int) ENGINE=INNODB;
# 查看监视数据
mysql> SHOW ENGINE innodb status;
# 停止查看监视器
mysql> DROP TABLE innodb_monitor;
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
