MySQL 事务解析

-- 方式1：表格形式显示（适合快速查看所有引擎）
show engines;
-- 方式2：行形式显示（适合查看详细信息，加\g格式化输出）
show engines \g;

*************************** 1. row ***************************
      engine: innodb
     support: default  --  mysql默认引擎
     comment: supports transactions, row-level locking, and foreign keys  -- 支持事务、行级锁、外键
transactions: yes  -- 支持事务（核心）
          xa: yes
  savepoints: yes  -- 支持保存点（后面会讲）
*************************** 5. row ***************************
      engine: myisam
     support: yes
     comment: myisam storage engine
transactions: no  -- 不支持事务（核心）
          xa: no
  savepoints: no  -- 不支持保存点

-- 转换表引擎为innodb
alter table 表名 engine=innodb;

create table 表名 (
    字段1 类型 约束,
    字段2 类型 约束
) engine=innodb default charset=utf8mb4; -- utf8mb4支持emoji，推荐使用

-- 查看表的引擎
show table status like '表名' \g;
-- 重点看engine字段，显示innodb即为支持事务

-- 查看autocommit变量（on=自动提交，off=手动提交）
show variables like 'autocommit';

+---------------+-------+
| variable_name | value |
+---------------+-------+
| autocommit    | on    |
+---------------+-------+

-- 新建测试表（innodb引擎）
create table account (
    id int primary key auto_increment,
    name varchar(50) not null,
    balance decimal(10,2) not null default 0.0
) engine=innodb default charset=utf8mb4;
-- 插入一条记录（自动提交，无需commit）
insert into account (name, balance) values('张三', 1000.00);
-- 查看记录（能查到，因为insert已自动提交）
select * from account; -- 结果：id=1, name=张三, balance=1000.00
-- 修改记录（自动提交，无需commit）
update account set balance=2000.00 where id=1;
-- 再次查看（能查到修改后的数据，update已自动提交）
select * from account where id=1; -- 结果：balance=2000.00
-- 删除记录（自动提交，无需commit）
delete from account where id=1;
-- 再次查看（记录已删除，delete已自动提交）
select * from account where id=1; -- 结果：空

-- 方式1：关闭自动提交（当前会话有效，重启客户端失效）
set autocommit=0; -- 0=关闭，1=开启
-- 方式2：验证关闭结果（确保autocommit=off）
show variables like 'autocommit'; -- 结果：value=off
-- 开启事务（两种写法都可以，推荐begin，更简洁）
begin;
-- 或 start transaction;
-- 执行第一条sql（插入记录，事务内未提交）
insert into account (name, balance) values('张三', 1000.00);
-- 执行第二条sql（修改记录，事务内未提交）
update account set balance=balance+500 where id=1; -- 张三余额变成1500.00
-- 查看事务内的修改（当前事务能看到，其他事务看不到，这就是隔离性所在）
select * from account where id=1; -- 结果：balance=1500.00
-- 提交事务（所有操作永久生效）
commit;
-- 再次查看（修改已生效）
select * from account where id=1; -- 结果：balance=1500.00
-- 再次开启事务
begin;
-- 执行第三条sql（扣减余额）
update account set balance=balance-1000 where id=1; -- 张三余额变成500.00
-- 查看事务内的修改
select * from account where id=1; -- 结果：balance=500.00
-- 回滚事务（撤销所有未提交的操作）
rollback;
-- 查看回滚后结果（修改被撤销，余额恢复到1500.00）
select * from account where id=1; -- 结果：balance=1500.00

begin;
-- ddl语句（自动提交，事务直接结束）
create table test (id int);
rollback; -- 无效，test表已经创建成功

-- 自动提交开启（autocommit=on）
show variables like 'autocommit'; -- 结果：on
begin;
insert into account (name, balance) values('李四', 2000.00);
-- 未提交，其他事务看不到
select * from account where name='李四'; -- 当前事务能看到，其他事务看不到
commit; -- 提交后，其他事务才能看到

-- 写法1：begin（推荐，简洁易懂）
begin;
-- 写法2：start transaction（功能完全一致，更规范）
start transaction;

