Parsa-29/loop-config / skills/supabase-postgres-best-practices
supabase-postgres-best-practices
VerifiedPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
references/lock-deadlock-prevention.md
View on GitHub---
title: Prevent Deadlocks with Consistent Lock Ordering
impact: MEDIUM-HIGH
impactDescription: Eliminate deadlock errors, improve reliability
tags: deadlocks, locking, transactions, ordering
---
## Prevent Deadlocks with Consistent Lock Ordering
Deadlocks occur when transactions lock resources in different orders. Always
acquire locks in a consistent order.
**Incorrect (inconsistent lock ordering):**
```sql
-- Transaction A -- Transaction B
begin; begin;
update accounts update accounts
set balance = balance - 100 set balance = balance - 50
where id = 1; where id = 2; -- B locks row 2
update accounts update accounts
set balance = balance + 100 set balance = balance + 50
where id = 2; -- A waits for B where id = 1; -- B waits for A
-- DEADLOCK! Both waiting for each other
```
**Correct (lock rows in consistent order first):**
```sql
-- Explicitly acquire locks in ID order before updating
begin;
select * from accounts where id in (1, 2) order by id for update;
-- Now perform updates in any order - locks already held
update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;
```
Alternative: use a single statement to update atomically:
```sql
-- Single statement acquires all locks atomically
begin;
update accounts
set balance = balance + case id
when 1 then -100
when 2 then 100
end
where id in (1, 2);
commit;
```
Detect deadlocks in logs:
```sql
-- Check for recent deadlocks
select * from pg_stat_database where deadlocks > 0;
-- Enable deadlock logging
set log_lock_waits = on;
set deadlock_timeout = '1s';
```
Reference:
[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |