幂等性设计知识库

Appearance

唯一索引 - 数据库约束 ​

概述 ​

利用数据库的唯一索引（Unique Index）是实现幂等性最直接、最可靠的方式之一。通过在业务关键字段上建立唯一约束，数据库层面就能保证数据的唯一性，从而实现操作的幂等性。

核心原理 ​

客户端请求                          PostgreSQL
     |                                   |
     |--- INSERT (user@example.com) ---->|
     |                                   |--- 检查唯一索引 ---|
     |                                   |--- 不存在则插入 ---|
     |<-- 201 Created -------------------|
     |                                   |
     |--- INSERT (user@example.com) ---->|
     |                                   |--- 检查唯一索引 ---|
     |                                   |--- 已存在则拒绝 ---|
     |<-- 409 Conflict -----------------|

PostgreSQL 实现 ​

1. 基础表设计 ​

sql
-- 用户表：邮箱唯一
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 或者在表定义时直接声明
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT uk_users_username UNIQUE (username);

2. C# 实体类配置 ​

csharp
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

public class User
{
    public Guid Id { get; set; }
    
    [Required]
    [MaxLength(50)]
    public string Username { get; set; }
    
    [Required]
    [MaxLength(100)]
    [EmailAddress]
    public string Email { get; set; }
    
    [Required]
    public string PasswordHash { get; set; }
    
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) { }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置唯一索引
        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email)
            .IsUnique()
            .HasDatabaseName("idx_users_email");
        
        modelBuilder.Entity<User>()
            .HasIndex(u => u.Username)
            .IsUnique()
            .HasDatabaseName("idx_users_username");
        
        // 复合唯一索引：同一用户不能有相同的订单号
        modelBuilder.Entity<Order>()
            .HasIndex(o => new { o.UserId, o.OrderNumber })
            .IsUnique()
            .HasDatabaseName("idx_orders_user_ordernumber");
    }
}

3. 处理唯一约束冲突 ​

方式 1：捕获异常（推荐） ​

csharp
public class UserService
{
    private readonly AppDbContext _dbContext;
    private readonly ILogger<UserService> _logger;
    
    public UserService(AppDbContext dbContext, ILogger<UserService> logger)
    {
        _dbContext = dbContext;
        _logger = logger;
    }
    
    public async Task<Result<User>> RegisterUserAsync(RegisterRequest request)
    {
        var user = new User
        {
            Username = request.Username,
            Email = request.Email.ToLowerInvariant(), // 统一小写
            PasswordHash = BCrypt.Net.BCrypt.HashPassword(request.Password),
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow
        };
        
        try
        {
            _dbContext.Users.Add(user);
            await _dbContext.SaveChangesAsync();
            
            _logger.LogInformation("用户注册成功: {Email}", request.Email);
            return Result.Success(user);
        }
        catch (DbUpdateException ex) when (IsUniqueViolation(ex))
        {
            // 解析是哪个字段违反了唯一约束
            var constraintName = ExtractConstraintName(ex);
            
            if (constraintName.Contains("email"))
            {
                _logger.LogWarning("邮箱已被注册: {Email}", request.Email);
                return Result.Failure<User>("Email already registered");
            }
            else if (constraintName.Contains("username"))
            {
                _logger.LogWarning("用户名已被使用: {Username}", request.Username);
                return Result.Failure<User>("Username already taken");
            }
            
            _logger.LogError(ex, "唯一约束冲突: {Constraint}", constraintName);
            return Result.Failure<User>("Registration failed");
        }
    }
    
    private bool IsUniqueViolation(DbUpdateException ex)
    {
        // PostgreSQL 的唯一约束违反错误码是 23505
        return ex.InnerException is PostgresException pgEx 
            && pgEx.SqlState == "23505";
    }
    
    private string ExtractConstraintName(DbUpdateException ex)
    {
        if (ex.InnerException is PostgresException pgEx)
        {
            return pgEx.ConstraintName ?? "";
        }
        return "";
    }
}

// 结果包装类
public class Result<T>
{
    public bool IsSuccess { get; }
    public T Data { get; }
    public string Error { get; }
    
