本文源码: GitHub. 点这里 https://github.com/cicadasmile/mysql-data-base || GitEE. 点这里 https://github.com/cicadasmile/mysql-data-base
一, 锁概念简介
1, 基础描述
锁机制核心功能是用来协调多个会话中多线程并发访问相同资源时, 资源的占用问题. 锁机制是一个非常大的模块, 贯彻 MySQL 的几大核心难点模块: 索引, 锁机制, 事务. 这里是基于 MySQL5.6 演示的几种典型场景, 对面 MySQL 这几块问题时, 有分析流程和思路是比较关键的. 在 MySQL 中常见这些锁概念: 共享读锁, 排它写锁 ; 表锁, 行锁, 间隙锁.
2, 存储引擎和锁
MyISAM 引擎: 基于读写两种模式, 支持表级锁 ;
InnoDB 引擎: 支持行级别读写锁, 跨行的间隙锁, InnoDB 也支持表锁 ;
3, 锁操作 API
LOCK TABLE name [READ,WRITE] ; 加表锁
UNLOCK TABLES ; 释放标所
二, MyISAM 锁机制
1, 基础描述
MySQL 的表级锁有两种模式: 共享读锁 (Read-Lock) 和排它写锁(Write-Lock). 针对 MyISAM 表的读操作, 不会阻塞其他线程对同一表的读请求, 但阻塞对同一表的写请求; 针对 MyISAM 表的写操作, 会阻塞其他线程对同一表的读和写操作; MyISAM 引擎读写操作之间, 以及写与写操作之间是串行化. 当一次会话线程获取表的写锁后, 只有当前持有锁的会话线程可以对表进行操作. 其它线程的读, 写操作都会等待, 直到锁被释放为止.
2, 验证案例
基于上面的表锁机制特点, 使用下面两个案例验证.
基础表结构
- CREATE TABLE `dc_user` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
- `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
- `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号',
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户表';
- CREATE TABLE `dc_user_info` (
- `user_id` int(11) NOT NULL COMMENT '用户 ID',
- `city` varchar(20) DEFAULT NULL COMMENT '城市',
- `country` varchar(20) DEFAULT NULL COMMENT '国家',
- PRIMARY KEY (`user_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户信息表';
共享读锁
会话窗口一
-- 1, 加读锁
LOCK TABLE dc_user READ ;
-- 2, 当前会话查询, OK
SELECT * FROM dc_user ;
-- 4, 当前会话写入, Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 6, 查询其他表, Error
SELECT * FROM dc_user_info ;
-- 7, 释放锁
UNLOCK TABLES ;
会话窗口二
-- 3, 其他会话查询, OK
SELECT * FROM dc_user ;
-- 5, 其他会话写入, Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 8, 再次执行写入读取, OK
- INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
- SELECT * FROM dc_user ;
这里验证表锁的共享读机制.
排它写锁
这里验证表锁的排它写机制.
查询锁争用
通过下面语句查看配置,
show status like 'table%';
Table_locks_waited 的值越大, 锁争用情况越严重, 效率则越低下.
3, 并发写入问题
针对排它写锁的测试案例再说明: 在一定条件下, MyISAM 表也支持查询和插入操作的并发执行. 通过配置系统变量 concurrent_insert 的值[0,1,2], 可以实现并发写入.
concurrent_insert=0, 禁止并发写入;
concurrent_insert=1, 默认配置 AUTO, 在 MyISAM 表中没有空洞, 即表的中间没有被删除的行, 例如[1,2,3], 删除 2 之后[1,,3], 则允许在读表的同时, 另一个线程从表尾写入记录.
concurrent_insert=2, 无论 MyISAM 表中有没有空洞, 都允许在表尾并发插入记录.
在下面的例子中, session_1 获得了一个表的 READ LOCAL 锁, 该线程可以对表进行查询操作, 但不能对表进行更新操作; 其他的线程(session_2), 虽然不能对表进行删除和更新操作, 但却可以对该表进行并发插入操作, 这里假设该表中间不存在空洞.
4, 优先级问题
MyISAM 存储引擎的读锁和写锁是互斥的, 读写操作是串行的. 但是当一个读操作和写操作同时请求, 写数据会优先获得锁, 这一机制可以通过配置修改, 指定配置参数 low-priority-updates, 使 MyISAM 引擎默认给予读请求以优先的权利.
通过执行命令 SET
LOW_PRIORITY_UPDATES=1, 使该会话的写操作优先级降低.
指定 INSERT,UPDATE,DELETE 语句的 LOW_PRIORITY 属性, 降低该语句的优先级.
5, 表锁应用
数据一致性校验问题, 比如销售量 + 剩余库存 = 货品总量, 在校验时就要在一次会话中同时锁住订单表和库存表, 免得在读取订单表的时候, 库存表被修改, 导致数据误差出现.
三, InnoDB 锁机制
1, 事务基础概念
事务概念
事务是指作为单个逻辑工作单元执行的一系列操作(SQL 语句). 这些操作要么全部成功, 要么全部不成功.
事务特性 ACID
原子性(Atomicity): 事务中的多个操作要么都成功要么都失败
一致性(consistency): 事务的执行的前后数据的完整性保持一致
隔离性(isolation): 事务执行的过程中, 不应该受到其他事务的干扰
持久性(durability): 事务一旦结束, 数据就持久到数据库
事务问题
脏读: 一个事务读到另一个事务没有提交的数据
不可重复读: 一个事务前后多次读取相同数据, 数据内容不一致, update 场景问题
虚读(幻读): 一个事务前后多次读取, 数据总量不一致, insert 场景问题
隔离级别
read uncommitted: 事务可以读取另一个未提交事务的数据.
read committed: 事务要等另一个事务提交后才能读取数据, 解决脏读.
repeatable read: 在开始读取数据时, 事务开启, 不再允许修改操作, 解决: 脏读, 不可重复读.
serializable: 最高事务隔离级别, 事务串行化顺序执行, 解决脏读, 不可重复读, 幻读. 但是效率低下, 耗数据库性能.
2, 锁机制描述
InnoDB 与 MyISAM 的最大不同有两点: 一是支持事务 TRANSACTION, 二是采用了行级锁. 行级锁与表级锁本来就有许多不同之处, 另外, 事务的引入也带来新问题: 并发, 死锁等.
共享锁: 又称读锁. 允许一个事务去读一行, 阻止其他事务获得相同数据集的排他锁. 若事务 T 对数据对象 A 加上共享锁, 则事务 T 可以读 A 但不能修改 A, 其他事务只能再对 A 加共享锁, 而不能加写锁, 直到 T 释放 A 上的共享锁. 这保证了其他事务可以读 A, 但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改.
排他锁: 又称写锁. 允许获取排他锁的事务更新数据, 阻止其他事务取得相同的资源的共享读锁和排他锁. 若事务 T 对数据对象 A 加上写锁, 事务 T 可以读 A 也可以修改 A, 其他事务不能再对 A 加任何锁, 直到 T 释放 A 上的写锁.
3, 验证案例
基础表结构
- CREATE TABLE `dc_user_in01` (
- `id` int(11) DEFAULT NULL COMMENT 'id',
- `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
- `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
- CREATE TABLE `dc_user_in02` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
- `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
- `tell_phone` varchar(20) DEFAULT NULL COMMENT '手机号',
- PRIMARY KEY (`id`)
- ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户表';
注意结构: 表 dc_user_in01 主键没有索引. 表 dc_user_in02 主键有索引, 但是都使用 INNODB 存储引擎, 下面验证案例会有不同.
无索引结构表
会话窗口一
-- 1, 关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2, 查询 id=1,OK
SELECT * FROM dc_user_in01 WHERE id=1 ;
-- 3, 添加写锁失败
- SELECT * FROM dc_user_in01
- WHERE id=1 FOR UPDATE ;
-- 4, 恢复事务提交
SET AUTOCOMMIT = 1 ;
会话窗口二
-- 1, 关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2, 查询 id=2,OK
SELECT * FROM dc_user_in01 WHERE id=2 ;
-- 3, 写入失败(等待)
- INSERT INTO dc_user_in01 (id,user_name,tell_phone)
- VALUES (3,'lock01','13267788998');
-- 4, 写锁失败(等待)
- SELECT * FROM dc_user_in01
- WHERE id=2 FOR UPDATE ;
-- 5, 恢复事务提交
SET AUTOCOMMIT=1 ;
索引结构表
会话窗口一
-- 1, 关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2, 查询 id=1,OK
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 3, 添加写锁成功
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 执行到这里, 再执行窗口 2
-- 4, 恢复事务提交
SET AUTOCOMMIT = 1 ;
会话窗口二
-- 1, 关闭自动提交
SET AUTOCOMMIT = 0 ;
-- 2, 查询 id=2,OK
SELECT * FROM dc_user_in02 WHERE id=2 ;
-- 3, 查询 id=1,OK, 加读锁
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 4, 写入成功
INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 5, 加写锁成功, id 为 2 的
- SELECT * FROM dc_user_in02
- WHERE id=2 FOR UPDATE ;
-- 6, 加写锁失败(等待), 占用 id 为 1 的
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 7, 恢复事务提交
SET AUTOCOMMIT=1 ;
索引失效问题
这里要注意索引是否被使用问题, 在很多查询中, 可能因为种种原因导致索引不执行.
explain SELECT * FROM dc_user_in02 WHERE id=1 ;
查询锁争用
show status like 'innodb_row_lock%';
Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值越大, 锁争用情况越严重, 效率则越低下.
4,Next-Key 锁
官方文档说明
为了防止幻读, InnoDB 使用了一种名为 Next-Key 锁定的算法, 它将记录锁和间隙锁定结合在一起即: InnoDB 在执行行级锁的时候, 会用这种方式 - 扫描索引记录, 会在符合索引条件的记录上加共享锁或者独占锁.
[Next-Key]=[Record-lock]+[Gap-lock]
如果说上面的几种锁机制给人的感觉是昏天暗地, 那个这个 Next-Key 算法就会叫人怀疑人生.
验证案例
这里主要验证 Gap-lock 间隙锁的存在机制.
- CREATE TABLE `dc_gap` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
- `id_index` int(11) NOT NULL COMMENT 'index',
- PRIMARY KEY (`id`),
- KEY `id_index` (`id_index`)
- ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='间隙表';
- INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('1', '2');
- INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('3', '4');
- INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('6', '7');
- INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('8', '7');
- INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('9', '9');
会话窗口一
-- 1, 开始事务
START TRANSACTION ;
-- 3, 锁定 id_index=7 的两条记录
- SELECT * FROM dc_gap
- WHERE id_index=7 FOR UPDATE ;
-- 9, 提交
COMMIT ;
会话窗口二
-- 2, 开始事务
START TRANSACTION ;
-- 4, 写入等待, id_index=6
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '6');
-- 5, 写入等待, id_index=4
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '4');
-- 6, 写入成功, id_index=3
- INSERT INTO `dc_gap` (`id`, `id_index`)
- VALUES ('4', '3');
-- 7, 写入等待, id_index=9
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '9');
-- 8, 写入成功, id_index=10
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '10');
7 向上到 4 有间隙, 7 向下到 9 有间隙, 所以间隙锁定[4,9], 且包含首尾值.
5,Dead-Lock 锁
基础描述
两个或者多个事务在同一个资源上相互占用, 并请求锁定对方占用的资源, 从而导致死循环现象, 也就是死锁.
验证案例
会话窗口一
-- 1, 开启事务
START TRANSACTION ;
-- 3, 占用 id=6 的资源
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;
-- 5, 占用 id=9 的资源等待
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;
会话窗口二
-- 2, 开启事务
START TRANSACTION ;
-- 4, 占用 id=9 的资源
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;
-- 6, 占用 id=6 的资源抛死锁
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;
补刀一句: 数据库实现各种死锁检测机制, 或者死锁超时等待结束, InnoDB 存储引擎在检测到死锁后, 会立即返回错误, 不然两个事务会隔空对望, 一眼万年.
注意: 死锁在事务型业务中, 是无法绝对避免的, 锁定资源少, 粒度细, 尽量避免该情况出现.
四, 源代码地址
GitHub. 地址
https://github.com/cicadasmile/mysql-data-base
GitEE. 地址
https://gitee.com/cicadasmile/mysql-data-base
来源: https://www.cnblogs.com/cicada-smile/p/12520893.html