begin;
-- 事务内的所有操作都会被打包
insert into account (name, balance) values('王五', 3000.00);
update account set balance=balance+500 where name='王五';

commit;

begin;
insert into account (name, balance) values('赵六', 1500.00);
commit; -- 提交后，赵六的记录永久生效

rollback;

begin;
update account set balance=balance-1000 where name='张三'; -- 张三余额1500→500
update account set balance=balance+1000 where name='不存在的用户'; -- sql执行失败（无此用户）
rollback; -- 回滚所有操作，张三余额恢复到1500
select * from account where name='张三'; -- 结果：balance=1500

-- 1. 创建保存点（语法：savepoint 保存点名称）
savepoint save1;
-- 2. 回滚到保存点（语法：rollback to 保存点名称）
rollback to save1;
-- 3. 删除保存点（语法：release savepoint 保存点名称）
release savepoint save1;

-- 准备工作：关闭自动提交，account表已有张三（1500）、李四（2000）、王五（3500）
set autocommit=0;
begin;
-- 第一步：张三余额+500（1500→2000）
update account set balance=balance+500 where name='张三';
-- 创建保存点save1（标记第一步完成）
savepoint save1;
-- 第二步：李四余额+500（2000→2500）
update account set balance=balance+500 where name='李四';
-- 创建保存点save2（标记第二步完成）
savepoint save2;
-- 第三步：王五余额-1000（3500→2500）
update account set balance=balance-1000 where name='王五';
-- 创建保存点save3（标记第三步完成）
savepoint save3;
-- 查看当前状态（三步都执行了）
select name, balance from account;
-- 结果：张三2000，李四2500，王五2500
-- 回滚到save2（撤销第三步：王五余额恢复到3500）
rollback to save2;
-- 查看回滚后状态
select name, balance from account;
-- 结果：张三2000，李四2500，王五3500（第三步被撤销）
-- 回滚到save1（撤销第二步和第三步：李四2000，王五3500）
rollback to save1;
-- 查看回滚后状态
select name, balance from account;
-- 结果：张三2000，李四2000，王五3500（第二步、第三步都被撤销）
-- 执行第四步：张三余额+1000（2000→3000）
update account set balance=balance+1000 where name='张三';
-- 查看当前状态
select name, balance from account;
-- 结果：张三3000，李四2000，王五3500
-- 回滚到事务开始（撤销所有操作）
rollback;
-- 查看最终状态（所有修改都被撤销）
select name, balance from account;
-- 结果：张三1500，李四2000，王五3500

begin;
update account set balance=balance+500 where name='张三';
savepoint save1;
commit; -- 提交后，save1被删除
rollback to save1; -- 报错：savepoint save1 does not exist

begin;
savepoint save1;
update account set balance=balance+500 where name='张三';
savepoint save2;
rollback to save1; -- 回滚后，save2被删除
rollback to save2; -- 报错：savepoint save2 does not exist

begin;
update account set balance=balance-1000 where name='张三'; -- 张三1500→500
update account set balance=balance+1000 where name='李四'; -- 李四2000→3000

select name, balance from account;
-- 结果：张三1500，李四2000（修改被回滚，原子性成立）

set autocommit=0;
begin;
update account set balance=balance-1000 where name='张三'; -- 张三1500→500
update account set balance=balance+1000 where name='李四'; -- 李四2000→3000
commit; -- 提交事务

select name, balance from account;
-- 结果：张三500，李四3000（修改永久生效，持久性成立）

begin;
update account set balance=balance+500 where name='张三'; -- 张三500→1000

begin;
select name, balance from account where name='张三';
-- 结果：500（看不到客户端a未提交的修改，隔离性成立）

commit;

select name, balance from account where name='张三';
-- 结果：500（同一事务内，依然看不到提交的修改，可重复读特性）

commit;
select name, balance from account where name='张三';
-- 结果：1000（事务结束后，看到最新数据）

