A water utility’s meter-insert job and a balance-update procedure repeatedly abort with deadlock errors during peak billing. What should the SQL AI developer focus on?

When the meter-insert job and balance-update procedure abort at peak billing, each session may hold a resource the other needs, creating a lock cycle. Identify the deadlock graph, including the victim, resources, and access order; then align the procedures’ resource access order and shorten transactions so meters and balances are not held across unnecessary work. Consistent ordering and shorter critical sections break the recurring cycle. Raising compatibility level alone changes optimizer behavior and cardinality estimation; it does not eliminate multi-session lock cycles between billing procedures. Replacing both procedures with one anonymous GraphQL mutation and leaving database work untransactional removes ACID guarantees needed for municipal billing and does not prevent concurrent conflicts on shared meter and balance rows. Disabling auditing does not affect deadlock victim selection or prevent cycles; audit overhead is a separate performance concern. Exam caveat: SQL Server and Azure SQL choose a deadlock victim based on cost and deadlock priority, so lowering priority everywhere is not a design fix and may cause the wrong municipal job to fail repeatedly. READ_COMMITTED_SNAPSHOT can reduce some reader/writer blocking, but it does not remove writer/writer deadlocks on the same rows; ordering and transaction scope still matter. Practical check: capture a deadlock graph with system_health, Extended Events, or Azure diagnostics; record the tables, indexes, and access order in each procedure; standardize the order, such as meter then balance; minimize transaction statements; and rerun a peak-billing concurrency test, confirming the deadlock rate approaches zero before declaring success.