MySQL 下的持有锁分析
原创
1、MySQL 下的持有锁分析
主要是讲在事务环境下，MySQL 是如何对数据加锁保证事务的 ACID 的。
1.1、数据准备
CREATE TABLE `callback` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`account_id` int(0) NOT NULL COMMENT 'app账号ID',
`title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '反馈标题',
`content` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '反馈内容',
`img` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图片',
`deal_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '处理状态(0：未处理 1：已处理)',
`create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '意见反馈表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of callback
-- ----------------------------
INSERT INTO `callback` VALUES (1, 4, '1', '1', '', 0, '2021-07-26 10:32:36');
INSERT INTO `callback` VALUES (2, 23, '5', '5', '', 0, '2021-07-26 10:32:45');
INSERT INTO `callback` VALUES (3, 55, '5', '5', '', 0, '2021-07-26 10:49:59');
INSERT INTO `callback` VALUES (4, 8, '7', '5', '', 0, '2021-07-26 10:51:12');
INSERT INTO `callback` VALUES (5, 55, '哈哈哈', '5', '', 0, '2021-07-26 10:53:30');MySQL 默认的隔离级别下：
开启事务，数据隔离从第一个 select 语句开始。
在本地分别开启两个 MySQL 命令行窗口：
\===A窗口===
start TRANSACTION; select * from callback where id = 1\===B窗口===
update callback set title = '巴拉巴拉' where id = 1\===A窗口===
select * from callback where id = 1rollbackA 窗口开启事务，B 窗口以非事务的形式运行，修改一条记录，id 为 1 的数据在运行完毕后 X（排他锁）就已经释放了，A 窗口再次查询发现还是原来的数据。这里保证了事务之间的隔离性，如果未保证的话就会有高并发引起的不可重复读问题。最终这个问题由 MVCC 方法解决。
每一个 insert、delete、update 都会加一个 X（排他锁），非事务的 DML 语句是在语句执行完毕后释放锁，而事务的 DML 语句是在整个事务执行完毕后释放锁。锁的范围是根据索引来走的，如果匹配语句中未走索引就会直接锁表来保证事务的一致性。
我们可以来测试一下：
\===A窗口===
start TRANSACTION; update callback set title = '444' where id = 1\===B窗口===
update callback set title = '444' where id = 1运行完 A 窗口后再运行 B 窗口发现 B 窗口会一直卡在执行的进度上一直没有结果，而当 A 窗口事务回滚或者提交后，B 窗口的结果就出来了，这就说明了在事务期间的 DML 语句都会将相应的匹配行给加锁。
我们再来一个锁表的测试：
\===A窗口===
start TRANSACTION; update callback set title = '444' where account_id = 4\===B窗口===
update callback set title = '444' where id = 1account_id 是没有索引的。这时候分别执行完 SQL 会发现 B 窗口是卡着的状态，按理来说它只会锁住 account_id 为 4 的数据，但是实际上这时候发生了锁表操作了。我们再给 account_id 加上普通索引再次执行发现 B 窗口直接执行完毕了。
所以说为什么建议将大事务（锁的记录多，运行时间长）拆分成小事务，因为加锁的行数太多再加上运行时间长，就会影响其他操作相应数据的 SQL，而拆分成小事务的结果是，运行完事务就会释放锁，然后再加锁，中间的影响时间就会变得非常小。
就像单核 CPU 中的多线程一样，看起来是多线程，实际上是因为线程之间的切换速度快而产生的并行效果。
1.2、加锁分析
在上面的行为我们可以分析出，如果是像 = 这种的匹配型操作符，MySQL 是锁住对应的匹配记录的。而如果是范围匹配，MySQL 是如何加锁的呢？
假设我们有 ID 为 1、4、5 的三条数据，ID是主键，然后下面执行这样一条更新语句：
start TRANSACTION; update callback set title = '444' where id > 3你们觉得它锁的范围是到哪， 4 之后的数据吗？ NO NO NO，上面说了，锁的范围是根据索引来走的，现在索引有哪些， 1,4,5这三个索引，也就是锁的范围可能有 (-∞,1] 、(1,4]、(4,5]、(5,+∞]，而 > 3 的范围也就是落在了(1,4]、(4,5]、(5,+∞] 这个范围内，所以说如果插入一条 ID 为 2 的数据，SQL 执行也是会被阻塞的。
不行我们可以自己尝试一下：
insert into callback(id,account_id) values(2,1)1.3、Java 中的事务语句分析
@Transactional(rollbackFor = Exception.class)
public void test(){
// 获取钱包的金币
int coin = testService.getCoin();
if (coin > 10){
// 如果钱包金币充足，则减金币
testService.saleCoin();
}
}最上面说明了以上的语句为什么数据是不可靠的，coin 获取的可能是旧的数据，其他操作如果已经对数据进行了修改，然后 Java 层面保存了旧数据的副本，最后操作下来可能导致金币是负的。
我们可以在数据库层面来保证数据的一致性，也就是可以这样：
update wallet set coin = coin - 10 where id = 1 and cion >= 10因为同一条记录的 SQL 是按顺序执行下来的，所以这里只要判断是否执行成功就可以保证数据不成负值了。
同时这种同步效果也在分布式应用单数据库中有效。就算你是多个 Java 应用执行并发修改数据库数据，但是因为我数据库是一个，我在数据库层面上保证了数据的一致性，我的数据就是有效的，这个效果和 redis 的分布式锁有着差不多的效果。
我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。