Transaction isolation levels, locking, and the ACID properties, each explained with a short example.
29 cards · basic cards · AI-written, checked twice. Edit anything.
- What does ACID stand for in database transactions?
- Atomicity, Consistency, Isolation, Durability
- Explain Atomicity in ACID.
- A transaction either completes entirely or not at all; partial changes never persist.
- Explain Consistency in ACID.
- The database moves from one valid state to another; all constraints and rules are maintained.
- Explain Isolation in ACID.
- Concurrent transactions do not interfere with each other; each operates as if alone.
- Explain Durability in ACID.
- Once committed, a transaction's changes persist permanently, even after failure.
- What is a transaction in a relational database?
- A sequence of SQL operations treated as a single unit, either all executed or all rolled back.
- What is a dirty read?
- Reading uncommitted data from another transaction that may later be rolled back.
- What is a non-repeatable read?
- Reading the same data twice in one transaction yields different values because another transaction modified and committed it.
- What is a phantom read?
- A query returns different sets of rows on repeated execution because another transaction inserted or deleted rows.
- Which isolation level allows dirty reads?
- Read Uncommitted
- What anomalies does Read Committed prevent?
- Dirty reads. Does not prevent non-repeatable reads or phantom reads.
- What anomalies does Repeatable Read prevent?
- Dirty reads and non-repeatable reads. Does not prevent phantom reads.
- What anomalies does Serializable prevent?
- All three: dirty reads, non-repeatable reads, and phantom reads.
- What is a shared lock?
- A lock that allows multiple transactions to read the same data simultaneously, but prevents writing.
- What is an exclusive lock?
- A lock that allows only one transaction to access data; prevents both reading and writing by other transactions.