    private Result(bool isSuccess, T data, string error)
    {
        IsSuccess = isSuccess;
        Data = data;
        Error = error;
    }
    
    public static Result<T> Success(T data) => new(true, data, null);
    public static Result<T> Failure(string error) => new(false, default, error);
}

方式 2：先查询后插入（不推荐，有竞态条件） ​

csharp
// ❌ 不推荐：存在竞态条件
public async Task<User> RegisterUserUnsafe(RegisterRequest request)
{
    // 检查是否已存在
    var existing = await _dbContext.Users
        .FirstOrDefaultAsync(u => u.Email == request.Email);
    
    if (existing != null)
    {
        throw new InvalidOperationException("Email already registered");
    }
    
    // 创建新用户
    var user = new User { /* ... */ };
    _dbContext.Users.Add(user);
    await _dbContext.SaveChangesAsync();
    
    return user;
}

// 问题：两个并发请求可能都通过检查，然后都尝试插入
// 时间线：
// T1: 请求A检查邮箱 -> 不存在
// T2: 请求B检查邮箱 -> 不存在
// T3: 请求A插入 -> 成功
// T4: 请求B插入 -> 失败（唯一约束冲突）

方式 3：UPSERT（PostgreSQL 特有） ​

sql
-- PostgreSQL 的 INSERT ... ON CONFLICT 语法
INSERT INTO users (id, username, email, password_hash, created_at, updated_at)
VALUES (@id, @username, @email, @passwordHash, @createdAt, @updatedAt)
ON CONFLICT (email) 
DO NOTHING; -- 如果邮箱已存在，什么都不做

-- 或者更新已有记录
INSERT INTO users (id, username, email, password_hash, created_at, updated_at)
VALUES (@id, @username, @email, @passwordHash, @createdAt, @updatedAt)
ON CONFLICT (email) 
DO UPDATE SET 
    username = EXCLUDED.username,
    updated_at = NOW();
csharp
public async Task UpsertUserAsync(User user)
{
    await using var command = _dbContext.Database.GetDbConnection().CreateCommand();
    
    command.CommandText = @"
        INSERT INTO users (id, username, email, password_hash, created_at, updated_at)
        VALUES (@id, @username, @email, @passwordHash, @createdAt, @updatedAt)
        ON CONFLICT (email) 
        DO UPDATE SET 
            username = EXCLUDED.username,
            updated_at = NOW()";
    
    command.Parameters.AddWithValue("@id", user.Id);
    command.Parameters.AddWithValue("@username", user.Username);
    command.Parameters.AddWithValue("@email", user.Email);
    command.Parameters.AddWithValue("@passwordHash", user.PasswordHash);
    command.Parameters.AddWithValue("@createdAt", user.CreatedAt);
    command.Parameters.AddWithValue("@updatedAt", user.UpdatedAt);
    
    await _dbContext.Database.OpenConnectionAsync();
    await command.ExecuteNonQueryAsync();
}

实际应用场景 ​

场景 1：订单系统防止重复下单 ​

sql
-- 订单表：同一用户在同一时间点不能有相同的订单
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    order_number VARCHAR(50) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    idempotency_key VARCHAR(64) UNIQUE, -- 幂等键唯一
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 复合唯一索引：用户的订单号唯一
CREATE UNIQUE INDEX idx_orders_user_order_number 
ON orders(user_id, order_number);

-- 幂等键唯一索引
CREATE UNIQUE INDEX idx_orders_idempotency_key 
ON orders(idempotency_key);
csharp
public class OrderService
{
    private readonly AppDbContext _dbContext;
    
