MySQL - 5、事务和锁操作

短命女 2023-10-14 08:43 33阅读 0赞

有一个简单的数据库，其中有两张表：users 和 transactions。users 表用于存储用户信息，transactions 表用于存储交易信息。

## 首先，创建数据库和表： ##

CREATE DATABASE IF NOT EXISTS my_database;
    
    USE my_database;
    
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      balance DECIMAL(10, 2) NOT NULL DEFAULT 0
    );
    
    CREATE TABLE IF NOT EXISTS transactions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      amount DECIMAL(10, 2) NOT NULL,
      type ENUM('deposit', 'withdraw') NOT NULL,
      timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

## 插入一些测试数据： ##

INSERT INTO users (username, balance) VALUES
      ('lfsun-1', 1000.00),
      ('lfsun-2', 500.00),
      ('lfsun-4', 200.00);

## 现在，我们将使用事务来模拟转账操作。假设我们要将 100.00 的金额从 lfsun-1 转账给 lfsun-2： ##

-- 开始事务
    START TRANSACTION;
    
    -- 更新 lfsun-1 的余额
    UPDATE users
    SET balance = balance - 100.00
    WHERE username = 'lfsun-1';
    
    -- 更新 lfsun-2 的余额
    UPDATE users
    SET balance = balance + 100.00
    WHERE username = 'lfsun-2';
    
    -- 向 transactions 表插入一条记录表示转账操作
    INSERT INTO transactions (user_id, amount, type)
    VALUES ((SELECT id FROM users WHERE username = 'lfsun-1'), 100.00, 'withdraw'),
           ((SELECT id FROM users WHERE username = 'lfsun-2'), 100.00, 'deposit');
    
    -- 提交事务
    COMMIT;

## 现在，假设转账出现了问题，我们需要回滚操作： ##

## 在某些情况下，可能需要使用保存点（SAVEPOINT）来实现更细粒度的事务控制： ##

-- 开始事务
    START TRANSACTION;
    
    -- 更新 lfsun-1的余额
    UPDATE users SET balance = balance - 100.00 WHERE username = 'lfsun-1';
    
    -- 保存点1
    SAVEPOINT point1;
    
    -- 更新 lfsun-2 的余额
    UPDATE users SET balance = balance + 100.00 WHERE username = 'lfsun-2';
    
    -- 向 transactions 表插入一条记录表示转账操作
    INSERT INTO transactions (user_id, amount, type) VALUES
      ((SELECT id FROM users WHERE username = 'lfsun-1'), 100.00, 'withdraw'),
      ((SELECT id FROM users WHERE username = 'lfsun-2'), 100.00, 'deposit');
    
    -- 发生错误，回滚到保存点1
    ROLLBACK TO point1;
    
    -- 提交事务
    COMMIT;

## 最后，演示表锁（LOCK TABLES 和 UNLOCK TABLES）的使用： ##

-- 锁定 users 表，防止其他会话对其进行修改
    LOCK TABLES users WRITE;
    
    -- 执行一些操作，例如更新或插入数据
    
    -- 解锁 users 表
    UNLOCK TABLES;