基本介绍
锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全有序所设计的一种规则
利用 MVCC 性质进行读取的操作叫一致性读，读取数据前加锁的操作叫锁定读
锁的分类：
- 按操作分类：
- 共享锁：也叫读锁。对同一份数据，多个事务读操作可以同时加锁而不互相影响 ，但不能修改数据
- 排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入
- 按粒度分类：
- 表级锁：会锁定整个表，开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低，偏向 MyISAM
- 行级锁：会锁定当前操作行，开销大，加锁慢；会出现死锁；锁定力度小，发生锁冲突概率低，并发度高，偏向 InnoDB
- 页级锁：锁的力度、发生冲突的概率和加锁开销介于表锁和行锁之间，会出现死锁，并发性能一般
- 按使用方式分类：
- 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁
- 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
不同存储引擎支持的锁
存储引擎 表级锁 行级锁 页级锁 MyISAM 支持 不支持 不支持 InnoDB 支持 支持 不支持 MEMORY 支持 不支持 不支持 BDB 支持 不支持 支持
从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理系统
内存结构
对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，结构包括
- 事务信息：锁对应的事务信息，一个锁属于一个事务
- 索引信息：对于行级锁，需要记录加锁的记录属于哪个索引
- 表锁和行锁信息：表锁记录着锁定的表，行锁记录了 Space ID 所在表空间、Page Number 所在的页号、n_bits 使用了多少比特
- type_mode：一个 32 比特的数，被分成 lock_mode、lock_type、rec_lock_type 三个部分
- lock_mode：锁模式，记录是共享锁、排他锁、意向锁之类
- lock_type：代表表级锁还是行级锁
- rec_lock_type：代表行锁的具体类型和 is_waiting 属性，is_waiting = true 时表示当前事务尚未获取到锁，处于等待状态。事务获取锁后的锁结构是 is_waiting 为 false，释放锁时会检查是否与当前记录关联的锁结构，如果有就唤醒对应事务的线程
一个事务可能操作多条记录，为了节省内存，满足下面条件的锁使用同一个锁结构：
- 在同一个事务中的加锁操作
- 被加锁的记录在同一个页面中
- 加锁的类型是一样的
- 加锁的状态是一样的
Server
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)
MDL 叫元数据锁，主要用来保护 MySQL 内部对象的元数据，保证数据读写的正确性，当对一个表做增删改查的时候，加 MDL 读锁；当要对表做结构变更操作 DDL 的时候，加 MDL 写锁，两种锁不相互兼容，所以可以保证 DDL、DML、DQL 操作的安全
说明：DDL 操作执行前会隐式提交当前会话的事务，因为 DDL 一般会在若干个特殊事务中完成，开启特殊事务前需要提交到其他事务
MDL 锁的特性：
MDL 锁不需要显式使用，在访问一个表的时候会被自动加上，在事务开始时申请，整个事务提交后释放（执行完单条语句不释放）
MDL 锁是在 Server 中实现，不是 InnoDB 存储引擎层能直接实现的锁
MDL 锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁
FLUSH TABLES WITH READ LOCK 简称（FTWRL），全局读锁，让整个库处于只读状态，DDL DML 都被阻塞，工作流程：
- 上全局读锁（lock_global_read_lock）
- 清理表缓存（close_cached_tables）
- 上全局 COMMIT 锁（make_global_read_lock_block_commit）
该命令主要用于备份工具做一致性备份，由于 FTWRL 需要持有两把全局的 MDL 锁，并且还要关闭所有表对象，因此杀伤性很大
MyISAM
表级锁
MyISAM 存储引擎只支持表锁，这也是 MySQL 开始几个版本中唯一支持的锁类型
MyISAM 引擎在执行查询语句之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁，这个过程并不需要用户干预，所以用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁
加锁命令：（对 InnoDB 存储引擎也适用）
读锁：所有连接只能读取数据，不能修改
写锁：其他连接不能查询和修改数据
sql-- 读锁 LOCK TABLE table_name READ; -- 写锁 LOCK TABLE table_name WRITE;解锁命令：
sql-- 将当前会话所有的表进行解锁 UNLOCK TABLES;
锁的兼容性：
- 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
- 对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作
锁调度：MyISAM 的读写锁调度是写优先，因为写锁后其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞，所以 MyISAM 不适合做写为主的表的存储引擎
锁操作
读锁
两个客户端操作 Client 1和 Client 2，简化为 C1、C2
数据准备：
sqlCREATE TABLE `tb_book` ( `id` INT(11) AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, `publish_time` DATE DEFAULT NULL, `status` CHAR(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8 ; INSERT INTO tb_book (id, NAME, publish_time, STATUS) VALUES(NULL,'java编程思想','2088-08-01','1'); INSERT INTO tb_book (id, NAME, publish_time, STATUS) VALUES(NULL,'mysql编程思想','2088-08-08','0');C1、C2 加读锁，同时查询可以正常查询出数据
sqlLOCK TABLE tb_book READ; -- C1、C2 SELECT * FROM tb_book; -- C1、C2
C1 加读锁，C1、C2 查询未锁定的表，C1 报错，C2 正常查询
sqlLOCK TABLE tb_book READ; -- C1 SELECT * FROM tb_user; -- C1、C2
C1、C2 执行插入操作，C1 报错，C2 等待获取
sqlINSERT INTO tb_book VALUES(NULL,'Spring高级','2088-01-01','1'); -- C1、C2
当在 C1 中释放锁指令 UNLOCK TABLES，C2 中的 INSERT 语句立即执行
写锁
两个客户端操作 Client 1和 Client 2，简化为 C1、C2
C1 加写锁，C1、C2查询表，C1 正常查询，C2 需要等待
sqlLOCK TABLE tb_book WRITE; -- C1 SELECT * FROM tb_book; -- C1、C2
当在 C1 中释放锁指令 UNLOCK TABLES，C2 中的 SELECT 语句立即执行
C1、C2 同时加写锁
sqlLOCK TABLE tb_book WRITE;
C1 加写锁，C1、C2查询未锁定的表，C1 报错，C2 正常查询
锁状态
查看锁竞争：
sqlSHOW OPEN TABLES;
In_user：表当前被查询使用的次数，如果该数为零，则表是打开的，但是当前没有被使用
Name_locked：表名称是否被锁定，名称锁定用于取消表或对表进行重命名等操作
sqlLOCK TABLE tb_book READ; -- 执行命令
查看锁状态：
sqlSHOW STATUS LIKE 'Table_locks%';
Table_locks_immediate：指的是能立即获得表级锁的次数，每立即获取锁，值加 1
Table_locks_waited：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加 1，此值高说明存在着较为严重的表级锁争用情况
InnoDB
行级锁
记录锁
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁，InnoDB 同时支持表锁和行锁
行级锁，也称为记录锁（Record Lock），InnoDB 实现了以下两种类型的行锁：
- 共享锁 (S)：又称为读锁，简称 S 锁，多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
- 排他锁 (X)：又称为写锁，简称 X 锁，不能与其他锁并存，获取排他锁的事务是可以对数据读取和修改
RR 隔离界别下，对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（行锁），在 commit 时自动释放；对于普通 SELECT 语句，不会加任何锁（只是针对 InnoDB 层来说的，因为在 Server 层会加 MDL 读锁），通过 MVCC 防止并发冲突
在事务中加的锁，并不是不需要了就释放，而是在事务中止或提交时自动释放，这个就是两阶段锁协议。所以一般将更新共享资源（并发高）的 SQL 放到事务的最后执行，可以让其他线程尽量的减少等待时间
锁的兼容性：
- 共享锁和共享锁 兼容
- 共享锁和排他锁 冲突
- 排他锁和排他锁 冲突
- 排他锁和共享锁 冲突
显式给数据集加共享锁或排他锁：加锁读就是当前读，读取的是最新数据
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享锁
SELECT * FROM table_name WHERE ... FOR UPDATE -- 排他锁注意：锁默认会锁聚簇索引（锁就是加在索引上），但是当使用覆盖索引时，加共享锁只锁二级索引，不锁聚簇索引
锁操作
两个客户端操作 Client 1和 Client 2，简化为 C1、C2
环境准备
sqlCREATE TABLE test_innodb_lock( id INT(11), name VARCHAR(16), sex VARCHAR(1) )ENGINE = INNODB DEFAULT CHARSET=utf8; INSERT INTO test_innodb_lock VALUES(1,'100','1'); -- .......... CREATE INDEX idx_test_innodb_lock_id ON test_innodb_lock(id); CREATE INDEX idx_test_innodb_lock_name ON test_innodb_lock(name);关闭自动提交功能：
sqlSET AUTOCOMMIT=0; -- C1、C2正常查询数据：
sqlSELECT * FROM test_innodb_lock; -- C1、C2查询 id 为 3 的数据，正常查询：
sqlSELECT * FROM test_innodb_lock WHERE id=3; -- C1、C2
C1 更新 id 为 3 的数据，但不提交：
sqlUPDATE test_innodb_lock SET name='300' WHERE id=3; -- C1
C2 查询不到 C1 修改的数据，因为隔离界别为 REPEATABLE READ，C1 提交事务，C2 查询：
sqlCOMMIT; -- C1
提交后仍然查询不到 C1 修改的数据，因为隔离级别可以防止脏读、不可重复读，所以 C2 需要提交才可以查询到其他事务对数据的修改：
sqlCOMMIT; -- C2 SELECT * FROM test_innodb_lock WHERE id=3; -- C2
C1 更新 id 为 3 的数据，但不提交，C2 也更新 id 为 3 的数据：
sqlUPDATE test_innodb_lock SET name='3' WHERE id=3; -- C1 UPDATE test_innodb_lock SET name='30' WHERE id=3; -- C2
当 C1 提交，C2 直接解除阻塞，直接更新
操作不同行的数据：
sqlUPDATE test_innodb_lock SET name='10' WHERE id=1; -- C1 UPDATE test_innodb_lock SET name='30' WHERE id=3; -- C2
由于 C1、C2 操作的不同行，获取不同的行锁，所以都可以正常获取行锁
锁分类
间隙锁
InnoDB 会对间隙（GAP）进行加锁，就是间隙锁 （RR 隔离级别下才有该锁）。间隙锁之间不存在冲突关系，多个事务可以同时对一个间隙加锁，但是间隙锁会阻止往这个间隙中插入一个记录的操作
InnoDB 加锁的基本单位是 next-key lock，该锁是行锁和 gap lock 的组合（X or S 锁），但是加锁过程是分为间隙锁和行锁两段执行
- 可以保护当前记录和前面的间隙，遵循左开右闭原则，单纯的间隙锁是左开右开
- 假设有 10、11、13，那么可能的间隙锁包括：(负无穷,10]、(10,11]、(11,13]、(13,正无穷)
几种索引的加锁情况：
- 唯一索引加锁在值存在时是行锁，next-key lock 会退化为行锁，值不存在会变成间隙锁
- 普通索引加锁会继续向右遍历到不满足条件的值为止，next-key lock 退化为间隙锁
- 范围查询无论是否是唯一索引，都需要访问到不满足条件的第一个值为止
- 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么会加间隙锁
间隙锁优点：RR 级别下间隙锁可以解决事务的一部分的幻读问题，通过对间隙加锁，可以防止读取过程中数据条目发生变化。一部分的意思是不会对全部间隙加锁，只能加锁一部分的间隙
间隙锁危害：
- 当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害，影响并发度
- 事务 A B 同时锁住一个间隙后，A 往当前间隙插入数据时会被 B 的间隙锁阻塞，B 也执行插入间隙数据的操作时就会产生死锁
现场演示：
关闭自动提交功能：
sqlSET AUTOCOMMIT=0; -- C1、C2查询数据表：
sqlSELECT * FROM test_innodb_lock;
C1 根据 id 范围更新数据，C2 插入数据：
sqlUPDATE test_innodb_lock SET name='8888' WHERE id < 4; -- C1 INSERT INTO test_innodb_lock VALUES(2,'200','2'); -- C2
出现间隙锁，C2 被阻塞，等待 C1 提交事务后才能更新
意向锁
InnoDB 为了支持多粒度的加锁，允许行锁和表锁同时存在，支持在不同粒度上的加锁操作，InnoDB 增加了意向锁（Intention Lock）
意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，意向锁分为两种：
- 意向共享锁（IS）：事务有意向对表加共享锁
- 意向排他锁（IX）：事务有意向对表加排他锁
IX，IS 是表级锁，不会和行级的 X，S 锁发生冲突，意向锁是在加表级锁之前添加，为了在加表级锁时可以快速判断表中是否有记录被上锁，比如向一个表添加表级 X 锁的时：
- 没有意向锁，则需要遍历整个表判断是否有锁定的记录
- 有了意向锁，首先判断是否存在意向锁，然后判断该意向锁与即将添加的表级锁是否兼容即可，因为意向锁的存在代表有表级锁的存在或者即将有表级锁的存在
兼容性如下所示：
插入意向锁 Insert Intention Lock 是在插入一行记录操作之前设置的一种间隙锁，是行级锁
插入意向锁释放了一种插入信号，即多个事务在相同的索引间隙插入时如果不是插入相同的间隙位置就不需要互相等待。假设某列有索引，只要两个事务插入位置不同，如事务 A 插入 3，事务 B 插入 4，那么就可以同时插入
自增锁
系统会自动给 AUTO_INCREMENT 修饰的列进行递增赋值，实现方式：
- AUTO_INC 锁：表级锁，执行插入语句时会自动添加，在该语句执行完成后释放，并不是事务结束
- 轻量级锁：为插入语句生成 AUTO_INCREMENT 修饰的列时获取该锁，生成以后释放掉，不需要等到插入语句执行完后释放
系统变量 innodb_autoinc_lock_mode 控制采取哪种方式：
- 0：全部采用 AUTO_INC 锁
- 1：全部采用轻量级锁
- 2：混合使用，在插入记录的数量确定时采用轻量级锁，不确定时采用 AUTO_INC 锁
隐式锁
一般情况下 INSERT 语句是不需要在内存中生成锁结构的，会进行隐式的加锁，保护的是插入后的安全
注意：如果插入的间隙被其他事务加了间隙锁，此次插入会被阻塞，并在该间隙插入一个插入意向锁
- 聚簇索引：索引记录有 trx_id 隐藏列，表示最后改动该记录的事务 id，插入数据后事务 id 就是当前事务。其他事务想获取该记录的锁时会判断当前记录的事务 id 是否是活跃的，如果不是就可以正常加锁；如果是就创建一个 X 的锁结构，该锁的 is_waiting 是 false，为自己的事务创建一个锁结构，is_waiting 是 true（类似 Java 中的锁升级）
- 二级索引：获取数据页 Page Header 中的 PAGE_MAX_TRX_ID 属性，代表修改当前页面的最大的事务 ID，如果小于当前活跃的最小事务 id，就证明插入该数据的事务已经提交，否则就需要获取到主键值进行回表操作
隐式锁起到了延迟生成锁的效果，如果其他事务与隐式锁没有冲突，就可以避免锁结构的生成，节省了内存资源
INSERT 在两种情况下会生成锁结构：
重复键：在插入主键或唯一二级索引时遇到重复的键值会报错，在报错前需要对对应的聚簇索引进行加锁
- 隔离级别 <= Read Uncommitted，加 S 型 Record Lock
- 隔离级别 >= Repeatable Read，加 S 型 next_key 锁
外键检查：如果待插入的记录在父表中可以找到，会对父表的记录加 S 型 Record Lock。如果待插入的记录在父表中找不到
- 隔离级别 <= Read Committed，不加锁
- 隔离级别 >= Repeatable Read，加间隙锁
锁优化
优化锁
InnoDB 存储引擎实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高，但是在整体并发处理能力方面要远远优于 MyISAM 的表锁，当系统并发量较高的时候，InnoDB 的整体性能远远好于 MyISAM
但是使用不当可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至可能会更差
优化建议：
- 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少索引条件及索引范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可使用低级别事务隔离（需要业务层面满足需求）
锁升级
索引失效造成行锁升级为表锁，不通过索引检索数据，全局扫描的过程中 InnoDB 会将对表中的所有记录加锁，实际效果和表锁一样，实际开发过程应避免出现索引失效的状况
查看当前表的索引：
sqlSHOW INDEX FROM test_innodb_lock;关闭自动提交功能：
sqlSET AUTOCOMMIT=0; -- C1、C2执行更新语句：
sqlUPDATE test_innodb_lock SET sex='2' WHERE name=10; -- C1 UPDATE test_innodb_lock SET sex='2' WHERE id=3; -- C2
索引失效：执行更新时 name 字段为 varchar 类型，造成索引失效，最终行锁变为表锁
死锁
不同事务由于互相持有对方需要的锁而导致事务都无法继续执行的情况称为死锁
死锁情况：线程 A 修改了 id = 1 的数据，请求修改 id = 2 的数据，线程 B 修改了 id = 2 的数据，请求修改 id = 1 的数据，产生死锁
解决策略：
直接进入等待直到超时，超时时间可以通过参数 innodb_lock_wait_timeout 来设置，默认 50 秒，但是时间的设置不好控制，超时可能不是因为死锁，而是因为事务处理比较慢，所以一般不采取该方式
主动死锁检测，发现死锁后主动回滚死锁链条中较小的一个事务，让其他事务得以继续执行，将参数
innodb_deadlock_detect设置为 on，表示开启该功能（事务较小的意思就是事务执行过程中插入、删除、更新的记录条数）死锁检测并不是每个语句都要检测，只有在加锁访问的行上已经有锁时，当前事务被阻塞了才会检测，也是从当前事务开始进行检测
通过执行 SHOW ENGINE INNODB STATUS 可以查看最近发生的一次死循环，全局系统变量 innodb_print_all_deadlocks 设置为 on，就可以将每个死锁信息都记录在 MySQL 错误日志中
死锁一般是行级锁，当表锁发生死锁时，会在事务中访问其他表时直接报错，破坏了持有并等待的死锁条件
锁状态
查看锁信息
SHOW STATUS LIKE 'innodb_row_lock%';
参数说明：
Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时长
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不短的时候，就需要分析系统中为什么会有如此多的等待，然后根据分析结果制定优化计划
查看锁状态：
SELECT * FROM information_schema.innodb_locks; #锁的概况
SHOW ENGINE INNODB STATUS\G; #InnoDB整体状态，其中包括锁的情况
lock_id 是锁 id；lock_trx_id 为事务 id；lock_mode 为 X 代表排它锁（写锁）；lock_type 为 RECORD 代表锁为行锁（记录锁）
乐观锁
悲观锁：在整个数据处理过程中，将数据处于锁定状态，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据，修改删除数据时也加锁，其它事务同样无法读取这些数据
悲观锁和乐观锁使用前提：
- 对于读的操作远多于写的操作的时候，一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量，最后需要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
- 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险，这时候可以选择悲观锁
乐观锁的实现方式：就是 CAS，比较并交换
版本号
给数据表中添加一个 version 列，每次更新后都将这个列的值加 1
读取数据时，将版本号读取出来，在执行更新的时候，比较版本号
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化
用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新
sql-- 创建city表 CREATE TABLE city( id INT PRIMARY KEY AUTO_INCREMENT, -- 城市id NAME VARCHAR(20), -- 城市名称 VERSION INT -- 版本号 ); -- 添加数据 INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1); -- 修改北京为北京市 -- 1.查询北京的version SELECT VERSION FROM city WHERE NAME='北京'; -- 2.修改北京为北京市，版本号+1。并对比版本号 UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;
时间戳
- 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是 timestamp
- 每次更新后都将最新时间插入到此列
- 读取数据时，将时间读取出来，在执行更新的时候，比较时间
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化
乐观锁的异常情况：如果 version 被其他事务抢先更新，则在当前事务中更新失败，trx_id 没有变成当前事务的 ID，当前事务再次查询还是旧值，就会出现值没变但是更新不了的现象（anomaly）
解决方案：每次 CAS 更新不管成功失败，就结束当前事务；如果失败则重新起一个事务进行查询更新
Q.E.D.