Java面试事务的特性：ACID、隔离级别与工程实践全景解析

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 若任一步骤失败，整个事务回滚

-- MySQL全局设置
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 会话级设置（当前连接）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Spring Boot配置（application.yml）
spring:
  datasource:
    hikari:
      connection-init-sql: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 查看长事务（MySQL 5.7+）
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE time > 60 -- 超过60秒
  AND command != 'Sleep';
-- 查看当前事务（MySQL 8.0）
SELECT * FROM performance_schema.data_locks WHERE lock_duration = 'TRANSACTION';

@Service
public class UserService {
    @Transactional(propagation = Propagation.REQUIRED)
    public void methodA() {
        // 主事务开始
        methodB();  // REQUIRES_NEW：新建独立事务
        // methodB()失败回滚，但methodA()继续执行
        throw new RuntimeException("main fail");  -- methodA回滚，methodB提交的数据不回滚
    }
    @Transactional(propagation = Propagation.NESTED)
    public void methodC() {
        // 嵌套事务：有savepoint
        throw new RuntimeException("nested fail");
        -- methodC失败回滚到savepoint，methodA可继续执行
    }
}

-- 版本链结构（简化）
record(version=3, trx_id=102, prev_version=version=2)
record(version=2, trx_id=101, prev_version=version=1)
record(version=1, trx_id=100, prev_version=null)
-- T1启动（trx_id=103），Read View=[100,101,102]
SELECT * FROM t WHERE id=1;
→ 查version=3：trx_id=102 ∈ Read View → 不可见
→ 查version=2：trx_id=101 ∈ Read View → 不可见
→ 查version=1：trx_id=100 ∉ Read View（且100已提交）→ 可见
结果：返回version=1的数据

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row(s) locked
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`user`
lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000003000; asc     0 ;;
...
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec fetching rows
...
*** (2) HOLDS THE LOCK:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`user`
lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
...
*** WE ROLL BACK TRANSACTION (2)

-- 1. 创建消息表（与业务表同库）
CREATE TABLE message_queue (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  biz_id VARCHAR(64) NOT NULL,
  msg_type VARCHAR(32) NOT NULL,
  payload JSON NOT NULL,
  status TINYINT DEFAULT 0,  -- 0:待发送, 1:已发送, 2:已消费
  create_time DATETIME DEFAULT NOW(),
  update_time DATETIME DEFAULT NOW() ON UPDATE NOW()
);
-- 2. 业务逻辑（本地事务）
@Transactional
public void placeOrder(Order order) {
    // 1. 创建订单
    orderMapper.insert(order);
    // 2. 插入消息（与订单同一事务）
    Message msg = new Message(order.getId(), "ORDER_CREATED", order);
    messageMapper.insert(msg);  -- status=0
}
-- 3. 消息服务轮询发送（幂等）
SELECT * FROM message_queue
WHERE status = 0 AND create_time < NOW() - INTERVAL 1 MINUTE
LIMIT 100;
-- 4. 发送成功后更新状态
UPDATE message_queue
SET status = 1, update_time = NOW()
WHERE id = ? AND status = 0;  -- 乐观锁防重复发送