-- 客户端a：关闭自动提交，开启事务
set autocommit=0;
begin;
-- 查询转账前总金额
select sum(balance) from account; -- 结果：500（张三）+3000（李四）+3500（王五）=7000
-- 执行转账操作（张三转1000给王五）
update account set balance=balance-1000 where name='张三'; -- 张三500→-500？不，业务逻辑不允许！
-- 这里故意写错，验证一致性需要业务逻辑配合，正确的应该先判断余额是否充足：
rollback; -- 回滚错误操作
-- 正确的转账流程
begin;
-- 先判断余额是否充足
if (select balance from account where name='张三') < 1000 then
    signal sqlstate '45000' set message_text='余额不足';
end if;
-- 执行转账
update account set balance=balance-1000 where name='张三'; -- 张三500→-500？不，余额只有500，会触发业务逻辑判断失败
-- 实际执行会报错：余额不足，事务回滚
-- 调整转账金额为300（张三500→200）
update account set balance=balance-300 where name='张三';
update account set balance=balance+300 where name='王五'; -- 王五3500→3800
-- 查询转账后总金额
select sum(balance) from account; -- 结果：200+3000+3800=7000（总金额不变，一致性成立）
-- 提交事务
commit;

-- 方式1：查看全局隔离级别（影响所有新连接，不影响当前连接）
select @@global.tx_isolation;
-- 方式2：查看当前会话隔离级别（影响当前连接，默认和全局一致）
select @@session.tx_isolation;
-- 方式3：查看当前事务隔离级别（和会话隔离级别一致）
select @@tx_isolation;

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| repeatable-read       |
+-----------------------+

-- 语法：set [global | session] transaction isolation level 隔离级别;
-- 隔离级别可选：read uncommitted / read committed / repeatable read / serializable
-- 方式1：设置全局隔离级别（需要重启客户端生效，影响所有新连接）
set global transaction isolation level read uncommitted;
-- 方式2：设置当前会话隔离级别（立即生效，只影响当前连接）
set session transaction isolation level read committed;

set global transaction isolation level read uncommitted;
-- 重启客户端后
set autocommit=0;

set autocommit=0;

begin;
update account set balance=balance+500 where name='张三'; -- 张三200→700（未提交）

begin;
select name, balance from account where name='张三';
-- 结果：700（读到未提交的修改，脏读出现）

rollback;

select name, balance from account where name='张三';
-- 结果：200（脏数据消失，验证脏读问题）

set global transaction isolation level read committed;
-- 重启客户端后
set autocommit=0;

set autocommit=0;

begin;
update account set balance=balance+500 where name='张三'; -- 张三200→700（未提交）

begin;
select name, balance from account where name='张三';
-- 结果：200（看不到未提交的修改，脏读问题解决）

commit;

select name, balance from account where name='张三';
-- 结果：700（同一事务内两次查询结果不同，不可重复读出现）

set global transaction isolation level repeatable read;
-- 重启客户端后
set autocommit=0;

set autocommit=0;

begin;
select name, balance from account where name='张三';
-- 结果：700（第一次查询，生成快照）

begin;
update account set balance=balance+500 where name='张三'; -- 张三700→1200
commit; -- 提交事务

select name, balance from account where name='张三';
-- 结果：700（同一事务内，结果和第一次一致，不可重复读问题解决）

commit;
select name, balance from account where name='张三';
-- 结果：1200（事务结束后，看到最新数据）

begin;
-- 查询“余额<1000的用户”（当前只有李四3000、王五3800，张三1200，无符合条件的用户）
select name, balance from account where balance<1000;
-- 结果：空（第一次查询，生成快照）

begin;
insert into account (name, balance) values('赵六', 800); -- 赵六余额800，符合条件
commit; -- 提交事务

select name, balance from account where balance<1000;
-- 结果：空（同一事务内，看不到新插入的记录，幻读问题解决）

commit;
select name, balance from account where balance<1000;
-- 结果：赵六800（事务结束后，看到新插入的记录）

set global transaction isolation level serializable;
-- 重启客户端后
set autocommit=0;

set autocommit=0;

begin;
select name, balance from account where name='张三';
-- 结果：1200（加共享锁，阻止其他事务修改）

begin;
-- 尝试修改张三的余额（被阻塞，直到客户端a提交/回滚）
update account set balance=balance+500 where name='张三';
-- 卡住，无响应（阻塞中）

commit; -- 提交后，释放锁

