Test a deadlock is handled

Simulate a deadlock victim being rolled back and retried in a consistent lock order, and verify the final stock is correct and the retry was logged.

1. 1T2, first attempt: BEGIN; move 5 units from product 12 to 11 starting with UPDATE products SET stock = stock - 5 WHERE id = 12. Pretend the engine picks T2 as the deadlock victim: ROLLBACK.
2. 2T1: BEGIN; stock - 10 on product 11, stock + 10 on product 12; COMMIT.
3. 3T2 retry in id order: BEGIN; stock + 5 on product 11, stock - 5 on product 12; COMMIT.
4. 4Log the retry: INSERT INTO audit_log (table_name, action, record_id, changed_at) VALUES ('products', 'deadlock_retry', 12, '2025-12-31').

• Products 11 and 12 end at 295 and 505
• audit_log holds exactly one deadlock_retry row for product 12
• The script uses ROLLBACK

• Every run starts from a freshly seeded database, so your script must include its own setup statements.
• The workbench has a single connection, so concurrent sessions are simulated step by step inside one session (snapshots in TEMP tables, SAVEPOINTs as the other transaction).
• SQLite cannot deadlock with one connection; the ROLLBACK stands in for the engine choosing a victim.