MySQL 下的持有锁分析 | 8月更文挑战

·  阅读 108

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 = 1rollback
复制代码

A 窗口开启事务,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 = 1
复制代码

account_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 的分布式锁有着差不多的效果。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改