-- 阻塞解除，修改成功
query ok, 1 row affected (12.36 sec) -- 阻塞了12秒，直到a提交
-- 查询结果
select name, balance from account where name='张三';
-- 结果：1700（修改成功）

insert into account (name, balance) values('张三', 1000.00);

-- 事务id=101，修改张三的余额为1500
begin;
update account set balance=1500.00 where id=1;
commit;
-- 事务id=102，修改张三的余额为2000
begin;
update account set balance=2000.00 where id=1;
commit;

-- 事务a（id=106）：关闭自动提交，开启事务，第一次查询
set autocommit=0;
begin;
select name, balance from account where id=1;

-- 事务b（id=107）：关闭自动提交，开启事务，修改并提交
set autocommit=0;
begin;
update account set balance=2500.00 where id=1; -- db_trx_id=107，balance=2500
commit; -- 提交事务

-- 事务a：第二次查询张三的余额
select name, balance from account where id=1;

-- 事务a：提交事务，第三次查询
commit;
select name, balance from account where id=1;

-- 事务a（可重复读级别）
set autocommit=0;
begin;
-- 快照读：第一次查询
select * from account where id=1; -- 结果：2000
-- 事务b
set autocommit=0;
begin;
update account set balance=2500 where id=1;
commit;
-- 事务a：快照读（第二次查询，结果不变）
select * from account where id=1; -- 结果：2000
-- 事务a：当前读（查询最新版本）
select * from account where id=1 for update; -- 结果：2500
-- 事务a：快照读（第三次查询，结果还是2000）
select * from account where id=1; -- 结果：2000

create table 表名 (字段 类型 约束) engine=innodb default charset=utf8mb4;

alter table 表名 engine=innodb;

show table status like '表名' \g;
-- 查看engine字段，显示innodb即为成功

set autocommit=0;

show variables like 'autocommit';

begin;
insert into account (name, balance) values('孙七', 2000.00);
-- 未提交，其他客户端看不到
rollback; -- 回滚后，记录消失

-- 错误示例：事务中包含ddl
begin;
update account set balance=balance+500 where name='张三';
create table test (id int); -- ddl自动提交事务
rollback; -- 无效，update已提交

-- 设置全局隔离级别
set global transaction isolation level repeatable read;
-- 重启客户端生效

select @@global.tx_isolation, @@session.tx_isolation;

-- 错误示例：长事务包含远程调用
begin;
update account set balance=balance-100 where name='张三';
-- 远程调用第三方支付接口（耗时可能几秒到几十秒）
call third_party_pay(100);
update account set balance=balance+100 where name='商家';
commit;

-- 拆分后：先扣钱，提交事务
begin;
update account set balance=balance-100 where name='张三';
insert into pay_log (user_name, amount, status) values('张三', 100, 'pending');
commit;
-- 远程调用（事务外）
call third_party_pay(100);
-- 支付成功后，更新状态、商家加钱
begin;
update pay_log set status='success' where user_name='张三' and amount=100;
update account set balance=balance+100 where name='商家';
commit;

-- 查看执行时间超过60秒的长事务
select * from information_schema.innodb_trx 
where time_to_sec(timediff(now(), trx_started)) > 60;
-- 终止长事务（trx_id为事务id）
kill trx_id;

-- 事务a：扣减库存
begin;
-- 加行锁，其他事务无法修改该记录，直到当前事务提交/回滚
select * from product where id=1 for update;
update product set stock=stock-1 where id=1;
commit;
-- 事务b：同时扣减库存（会被阻塞，直到事务a提交）
begin;
select * from product where id=1 for update;
update product set stock=stock-1 where id=1;
commit;

-- 1. 表中添加版本号字段
alter table product add version int default 1;
-- 2. 事务a：乐观锁更新
begin;
-- 查询商品信息，获取当前版本号
select stock, version from product where id=1; -- stock=10, version=1
-- 更新时校验版本号，只有版本号一致才更新
update product set stock=stock-1, version=version+1 where id=1 and version=1;
-- 影响行数=1，更新成功
commit;
-- 3. 事务b：同时更新
begin;
select stock, version from product where id=1; -- stock=10, version=1
update product set stock=stock-1, version=version+1 where id=1 and version=1;
-- 影响行数=0，更新失败，回滚重试
rollback;
-- 重试：重新查询版本号，再次更新
begin;
select stock, version from product where id=1; -- stock=9, version=2
update product set stock=stock-1, version=version+1 where id=1 and version=2;
-- 影响行数=1，更新成功
commit;

