57 lines
2.0 KiB
Python
57 lines
2.0 KiB
Python
|
"""Atomic NCR number allocation.
|
||
|
|
|
||
|
|
Format: NCR-YYYY-NNNN (zero-padded, per-calendar-year sequence).
|
||
|
|
|
||
|
|
Strategy: UPDATE-first on the per-year row in ncr_sequences. The UPDATE takes
|
||
|
|
a row lock (InnoDB) / reserved write lock (SQLite) that is held until the
|
||
|
|
enclosing transaction commits, so two concurrent submissions serialize and can
|
||
|
|
never read the same sequence value. If the year row doesn't exist yet
|
||
|
|
(first NCR of a new year), it is inserted inside a SAVEPOINT; a losing racer
|
||
|
|
gets an IntegrityError, rolls back only the savepoint, and proceeds to the
|
||
|
|
UPDATE which now finds the winner's row.
|
||
|
|
"""
|
||
|
|
from datetime import datetime
|
||
|
|
|
||
|
|
from sqlalchemy import select, update
|
||
|
|
from sqlalchemy.exc import IntegrityError
|
||
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
||
|
|
|
||
|
|
from app.models import NcrSequence
|
||
|
|
from app.models.base import utcnow
|
||
|
|
|
||
|
|
|
||
|
|
async def allocate_ncr_number(
|
||
|
|
db: AsyncSession, now: datetime | None = None
|
||
|
|
) -> tuple[str, int, int]:
|
||
|
|
"""Allocate the next NCR number inside the caller's transaction.
|
||
|
|
|
||
|
|
Returns (ncr_number, year, seq). Must be called within the same
|
||
|
|
transaction that inserts the NCR so the sequence row lock is held
|
||
|
|
until commit.
|
||
|
|
"""
|
||
|
|
year = (now or utcnow()).year
|
||
|
|
|
||
|
|
result = await db.execute(
|
||
|
|
update(NcrSequence)
|
||
|
|
.where(NcrSequence.year == year)
|
||
|
|
.values(last_seq=NcrSequence.last_seq + 1)
|
||
|
|
)
|
||
|
|
if result.rowcount == 0:
|
||
|
|
# First NCR of this calendar year — create the sequence row.
|
||
|
|
try:
|
||
|
|
async with db.begin_nested():
|
||
|
|
db.add(NcrSequence(year=year, last_seq=0))
|
||
|
|
await db.flush()
|
||
|
|
except IntegrityError:
|
||
|
|
pass # another request created it first; fall through to UPDATE
|
||
|
|
await db.execute(
|
||
|
|
update(NcrSequence)
|
||
|
|
.where(NcrSequence.year == year)
|
||
|
|
.values(last_seq=NcrSequence.last_seq + 1)
|
||
|
|
)
|
||
|
|
|
||
|
|
seq = (
|
||
|
|
await db.execute(select(NcrSequence.last_seq).where(NcrSequence.year == year))
|
||
|
|
).scalar_one()
|
||
|
|
return f"NCR-{year}-{seq:04d}", year, seq
|