    public OrderService(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    
    /// <summary>
    /// 创建订单（带幂等性保证）
    /// </summary>
    public async Task<Result<Order>> CreateOrderAsync(
        CreateOrderRequest request, 
        string idempotencyKey)
    {
        // 生成订单号
        var orderNumber = GenerateOrderNumber();
        
        var order = new Order
        {
            UserId = request.UserId,
            OrderNumber = orderNumber,
            TotalAmount = request.TotalAmount,
            Status = "pending",
            IdempotencyKey = idempotencyKey,
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow
        };
        
        try
        {
            _dbContext.Orders.Add(order);
            await _dbContext.SaveChangesAsync();
            
            return Result<Order>.Success(order);
        }
        catch (DbUpdateException ex) when (IsUniqueViolation(ex))
        {
            // 检查是哪种唯一约束冲突
            var constraintName = ExtractConstraintName(ex);
            
            if (constraintName.Contains("idempotency_key"))
            {
                // 幂等键已存在，返回已有订单
                var existingOrder = await _dbContext.Orders
                    .FirstOrDefaultAsync(o => o.IdempotencyKey == idempotencyKey);
                
                if (existingOrder != null)
                {
                    return Result<Order>.Success(existingOrder);
                }
            }
            
            return Result<Order>.Failure("Failed to create order");
        }
    }
    
    private string GenerateOrderNumber()
    {
        // 生成格式：ORD-YYYYMMDD-XXXXX
        var date = DateTime.UtcNow.ToString("yyyyMMdd");
        var random = new Random().Next(10000, 99999);
        return $"ORD-{date}-{random}";
    }
}

场景 2：订阅系统防止重复订阅 ​

sql
-- 订阅表：用户不能重复订阅同一服务
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    service_id UUID NOT NULL REFERENCES services(id),
    plan_type VARCHAR(20) NOT NULL,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 复合唯一索引：用户和服务的组合唯一（仅针对活跃订阅）
CREATE UNIQUE INDEX idx_subscriptions_user_service_active
ON subscriptions(user_id, service_id)
WHERE status = 'active'; -- 部分唯一索引（PostgreSQL 特性）
csharp
public class SubscriptionService
{
    private readonly AppDbContext _dbContext;
    
    public async Task<Result<Subscription>> SubscribeAsync(
        Guid userId, 
        Guid serviceId, 
        string planType)
    {
        var subscription = new Subscription
        {
            UserId = userId,
            ServiceId = serviceId,
            PlanType = planType,
            StartedAt = DateTime.UtcNow,
            ExpiresAt = DateTime.UtcNow.AddMonths(1),
            Status = "active",
            CreatedAt = DateTime.UtcNow
        };
        
        try
        {
            _dbContext.Subscriptions.Add(subscription);
            await _dbContext.SaveChangesAsync();
            
            return Result<Subscription>.Success(subscription);
        }
        catch (DbUpdateException ex) when (IsUniqueViolation(ex))
        {
            // 用户已经订阅了该服务
            var existingSubscription = await _dbContext.Subscriptions
                .FirstOrDefaultAsync(s => 
                    s.UserId == userId && 
                    s.ServiceId == serviceId && 
                    s.Status == "active");
            
            if (existingSubscription != null)
            {
                return Result<Subscription>.Failure("Already subscribed to this service");
            }
            
            return Result<Subscription>.Failure("Subscription failed");
        }
    }
}

场景 3：投票系统防止重复投票 ​

sql
-- 投票表：用户对每个问题只能投一票
CREATE TABLE votes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    question_id UUID NOT NULL REFERENCES questions(id),
    option_id UUID NOT NULL REFERENCES options(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 确保用户和问题组合唯一
    CONSTRAINT uk_votes_user_question UNIQUE (user_id, question_id)
);
csharp
public class VotingService
{
    private readonly AppDbContext _dbContext;
    
    public async Task<Result<Vote>> CastVoteAsync(
        Guid userId, 
        Guid questionId, 
        Guid optionId)
    {
        var vote = new Vote
        {
            UserId = userId,
            QuestionId = questionId,
            OptionId = optionId,
            CreatedAt = DateTime.UtcNow
        };
        
        try
        {
            _dbContext.Votes.Add(vote);
            await _dbContext.SaveChangesAsync();
            
            // 更新问题的投票计数
            await UpdateVoteCountAsync(questionId);
            
            return Result<Vote>.Success(vote);
        }
        catch (DbUpdateException ex) when (IsUniqueViolation(ex))
        {
            return Result<Vote>.Failure("You have already voted on this question");
        }
    }
    