-- 示例：业务主键（日期+序列号）
insert into order (order_id, user_id) 
values(concat(date_format(now(), '%y%m%d'), lpad(last_insert_id()+1, 4, '0')), 1);

begin;
update account set balance=balance+500 where name='张三';
savepoint save1;
update account set balance=balance+500 where name='李四';
rollback to save1; -- 有效，回滚到save1
commit; -- 提交后，save1被删除
rollback to save1; -- 报错：savepoint save1 does not exist

begin;
savepoint save1;
update account set balance=balance+500 where name='张三';
savepoint save2;
rollback to save1; -- 回滚后，save2被删除
rollback to save2; -- 报错：savepoint save2 does not exist

-- 正确示例：where条件+索引字段
begin;
select * from account where id=1 for update; -- id有主键索引，加行锁
update account set balance=balance+500 where id=1;
commit;

# redo log大小（默认48m）
innodb_log_file_size = 2g
innodb_log_group_home_dir = /var/lib/mysql/
# undo log表空间大小
innodb_undo_tablespaces = 3
innodb_undo_log_size = 1g

-- 查看redo log使用情况
show engine innodb status like 'log%';
-- 查看undo log使用情况
select * from information_schema.innodb_trx_undo;

-- 1. 创建转账日志表
create table transfer_log (
    id int primary key auto_increment,
    from_name varchar(50) not null, -- 转出用户名
    to_name varchar(50) not null, -- 转入用户名
    amount decimal(10,2) not null, -- 转账金额
    status varchar(20) not null, -- 状态：success/fail
    create_time datetime not null default now() -- 转账时间
) engine=innodb default charset=utf8mb4;
-- 2. 创建转账存储过程（含事务+异常处理）
delimiter $$
create procedure transfer_money(
    in from_name varchar(50), -- 转出用户名
    in to_name varchar(50), -- 转入用户名
    in transfer_amount decimal(10,2) -- 转账金额
)
begin
    -- 关闭自动提交
    set autocommit=0;
    begin
        -- 异常捕获：如果出错，回滚事务，记录失败日志
        declare exit handler for sqlexception
        begin
            rollback;
            -- 记录失败日志
            insert into transfer_log (from_name, to_name, amount, status)
            values(from_name, to_name, transfer_amount, 'fail');
            select '转账失败' as result;
        end;
        -- 1. 验证转出用户是否存在
        if not exists (select 1 from account where name=from_name) then
            signal sqlstate '45000' set message_text='转出用户不存在';
        end if;
        -- 2. 验证转入用户是否存在
        if not exists (select 1 from account where name=to_name) then
            signal sqlstate '45000' set message_text='转入用户不存在';
        end if;
        -- 3. 验证转出用户余额是否充足
        if (select balance from account where name=from_name) < transfer_amount then
            signal sqlstate '45000' set message_text='余额不足';
        end if;
        -- 4. 扣减转出用户余额
        update account set balance=balance-transfer_amount where name=from_name;
        -- 5. 增加转入用户余额
        update account set balance=balance+transfer_amount where name=to_name;
        -- 6. 记录成功日志
        insert into transfer_log (from_name, to_name, amount, status)
        values(from_name, to_name, transfer_amount, 'success');
        -- 7. 提交事务
        commit;
        select '转账成功' as result;
    end;
end $$
delimiter ;
-- 3. 调用存储过程：张三给李四转账1000元
call transfer_money('张三', '李四', 1000.00);
-- 4. 查看结果
select * from account where name in ('张三', '李四'); -- 查看余额变化
select * from transfer_log where from_name='张三' and to_name='李四'; -- 查看转账日志

