数据库表设计 悲观锁/乐观锁 Redis 高并发场景实践

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY idx_username (username),
    UNIQUE KEY idx_email (email)
) COMMENT '用户表';

-- 帖子表
CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '帖子ID',
    user_id BIGINT NOT NULL COMMENT '作者ID',
    title VARCHAR(200) NOT NULL COMMENT '标题',
    content TEXT NOT NULL COMMENT '内容',
    likes_count INT NOT NULL DEFAULT 0 COMMENT '点赞数',
    comments_count INT NOT NULL DEFAULT 0 COMMENT '评论数',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1-正常,2-已删除',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号';
    KEY idx_user_id (user_id) COMMENT '用户ID索引,用于查询用户的帖子列表',
    KEY idx_created_at (created_at) COMMENT '创建时间索引,用于按时间排序'
) COMMENT '帖子表';

-- 帖子点赞表
CREATE TABLE post_likes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '点赞ID',
    post_id BIGINT NOT NULL COMMENT '帖子ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY idx_post_user (post_id, user_id) COMMENT '帖子用户联合唯一索引,防止重复点赞',
    KEY idx_user_id (user_id) COMMENT '用户ID索引,用于查询用户的点赞列表'
) COMMENT '帖子点赞表';

-- 评论表
CREATE TABLE comments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
    post_id BIGINT NOT NULL COMMENT '帖子ID',
    user_id BIGINT NOT NULL COMMENT '评论者ID',
    parent_id BIGINT DEFAULT NULL COMMENT '父评论ID,回复评论时使用',
    content TEXT NOT NULL COMMENT '评论内容',
    likes_count INT NOT NULL DEFAULT 0 COMMENT '点赞数',
    replies_count INT NOT NULL DEFAULT 0 COMMENT '回复数',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1-正常,2-已删除',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号';
    KEY idx_post_id (post_id) COMMENT '帖子ID索引,用于查询帖子的评论列表',
    KEY idx_user_id (user_id) COMMENT '用户ID索引,用于查询用户的评论列表',
    KEY idx_parent_id (parent_id) COMMENT '父评论ID索引,用于查询评论的回复列表'
) COMMENT '评论表';

-- 评论点赞表
CREATE TABLE comment_likes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '点赞ID',
    comment_id BIGINT NOT NULL COMMENT '评论ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY idx_comment_user (comment_id, user_id) COMMENT '评论用户联合唯一索引,防止重复点赞',
    KEY idx_user_id (user_id) COMMENT '用户ID索引,用于查询用户的点赞列表'
) COMMENT '评论点赞表';

-- 第一步：插入点赞记录成功
INSERT INTO post_likes (post_id, user_id) VALUES (1, 1);

-- 第二步：更新帖子点赞数
-- 假设在这时候数据库突然崩溃了或者网络中断了
UPDATE posts SET likes_count = likes_count + 1 WHERE id = 1;

BEGIN;
    INSERT INTO post_likes (post_id, user_id) VALUES (1, 1);
    UPDATE posts SET likes_count = likes_count + 1 WHERE id = 1;
COMMIT;

-- 用户A的操作
UPDATE posts SET likes_count = likes_count + 1 WHERE id = 1;

-- 用户B的操作(同时进行, 则都会读取到 likes_count = 100)
UPDATE posts SET likes_count = likes_count + 1 WHERE id = 1;

BEGIN;
    -- 插入点赞记录
    INSERT INTO post_likes (post_id, user_id) VALUES (1, 1);
    -- 更新点赞数
    UPDATE posts SET likes_count = likes_count + 1 WHERE id = 1;
COMMIT;

-- 开启事务保证多个操作的原子性(数据一致性)
BEGIN;
    -- 手工显式加x锁，防止其他事务在判断和扣减期间修改库存
    SELECT stock FROM products WHERE product_id = 100 FOR UPDATE;
    -- 根据读取的库存进行判断
    IF (stock >= 5) THEN
        UPDATE products SET stock = stock - 5 WHERE product_id = 100;
    END IF;
