51 lines
1.3 KiB
Markdown
51 lines
1.3 KiB
Markdown
---
|
|
title: Keep Transactions Short to Reduce Lock Contention
|
|
impact: MEDIUM-HIGH
|
|
impactDescription: 3-5x throughput improvement, fewer deadlocks
|
|
tags: transactions, locking, contention, performance
|
|
---
|
|
|
|
## Keep Transactions Short to Reduce Lock Contention
|
|
|
|
Long-running transactions hold locks that block other queries. Keep transactions as short as possible.
|
|
|
|
**Incorrect (long transaction with external calls):**
|
|
|
|
```sql
|
|
begin;
|
|
select * from orders where id = 1 for update; -- Lock acquired
|
|
|
|
-- Application makes HTTP call to payment API (2-5 seconds)
|
|
-- Other queries on this row are blocked!
|
|
|
|
update orders set status = 'paid' where id = 1;
|
|
commit; -- Lock held for entire duration
|
|
```
|
|
|
|
**Correct (minimal transaction scope):**
|
|
|
|
```sql
|
|
-- Validate data and call APIs outside transaction
|
|
-- Application: response = await paymentAPI.charge(...)
|
|
|
|
-- Only hold lock for the actual update
|
|
begin;
|
|
update orders
|
|
set status = 'paid', payment_id = $1
|
|
where id = $2 and status = 'pending'
|
|
returning *;
|
|
commit; -- Lock held for milliseconds
|
|
```
|
|
|
|
Use `statement_timeout` to prevent runaway transactions:
|
|
|
|
```sql
|
|
-- Abort queries running longer than 30 seconds
|
|
set statement_timeout = '30s';
|
|
|
|
-- Or per-session
|
|
set local statement_timeout = '5s';
|
|
```
|
|
|
|
Reference: [Transaction Management](https://www.postgresql.org/docs/current/tutorial-transactions.html)
|