    private async Task UpdateVoteCountAsync(Guid questionId)
    {
        await _dbContext.Database.ExecuteSqlRawAsync(
            "UPDATE questions SET vote_count = vote_count + 1 WHERE id = {0}",
            questionId);
    }
}

高级用法 ​

1. 部分唯一索引（Partial Unique Index） ​

PostgreSQL 支持条件唯一索引，只在满足特定条件时强制执行唯一性：

sql
-- 只保证活跃用户的邮箱唯一
CREATE UNIQUE INDEX idx_users_email_active 
ON users(email) 
WHERE is_active = true;

-- 允许同一个邮箱有多个账户，但只有一个可以是活跃的

2. 表达式唯一索引 ​

sql
-- 邮箱大小写不敏感的唯一索引
CREATE UNIQUE INDEX idx_users_email_lower 
ON users(LOWER(email));

-- 使用时需要转换为小写
INSERT INTO users (email) VALUES ('User@Example.COM');
INSERT INTO users (email) VALUES ('user@example.com'); -- 冲突！
csharp
// C# 中使用时统一转换
user.Email = request.Email.ToLowerInvariant();

3. 多列唯一索引 ​

sql
-- 确保同一房间在同一时间段只有一个预约
CREATE TABLE room_bookings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL,
    user_id UUID NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(20) DEFAULT 'confirmed'
);

-- 复合唯一索引：房间 + 开始时间
CREATE UNIQUE INDEX idx_room_bookings_unique_slot
ON room_bookings(room_id, start_time)
WHERE status != 'cancelled';

性能优化 ​

1. 索引监控 ​

sql
-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE 'idx_users_%';

-- 查看索引大小
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname::text)) AS index_size
FROM pg_indexes
WHERE tablename = 'users';

2. 避免过多唯一索引 ​

csharp
// ❌ 不推荐：过多的唯一索引会影响写入性能
modelBuilder.Entity<User>()
    .HasIndex(u => u.Email).IsUnique();
modelBuilder.Entity<User>()
    .HasIndex(u => u.Phone).IsUnique();
modelBuilder.Entity<User>()
    .HasIndex(u => u.IdCard).IsUnique();
modelBuilder.Entity<User>()
    .HasIndex(u => u.Passport).IsUnique();

// ✅ 推荐：只对真正需要唯一的字段建立索引
modelBuilder.Entity<User>()
    .HasIndex(u => u.Email).IsUnique(); // 登录用

最佳实践总结 ​

1. 选择合适的字段 ​

✅ 适合建立唯一索引的字段：

  • 邮箱地址
  • 用户名
  • 身份证号
  • 手机号
  • 订单号
  • 幂等键

❌ 不适合建立唯一索引的字段：

  • 频繁更新的字段
  • 高基数字段（如时间戳）
  • 非关键业务字段

2. 错误处理 ​

csharp
try
{
    await _dbContext.SaveChangesAsync();
}
catch (DbUpdateException ex) when (ex.InnerException is PostgresException pgEx && pgEx.SqlState == "23505")
{
    // 明确告知用户哪个字段重复了
    var field = pgEx.ConstraintName?.Replace("uk_", "").Replace("idx_", "");
    return Result.Failure($"The {field} is already in use");
}

3. 事务控制 ​

csharp
using var transaction = await _dbContext.Database.BeginTransactionAsync();
try
{
    // 多个相关操作
    await CreateOrderAsync(order);
    await DeductInventoryAsync(order.Items);
    await CreatePaymentAsync(order);
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

总结 ​

唯一索引是实现幂等性的最简单、最可靠的方式：

✅ 优点：

  • 数据库层面保证，绝对可靠
  • 实现简单，无需额外代码
  • 支持高并发，无竞态条件
  • 自动回滚，数据一致性好

⚠️ 注意事项：

  • 需要合理设计索引策略
  • 正确处理唯一约束冲突异常
  • 避免过多唯一索引影响性能
  • 考虑使用 UPSERT 优化体验

在实际应用中，唯一索引常与其他幂等性方案（如 Token 机制、分布式锁）结合使用，形成多层防护。

Released under the MIT License.

Copyright © 2026 Jarod Chen