COMMIT;

@Transactional
public void likePost(Long postId, Long userId) {
    String likesUsersKey = "post:" + postId + ":likes_users";
    String likesCountKey = "post:" + postId + ":likes_count";
    // 1. 检查是否已点赞
    if (Boolean.TRUE.equals(redisTemplate.opsForSet().isMember(likesUsersKey, userId))) {
        return;
    }

    // 2. Redis 操作：添加用户ID到点赞集合 & 计数+1
    redisTemplate.opsForSet().add(likesUsersKey, userId);
    redisTemplate.opsForValue().increment(likesCountKey, 1);

    // 3. 发送 Kafka 事件 (type=like)
    String event = "like," + postId + "," + userId;
    kafkaTemplate.send("like-topic", event);
}

redisTemplate.opsForSet().add(likesUsersKey, userId);

public void likePost(Long postId, Long userId) {
    String likesUsersKey = "post:" + postId + ":likes_users";
    String likesCountKey = "post:" + postId + ":likes_count";

    // sAddRet 要么是 1（成功加入，不在集合中），要么是 0（已存在，没加入）
    Long sAddRet = redisTemplate.opsForSet().add(likesUsersKey, userId);
    if (sAddRet != null && sAddRet > 0) {
        // 只有在成功新加了用户的时候才执行加1
        redisTemplate.opsForValue().increment(likesCountKey, 1);
        // 同时再发Kafka事件
        String event = "like," + postId + "," + userId;
        kafkaTemplate.send("like-topic", event);
    }
}

public boolean deductStock(String productId, int amount) {
    String key = "stock:" + productId;
    // 1. 检查库存
    Integer stock = redis.get(key);
    if (stock == null || stock < amount) {
        return false; // 库存不足
    }

    // 2. 原子扣减
    Integer newStock = redis.decrBy(key, amount);
    if (newStock < 0) {
        // 库存不足，手动回滚
        redis.incrBy(key, amount);
        return false;
    }
    return true; // 扣减成功
}

local stock = tonumber(redis.call('get', KEYS[1]))
if stock and stock >= tonumber(ARGV[1]) then
    return redis.call('decrby', KEYS[1], ARGV[1])
else
    return -1
end

public boolean deductStock(String productId, int amount) {
    String key = "stock:" + productId;
    String luaScript = "local stock = tonumber(redis.call('GET', KEYS[1]))\n" +
                       "local amount = tonumber(ARGV[1])\n" +
                       "if stock == nil then return -1 end\n" +
                       "if stock < amount then return -2 end\n" +
                       "local newStock = stock - amount\n" +
                       "redis.call('SET', KEYS[1], newStock)\n" +
                       "return newStock";
    // Lua 脚本在 Redis 内部执行, 效率极高
    Long result = redis.eval(luaScript, Collections.singletonList(key), Collections.singletonList(String.valueOf(amount)));
    return result >= 0; // >= 0 表示扣减成功
}

public boolean deductStock(String productId, int amount) {
    String lockKey = "lock:" + productId;
    String stockKey = "stock:" + productId;
    
    // 获取分布式锁
    boolean locked = redis.setNX(lockKey, "1", 10);
    if (!locked) {
        return false; // 获取锁失败, 被其他线程占用
    }
    
    try {
        // 检查库存
        Integer stock = redis.get(stockKey);
        if (stock == null || stock < amount) {
            return false;
        }
        
        // 扣减库存
        Integer newStock = redis.decrBy(stockKey, amount);
        if (newStock < 0) {
            redis.incrBy(stockKey, amount); // 回滚
            return false;
        }
        
        // 异步更新数据库
        asyncExecutor.submit(() -> {
            try {
                updateDatabase(productId, amount);
            } catch (Exception e) {
                // 数据库更新失败，回滚 Redis
                redis.incrBy(stockKey, amount);
                log.error("Database update failed, rolled back stock", e);
            }
        });
        return true;
    } finally {
        redis.del(lockKey); // 释放锁
    }
}