SQL的事务控制

为什么需要事务控制

首先得明确什么是事务，事务是数据库中将多个相关操作打包成一个“不可分割工作单元”的机制，保证这些操作要么全部成功，要么全部失败，不存在中间状态。

如果不存在事务，将会是什么样的？下面用一个经典的转账问题来回答。

先创建一些数据库表

CREATE DATABASE IF NOT EXISTS `transaction_lab`;
USE `transaction_lab`;
-- 银行账户表
DROP TABLE IF EXISTS `bank_account`;
CREATE TABLE `bank_account` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `account_name` VARCHAR(50) NOT NULL,
    `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

初始化一些数据

INSERT INTO `bank_account` (`account_name`, `balance`) VALUES
('张三', 1000.00),
('李四', 500.00);

现在模拟一个没有事务的场景

-- 第一步：从张三账户扣款100元
UPDATE `bank_account` SET `balance` = `balance` - 100 WHERE `account_name` = '张三';
-- 人为触发一个sql语句错误
SELECT 1/0;
-- 第二步：向李四账户增加100元
UPDATE `bank_account` SET `balance` = `balance` + 100 WHERE `account_name` = '李四';

这会使得张三的账户扣除了100元之后，整个sql停在了SELECT 1/0;的执行，李四的账户并没有增加100元，导致这100元不翼而飞。

而如果开启了事务，在sql出现错误的情况下，会回滚整个语句，张三的账户就不会被扣除100元。

事务的ACID特性

下面来介绍事务的ACID四个特性：

1. 原子性：事务是最小工作单元，不可再分割

-- MySQL通过undo log实现原子性
START TRANSACTION;
-- 这些操作被视为一个整体
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 要么都成功(COMMIT)，要么都失败(ROLLBACK)
COMMIT;

2. 一致性：事务前后数据库必须保持逻辑一致性

-- 转账前后，总金额应保持不变
START TRANSACTION;
-- 事务前：张三1000 + 李四500 = 1500
-- 转账100元
UPDATE bank_account SET balance = balance - 100 WHERE account_name = '张三';
UPDATE bank_account SET balance = balance + 100 WHERE account_name = '李四';
-- 事务后：张三900 + 李四600 = 1500（总金额不变）
COMMIT;

3. 隔离性：并发事务相互隔离，互不干扰

-- 会话1
START TRANSACTION;
UPDATE bank_account SET balance = balance - 100 WHERE account_name = '张三';
-- 此时未提交，其他会话看不到这个修改

-- 会话2（同时执行）
SELECT balance FROM bank_account WHERE account_name = '张三';
-- 默认隔离级别下，仍看到原始值，看不到会话1的未提交修改

4. 持久性：事务提交后，修改永久保存

START TRANSACTION;
UPDATE bank_account SET balance = 2000 WHERE account_name = '张三';
COMMIT;  -- 提交后，即使数据库重启，张三余额仍是2000

事务的自动提交

现在有朋友可能会有一些困惑，那我单独写一条update语句，没开启事务，也没进行提交啊，为什么数据库中的数据还是改变了？

因为在默认情况下每条sql语句都是处于自动提交状态

-- 查看当前自动提交设置
SHOW VARIABLES LIKE 'autocommit';

-- 关闭自动提交（慎用！）
SET autocommit = 0;

-- 此时每条SQL都需显式提交
UPDATE bank_account SET balance = 1500 WHERE account_name = '张三';
COMMIT;  -- 必须显式提交

-- 恢复自动提交
SET autocommit = 1;

一个实例

-- 重置数据
UPDATE bank_account SET balance = 1000.00 WHERE account_name = '张三';
UPDATE bank_account SET balance = 500.00 WHERE account_name = '李四';

-- 开启两个MySQL客户端会话，模拟并发转账

-- 会话1：
START TRANSACTION;
UPDATE bank_account SET balance = balance - 200 WHERE account_name = '张三';
-- 暂停，不提交也不回滚

-- 会话2：
START TRANSACTION;
UPDATE bank_account SET balance = balance - 300 WHERE account_name = '张三';

那么上述场景会发生什么？

答案是会话2会一直等待执行update语句，直到会话1提交事务。

原理：会话1的update语句会为account_name = '张三'的这条记录加上一个 排他锁（X锁），在会话1执行 COMMIT或 ROLLBACK结束事务之前，这个锁会一直持有。 当会话2也为该行执行update语句时，也会试图获取锁，但是这锁已经被会话1占用了。