Transaction Performance:

1. Transaction Duration:

Short transactions (< 1 second): Fast, minimal lock contention, low deadlock risk

Long transactions (> 10 seconds): Slow, high lock contention, high deadlock risk

Measurement: Transaction duration = time from BEGIN to COMMIT

Target: Keep transactions under 1 second (ideally < 100ms)

Example: Stripe processes 1 billion transactions daily with average duration < 50ms

2. Lock Contention:

Lock contention occurs when multiple transactions wait for same locks. Causes:

- Long transactions (hold locks longer)

- Hot rows (many transactions update same rows)

- Table-level locks (lock entire table instead of rows)

Solution:

- Keep transactions short (< 1 second)

- Use row-level locks (not table-level)

- Batch updates outside transactions

- Use optimistic locking (check version before update)

3. Deadlock Frequency:

Deadlock frequency = deadlocks per second

Target: < 1 deadlock per 10,000 transactions (0.01%)

High deadlock frequency indicates:

- Lock ordering issues (acquire locks in different order)

- Long transactions (more time for deadlock to occur)

- High concurrency (many transactions competing for locks)

Solution:

- Implement lock ordering (always lock A before B)

- Retry logic (catch deadlock error, retry transaction)

- Reduce transaction duration

4. Transaction Log Size:

Transaction log records all changes. Large transactions fill log quickly.

Example: Update 1 million rows in single transaction

- Transaction log size: 500MB

- COMMIT time: 5 seconds (flush log to disk)

Solution:

- Batch updates (5000 rows per transaction)

- Transaction log size per batch: 2.5MB

- COMMIT time per batch: 50ms

5. Isolation Level Performance:

Read Uncommitted: Fastest (no locks), dirty reads possible

Read Committed: Fast (default), no dirty reads