Long transaction holding the counter lock
This runbook helps you when every append waits.
Appends take turns on one counter row, from the counter update to the commit. A transaction you own, through UseTransaction or UseDbContext, holds the counter from its append until you commit. If it stays open, every other append waits.
Deedbox itself holds the counter for longer than one append only in a forced cut-over of an inline projection. That hold lasts at most 2 seconds, and log event 25 names the projection. If it repeats, see step 4 of the stalled projection runbook.
Symptoms
Section titled “Symptoms”deedbox.counter.durationanddeedbox.append.durationrise together.- Appends time out, while the database is otherwise idle.
-
Find the transaction that holds the lock.
Postgres:
SELECT pid, now() - xact_start AS open_for, state, queryFROM pg_stat_activityWHERE pid IN (SELECT pid FROM pg_locks l JOIN pg_class c ON c.oid = l.relation WHERE c.relname = 'position' AND l.granted);SQL Server:
SELECT s.session_id, t.transaction_begin_time, s.program_nameFROM sys.dm_tran_locks lJOIN sys.dm_exec_sessions s ON s.session_id = l.request_session_idJOIN sys.dm_tran_active_transactions t ON t.transaction_id = s.transaction_idWHERE l.resource_associated_entity_id = OBJECT_ID('deedbox.position') AND l.request_status = 'GRANT'; -
End it from its app if you can. Otherwise, end the session:
pg_terminate_backend(pid)orKILL <session_id>. Its append rolls back; no position is lost. -
Fix the code: commit soon after an append, and keep slow work, such as HTTP calls, outside the transaction.