如何基于ASP.NET Core Web API、EF Core与PostgreSQL构建唯一序号服务？

需求概述 #

需要构建一个序号生成服务，满足以下规则：

• 初始值为1
• 每次调用自增1
• 并发请求下不会出现重复序号
• 序号达到指定阈值时自动重置为1
• 支持手动提前重置为1

技术栈 #

• ASP.NET Core Web API
• Entity Framework Core
• PostgreSQL

示例参考（Python版） #

class Numbering:
    def __init__(self):
        self.number = 0

    def get_number(self):
        self.number += 1
        return self.number

    def reset(self):
        self.number = 0

num = Numbering()

for i in range(10):
    print(num.get_number())

num.reset()
print("number has been reset")

for i in range(10):
    print(num.get_number())

实现步骤 #

1. 定义实体类 #

创建用于持久化计数器状态的实体，存储当前序号和阈值：

public class SequenceCounter
{
    public int Id { get; set; }
    public int CurrentValue { get; set; }
    public int Threshold { get; set; }
    // 若需要多计数器场景，可添加CounterName字段作为唯一标识
}

2. 配置DbContext #

继承DbContext并注册实体，同时配置PostgreSQL连接：

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<SequenceCounter> SequenceCounters { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 初始化默认计数器（阈值可根据需求调整）
        modelBuilder.Entity<SequenceCounter>().HasData(
            new SequenceCounter { Id = 1, CurrentValue = 0, Threshold = 100 }
        );
    }
}

3. 实现并发安全的服务类 #

核心是利用PostgreSQL的行级锁保证并发安全，避免序号重复：

public interface ISequenceService
{
    Task<int> GetNextSequenceAsync();
    Task ResetSequenceAsync();
}

public class SequenceService : ISequenceService
{
    private readonly AppDbContext _dbContext;

    public SequenceService(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<int> GetNextSequenceAsync()
    {
        using var transaction = await _dbContext.Database.BeginTransactionAsync();
        try
        {
            // 使用SELECT ... FOR UPDATE锁定行，防止并发修改
            var counter = await _dbContext.SequenceCounters
                .FromSqlRaw("SELECT * FROM sequencecounters WHERE id = 1 FOR UPDATE")
                .FirstAsync();

            counter.CurrentValue += 1;

            // 达到阈值自动重置
            if (counter.CurrentValue > counter.Threshold)
            {
                counter.CurrentValue = 1;
            }

            await _dbContext.SaveChangesAsync();
            await transaction.CommitAsync();

            return counter.CurrentValue;
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

    public async Task ResetSequenceAsync()
    {
        using var transaction = await _dbContext.Database.BeginTransactionAsync();
        try
        {
            var counter = await _dbContext.SequenceCounters
                .FromSqlRaw("SELECT * FROM sequencecounters WHERE id = 1 FOR UPDATE")
                .FirstAsync();

            counter.CurrentValue = 0; // 下次调用会自增为1

            await _dbContext.SaveChangesAsync();
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

4. 注册服务与配置API #

在Program.cs中注册DbContext和服务：

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddScoped<ISequenceService, SequenceService>();

创建API控制器：

[ApiController]
[Route("api/sequence")]
public class SequenceController : ControllerBase
{
    private readonly ISequenceService _sequenceService;

    public SequenceController(ISequenceService sequenceService)
    {
        _sequenceService = sequenceService;
    }

    [HttpGet("next")]
    public async Task<IActionResult> GetNextSequence()
    {
        var sequence = await _sequenceService.GetNextSequenceAsync();
        return Ok(sequence);
    }

    [HttpPost("reset")]
    public async Task<IActionResult> ResetSequence()
    {
        await _sequenceService.ResetSequenceAsync();
        return Ok("序号已重置");
    }
}

关键说明 #

• 并发安全：通过PostgreSQL的SELECT ... FOR UPDATE实现行级锁，确保同一时间只有一个请求能修改计数器行，彻底避免并发场景下的序号重复问题。
• 持久化：计数器状态存储在数据库中，服务重启后不会丢失当前序号。
• 阈值配置：可通过修改实体初始化的Threshold值，或新增接口动态调整阈值。

内容的提问来源于stack exchange，提问作者Mohammad Zohar