transaction-concurrency-control-foundations

by benchflow-ai

Install this Skill

curl -fsSL -o transaction-concurrency-control-foundations.zip https://skill.seembox.com/api/v1/skills/transaction-concurrency-control-foundations/download && unzip transaction-concurrency-control-foundations.zip

Download transaction-concurrency-control-foundations.zip

SKILL.md About

---
name: transaction-concurrency-control-foundations
description: Provides database transaction concurrency-control background. Use when reasoning about transactions, commits and aborts, serializability, isolation anomalies, conflicts, dependencies, and protocol families such as locking, timestamp ordering, OCC, MVCC, SSI, TicToc, hybrid protocols.
---

# Transaction Concurrency-Control Foundations

Use this skill when you need *shared vocabulary + decision lenses* for transaction concurrency control before analyzing a specific protocol or trace.

This skill is most useful when you catch yourself asking:

- “What *exactly* is the anomaly / conflict here?”
- “What does the protocol need to prevent, and what metadata would prove it?”
- “Is this abort required for correctness or just conservative?”

## Quick Reference (pick the next file to read)

| If you need… | Read |
|---|---|
| Core terms and what “commit/abort” means in histories | [transaction-concepts.md](references/transaction-concepts.md) |
| A crisp definition of the *bug pattern* (lost update, dirty read, write skew, phantoms) | [concurrency-control-problems.md](references/concurrency-control-problems.md) |
| A guarantee taxonomy (serializable vs SI vs strict serializable; opacity if relevant) | [anomaly-and-isolation-taxonomy.md](references/anomaly-and-isolation-taxonomy.md) |
| Dependency/conflict edge types (rw/wr/ww; anti-deps; visibility) | [conflict-taxonomy.md](references/conflict-taxonomy.md) |
| Where a mechanism fits (locking vs TO vs OCC vs MVCC vs SSI vs hybrid) | [protocol-family-overview.md](references/protocol-family-overview.md) |
| Concrete protocol mini-specs (2PL/TO/OCC/MVCC/SSI/TicToc) | [representative-protocols.md](references/representative-protocols.md) |

## Router: from “protocol cues” to what to read

When you have a protocol description or trace fields but don’t know what family it is, route by cues.

### A. Is the protocol primarily **blocking** or **aborting**?

- **Blocking on conflicts, lock modes (S/X), deadlocks mentioned** → go to **2PL** in [representative-protocols.md](references/representative-protocols.md)
- **Nonblocking execution + commit-time validation** → go to **OCC** (and often TicToc/SSI live here too)

### B. Does it assign a **fixed transaction timestamp** early?

- **Per-txn fixed TS(T), and objects store RTS/WTS** → go to **Basic Timestamp Ordering**
- **Per-txn snapshot/start_ts + multiple versions** → go to **MVCC / Snapshot Isolation**

### C. Do reads choose a version from a **version chain**?

- **Yes** (visibility rule, begin/end timestamps, “as of snapshot”) → go to **MVCC**
- **No, single current version plus per-key metadata** → go to **TO / OCC / TicToc-like**

### D. Do you see **read timestamp extension** or “valid-through” metadata?

- **Fields like rts (read-valid-through), extension checks, or “extend rts if unchanged”** → go to **TicToc**

### E. Do you see **dependency / anti-dependency** tracking?

- **Explicit rw anti-deps, “dangerous structure”, serialization-graph edges** → go to **SSI**

### F. What’s the *proof object*?

- **Locks** prove exclusion → 2PL
- **Per-key RTS/WTS** prove timestamp order → TO
- **Read-set version check** proves stability → OCC
- **Visibility from snapshot + version chain** proves consistent reads → MVCC/SI
- **Anti-dep edges / dangerous structure** prove no cycles → SSI
- **Per-key wts/rts interval + extend-if-unchanged** proves read validity at chosen commit_ts → TicToc

## Minimal Working Vocabulary (keep this tiny)

When analyzing a trace or protocol, keep a running record with these nouns and edges:

```text
transactions: T1, T2, ...
objects/keys: x, y, ...
versions: x@wts=..., x@ver=...
operations: R(x@...), W(x@...), commit(T), abort(T)
dependencies:
  T_i ->wr T_j   (T_j read from T_i's write)
  T_i ->rw T_j   (T_i read a version that T_j later overwrote; anti-dep)
  T_i ->ww T_j   (T_j overwrote T_i on same object)
```

If you can’t name the version read/written, you’re still in “story mode,” not “proof mode.”

## Checklist: classify a protocol decision

Use this checklist before labeling an abort “unnecessary” or a commit “unsafe”:

- **Claimed guarantee**: serializable? strict? SI? opacity?
- **What the transaction read**: object + which version/timestamp evidence.
- **What changed by validation time**: overwrite? timestamp extension? dependency edge?
- **What *order* would make it safe**: a serial order or a timestamp assignment.
- **What evidence is missing**: did the protocol lack history/versions to prove safety?

## Common Pitfalls

- **Mixing scopes**: per-key counters and timestamps often cannot order different keys.
- **Equating “row order” with “happened-before”**: log order can be buffered/merged.
- **Assuming abort == anomaly**: aborts can be conservative proof failures, not unsafe histories.