每年重置发票编号，并发场景下发票号生成逻辑及锁实现咨询

Hey there! Let's work through this invoice numbering problem step by step—fixing that concurrency duplicate issue and making sure the annual reset works smoothly.

First, let's align on the core requirements: invoice numbers follow the format [sequence]/[academic year], resetting the sequence every year, and no duplicates even with concurrent requests. The key here is maintaining per-academic-year auto-increment sequences with concurrency safeguards.

1. Database Table Design #

Start with a dedicated table to track the current maximum invoice number for each academic year. Let's call it invoice_sequence:

CREATE TABLE invoice_sequence (
    academic_year VARCHAR(7) PRIMARY KEY, -- e.g., '18-19', '24-25'
    current_number INT NOT NULL DEFAULT 0
);

This table acts as a single source of truth—each row maps to one academic year and holds the last used invoice number for that period.

2. Concurrency-Safe Sequence Generation (Lock Mechanisms) #

The SELECT count() approach fails because it's a read-only operation that doesn't block concurrent requests. We need atomic operations or locks to ensure only one request can generate a new number at a time.

Option 1: Database Atomic Updates (Recommended) #

Leverage your database's built-in atomic operations to eliminate race conditions. This is the simplest and most reliable approach.

For PostgreSQL, use UPDATE ... RETURNING with a fallback insert for new academic years:

WITH updated AS (
    UPDATE invoice_sequence
    SET current_number = current_number + 1
    WHERE academic_year = '24-25'
    RETURNING current_number
)
INSERT INTO invoice_sequence (academic_year, current_number)
SELECT '24-25', 1
WHERE NOT EXISTS (SELECT 1 FROM updated)
RETURNING current_number;

This runs as a single atomic operation—no matter how many concurrent requests hit it, the database ensures only one gets a unique new number.

For MySQL, use INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO invoice_sequence (academic_year, current_number)
VALUES ('24-25', 1)
ON DUPLICATE KEY UPDATE current_number = current_number + 1;

-- Fetch the updated number immediately after
SELECT current_number FROM invoice_sequence WHERE academic_year = '24-25';

This also guarantees atomicity—MySQL handles the lock internally to prevent duplicates.

Option 2: Pessimistic Locking (For Complex Workflows) #

If you need to run business logic before generating the invoice number, use a pessimistic lock to block other requests until your transaction completes:

BEGIN TRANSACTION;

-- Lock the row for this academic year; other requests wait until we commit
SELECT current_number FROM invoice_sequence WHERE academic_year = '24-25' FOR UPDATE;

-- Run your custom business checks here (e.g., verify user permissions)

-- Increment the sequence
UPDATE invoice_sequence SET current_number = current_number + 1 WHERE academic_year = '24-25';

-- Get the new number
SELECT current_number FROM invoice_sequence WHERE academic_year = '24-25';

COMMIT;

The FOR UPDATE clause adds an exclusive lock to the row, ensuring no other transaction can modify it until yours finishes.

Option 3: Optimistic Locking (For Low-Concurrency Scenarios) #

If concurrent requests are rare, use an optimistic lock with a version number to avoid blocking:
First, add a version column to your table:

ALTER TABLE invoice_sequence ADD COLUMN version INT NOT NULL DEFAULT 0;

Then, update only if the version hasn't changed since you read it:

UPDATE invoice_sequence
SET current_number = current_number + 1, version = version + 1
WHERE academic_year = '24-25' AND version = [your_read_version];

If the update affects 0 rows, another request modified the sequence first—just retry the read-and-update cycle.

3. Final Invoice Number Formatting #

Once you have the new current_number, simply concatenate it with the academic year:

// Example in Java
String invoiceNumber = String.format("%d/%s", newSequenceNumber, academicYear);

Here are the core concepts to master for safe concurrency handling, no external links needed:

• Atomic Operations First: Always prefer database-native atomic updates over manual locks—databases are optimized for this, and you avoid reinventing the wheel.
• Pessimistic vs. Optimistic Locking:
  • Pessimistic: Assume conflicts will happen, lock resources upfront. Great for high-concurrency, complex workflows but watch out for long-held locks hurting performance.
  • Optimistic: Assume conflicts are rare, use version checks. Better for low-concurrency scenarios with faster throughput, but requires retry logic.
• Transaction Isolation Levels: Understand your database's default isolation level (e.g., MySQL's Repeatable Read, PostgreSQL's Read Committed) as it impacts how locks behave and data consistency is maintained.
• Deadlock Prevention: If using pessimistic locks, always acquire locks in the same order (e.g., sort academic years alphabetically before locking) to avoid circular wait deadlocks.
• Distributed Systems: For multi-database setups, use distributed locks (e.g., Redis RedLock, ZooKeeper-based locks) to coordinate sequence generation across instances.

• Ditch SELECT count() entirely—it's never safe for generating unique sequences in concurrent environments.
• Pre-populate new academic year rows: Automatically insert a new row with current_number = 0 when a new academic year starts, or let the atomic insert handle it (like in Option 1).
• Test concurrency: Use tools like JMeter or Postman to send 100+ simultaneous requests and verify no duplicate invoice numbers are generated.

内容的提问来源于stack exchange，提问作者Debabrata Roy