Isolation level is the primary ACID performance knob:

• READ UNCOMMITTED — no locks on reads. Fastest. Dirty data. Avoid for anything critical.

• READ COMMITTED — short read locks. Best balance for OLTP. Use as your default.

• REPEATABLE READ — holds read locks until commit. Good for reports that need a stable snapshot.

• SERIALIZABLE — predicate locks on ranges. Use for inventory reservation, seat booking, financial totals.

Long transactions are the main ACID performance problem:

• They hold locks longer, blocking concurrent writers

• They generate more WAL, increasing I/O and recovery time

• They delay MVCC garbage collection, causing table bloat in PostgreSQL

Practical rules:

• Keep transaction duration under 100ms for OLTP workloads

• Batch large writes into smaller transactions with checkpoints

• Never hold a transaction open across a network call or user interaction

• Index FK columns — FK checks on INSERT/DELETE scan the child table without an index

Higher isolation = more lock contention = lower throughput — choose the minimum level that is correct

MVCC (PostgreSQL, Oracle, InnoDB) allows reads and writes to proceed concurrently — reads do not block writes

Use SELECT FOR UPDATE only when you will definitely write the row — unnecessary locking kills throughput

Batch large bulk inserts into chunks of 1,000–10,000 rows inside their own transactions to limit WAL pressure

Run VACUUM ANALYZE (PostgreSQL) regularly — MVCC accumulates dead row versions that slow queries without it

Avoid SELECT * inside transactions — wide row scans hold locks or consume snapshot memory longer than necessary

Issuing two related UPDATEs without a transaction — partial failure leaves data corrupt

Forgetting to COMMIT or ROLLBACK — open transactions hold locks and block all concurrent writers indefinitely

Using READ UNCOMMITTED for any data that informs a business decision — dirty reads return data that may roll back

Using MyISAM in MySQL — it has no transactions, no ACID, and no crash recovery; always use InnoDB

Long transactions during peak traffic — they accumulate locks and cause cascading wait chains

Ignoring deadlock errors — if your application crashes instead of retrying, you have a bug, not a database problem