-- 1. 商品表（含库存）
create table product (
    id int primary key,
    name varchar(50) not null,
    price decimal(10,2) not null,
    stock int not null default 0 -- 库存
) engine=innodb default charset=utf8mb4;
-- 2. 订单表
create table `order` (
    id int primary key auto_increment,
    user_id int not null, -- 用户id
    product_id int not null, -- 商品id
    product_name varchar(50) not null, -- 商品名称
    product_price decimal(10,2) not null, -- 商品单价
    num int not null, -- 购买数量
    total_amount decimal(10,2) not null, -- 订单总金额
    create_time datetime not null default now(), -- 订单创建时间
    foreign key (product_id) references product(id) -- 外键关联商品表
) engine=innodb default charset=utf8mb4;
-- 3. 插入测试数据
insert into product values(1, 'iphone 15', 5999.00, 10); -- 商品1：库存10
insert into account values(2, '用户2', 10000.00); -- 用户2：余额10000
-- 4. 创建创建订单的存储过程
delimiter $$
create procedure create_order(
    in u_id int, -- 用户id
    in p_id int, -- 商品id
    in buy_num int -- 购买数量
)
begin
    set autocommit=0;
    begin
        declare exit handler for sqlexception
        begin
            rollback;
            select '订单创建失败' as result;
        end;
        -- 声明变量：商品名称、单价、用户余额、订单总金额
        declare p_name varchar(50);
        declare p_price decimal(10,2);
        declare u_balance decimal(10,2);
        declare total decimal(10,2);
        -- 1. 查询商品信息（加行锁，防止并发修改）
        select name, price, stock into p_name, p_price, stock from product where id=p_id for update;
        -- 2. 验证库存是否充足
        if stock < buy_num then
            signal sqlstate '45000' set message_text='库存不足';
        end if;
        -- 3. 查询用户余额
        select balance into u_balance from account where id=u_id;
        -- 4. 计算订单总金额
        set total = p_price * buy_num;
        -- 5. 验证用户余额是否充足
        if u_balance < total then
            signal sqlstate '45000' set message_text='余额不足';
        end if;
        -- 6. 扣减商品库存
        update product set stock=stock-buy_num where id=p_id;
        -- 7. 扣减用户余额
        update account set balance=balance-total where id=u_id;
        -- 8. 创建订单
        insert into `order`(user_id, product_id, product_name, product_price, num, total_amount)
        values(u_id, p_id, p_name, p_price, buy_num, total);
        -- 9. 提交事务
        commit;
        select '订单创建成功' as result;
    end;
end $$
delimiter ;
-- 5. 调用存储过程：用户2购买商品1，数量2
call create_order(2, 1, 2);
-- 6. 查看结果
select * from product where id=1; -- 库存从10→8
select * from account where id=2; -- 余额从10000→10000-5999*2= -1998？不，余额充足，实际为10000-11998= -1998？错误，调整测试数据：用户2余额改为20000
update account set balance=20000 where id=2;
call create_order(2, 1, 2); -- 订单创建成功
select * from `order` where user_id=2; -- 订单记录

-- 1. 积分表
create table points (
    id int primary key auto_increment,
    user_id int not null, -- 用户id
    score int not null default 0, -- 积分
    foreign key (user_id) references account(id) -- 外键关联用户表
) engine=innodb default charset=utf8mb4;
-- 2. 插入测试数据
insert into points values(1, 2, 1000); -- 用户2：积分1000
-- 3. 创建用户注销存储过程
delimiter $$
create procedure user_logout(in u_id int)
begin
    set autocommit=0;
    begin
        declare exit handler for sqlexception
        begin
            rollback;
            select '注销失败' as result;
        end;
        -- 1. 验证用户是否存在
        if not exists (select 1 from account where id=u_id) then
            signal sqlstate '45000' set message_text='用户不存在';
        end if;
        -- 2. 删除用户积分（外键关联，需先删除子表数据）
        delete from points where user_id=u_id;
        -- 3. 删除用户订单
        delete from `order` where user_id=u_id;
        -- 4. 删除用户基本信息
        delete from account where id=u_id;
        -- 5. 提交事务
        commit;
        select '注销成功' as result;
    end;
end $$
delimiter ;
-- 4. 调用存储过程：注销用户2
call user_logout(2);
-- 5. 查看结果
select * from account where id=2; -- 无数据
select * from `order` where user_id=2; -- 无数据
select * from points where user_id=2; -- 无数据