Concurrent refresh-token rotation forks a session into two live branches

The entire security pitch of refresh-token rotation rests on "exactly one refresh token is ever valid at a time per session." If two requests presenting the same refresh token can both succeed -- which a flaky mobile client silently retrying a slow request, a misbehaving proxy replaying a request, or a genuine attacker racing a legitimate client can all trigger -- the system silently ends up with two live, independent refresh-token chains descending from one login, with no record anywhere that this happened and no alert.

rotate_refresh_token() reads a RefreshToken row, validates it, issues a new child token, then sets row.revoked_at / row.replaced_by and commits -- with no conditional guard tying that final UPDATE to the exact state that was read (no WHERE replaced_by IS NULL, no SELECT ... FOR UPDATE, no optimistic-locking version column). Two concurrent calls that both read the row while it is still unrotated will both pass validation, both create a child token, and both commit.

1. See practice_tickets/tests/test_ticket_13.py for a deterministic reproduction using two real threads synchronized with a threading.Barrier.
2. Both threads call rotate_refresh_token() with the same refresh token.
3. Observe: 2 of the 2 concurrent rotations succeed (expected exactly 1).

This is one of the hardest tickets in this batch for a reason: it is not a wrong comparison operator or a missing line, it is an absence -- the kind of gap that is invisible by inspection because every individual statement in rotate_refresh_token() is correct in isolation. It only becomes a bug in the context of two callers running at once, which is exactly the class of defect that ships to production, passes every existing test, and only shows up under real concurrent load or a retry storm.

Think about what SQLAlchemy primitive would make the final write conditional on the row's state not having changed since it was read. Two standard approaches: (a) a WHERE-guarded UPDATE (e.g. via RefreshToken.query.filter_by(id=row.id, replaced_by=None).update(...)) and checking how many rows it actually affected, or (b) pessimistic locking (with_for_update() on the initial SELECT, inside a transaction). Either is defensible; consider what each costs under SQLite (used in this project's tests) versus Postgres (used in production).