Implementing Continuous Invoice Numbers Using a Transactional Watermark Table

When an order number skips from 12 to 14 it is usually harmless, but financial vouchers cannot have missing numbers because auditors will ask where number 13 went. The article solves this by storing three kinds of numbers separately: an internal id (auto‑increment primary key), a sequence_no that records every allocated number, and the final voucher_no shown to users.

Definitions – What a Watermark Table Is

A watermark table works like an old‑style numbering book. Each accounting period occupies one row; the row’s current_no holds the highest allocated sequence for that period. Updating the row and writing the voucher must happen in the same transaction so that a rollback also rolls back the number allocation.

Principle – Transactional UPSERT

MySQL’s INSERT ... ON DUPLICATE KEY UPDATE (UPSERT) is used to either insert a new row for a fresh period (setting current_no = 1) or, if the row already exists, increment current_no by one. The UPSERT holds an exclusive lock on the row, allowing the subsequent SELECT to read the just‑assigned sequence_no without needing FOR UPDATE.

START TRANSACTION;
INSERT INTO voucher_counter (period_code, current_no)
VALUES ('2099-10', 1)
ON DUPLICATE KEY UPDATE current_no = voucher_counter.current_no + 1;
SELECT current_no AS sequence_no FROM voucher_counter WHERE period_code = '2099-10';
INSERT INTO voucher (period_code, sequence_no, voucher_no, status)
VALUES ('2099-10', 1, 'DEMO-209910-000001', 'CONFIRMED');
COMMIT;

The business code then formats sequence_no (e.g., padding, adding prefixes) to produce the final voucher_no.

Full Table Definitions

voucher_counter : period_code VARCHAR(7) NOT NULL (primary key), current_no BIGINT UNSIGNED NOT NULL,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

.

voucher : id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT (primary key), period_code VARCHAR(7) NOT NULL, sequence_no BIGINT UNSIGNED NOT NULL, voucher_no VARCHAR(32) NOT NULL, status VARCHAR(16) NOT NULL, void_reason VARCHAR(255), timestamps, plus unique indexes on voucher_no and on (period_code, sequence_no).

Concurrency – Two Sessions Competing for the Same Number

Session A starts a transaction, inserts (or updates) the watermark row for period 2099-12, and holds the lock. Session B, using a separate connection, attempts the same UPSERT and blocks until A commits or rolls back. If A rolls back, the number is not consumed and B receives the same current_no (e.g., 1). This demonstrates that failed transactions do not waste numbers.

Rollback vs. Void

Rollback occurs before the transaction is committed; the number never becomes visible to other transactions. Void happens after a voucher is already committed – the row stays, its status is changed to VOID, and a void_reason is recorded. The number is never reused because it has been part of an auditable record.

START TRANSACTION;
UPDATE voucher SET status = 'VOID', void_reason = 'Amount entry error'
WHERE voucher_no = 'DEMO-209910-000001' AND status = 'CONFIRMED';
COMMIT;

Scope and Limitations

The approach guarantees strict sequential numbers per period, which is essential for audit‑heavy scenarios such as accounting vouchers, regulatory batch numbers, or archival identifiers. It is unsuitable for high‑throughput per‑second numbering within a single period because all requests contend for the same row lock. It also does not work for multi‑region active‑active setups, as row locks are local to a single primary instance.

Before adopting, verify that auditors will require a trace for every missing number. If gaps are acceptable, a simple auto‑increment primary key is sufficient.

In summary, the workflow is:

periodCode = determinePeriod(date);
validateAmountAndStatus();
START TRANSACTION;
-- UPSERT watermark row
INSERT INTO voucher_counter (period_code, current_no)
VALUES (periodCode, 1)
ON DUPLICATE KEY UPDATE current_no = current_no + 1;
SELECT current_no AS sequence_no FROM voucher_counter WHERE period_code = periodCode;
voucherNo = formatVoucherNo(periodCode, sequence_no);
INSERT INTO voucher (period_code, sequence_no, voucher_no, status)
VALUES (periodCode, sequence_no, voucherNo, 'CONFIRMED');
COMMIT;

If any step fails, the transaction is rolled back, ensuring the number is not consumed. After commit, a later UPDATE can mark the voucher as VOID without altering the sequence.