Transactions, Concurrency and Indexing
Transactions and ACID
A transaction is a logical unit of work.
transfer.sql
BEGIN;
UPDATE account SET balance = balance - 500 WHERE id = 1;
UPDATE account SET balance = balance + 500 WHERE id = 2;
COMMIT; -- or ROLLBACK on error
| Property | Meaning |
|---|---|
| Atomicity | All or nothing |
| Consistency | Moves the database from one valid state to another |
| Isolation | Concurrent transactions do not see each other's partial work |
| Durability | Committed changes survive crashes (write-ahead log) |
Concurrency problems
| Problem | Description |
|---|---|
| Dirty read | Reading uncommitted data |
| Non-repeatable read | The same row read twice returns different values |
| Phantom read | A repeated query returns new rows |
| Lost update | Two writers overwrite each other |
Isolation levels (SQL standard)
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | No | Possible | Possible |
| Repeatable Read | No | No | Possible |
| Serializable | No | No | No |
Concurrency control
- Locking: shared (read) and exclusive (write) locks; two-phase locking (2PL) guarantees serializability but can deadlock.
- Timestamp ordering and MVCC (multi-version concurrency control): readers see a snapshot and do not block writers (PostgreSQL, MySQL InnoDB).
A schedule is serializable if it is equivalent to some serial order; conflict serializability is tested with a precedence graph (a cycle means not serializable).
Indexing
An index is a separate structure that speeds up lookups at the cost of extra storage and slower writes.
index.sql
CREATE INDEX idx_student_city ON student (city);
EXPLAIN SELECT * FROM student WHERE city = 'Pune'; -- check it is used
| Index type | Notes |
|---|---|
| B+ tree | Default; supports equality and range queries, keeps data sorted |
| Hash | Equality only |
| Clustered | Determines the physical row order (one per table) |
| Non-clustered | Separate structure pointing to rows |
| Composite | Multi-column; follows the left-most prefix rule |
NOTE
Indexes help WHERE, JOIN and ORDER BY columns, but not queries that wrap the column in a function (WHERE LOWER(city) = 'pune') unless a matching functional index exists.
Loading Comments...