Unit 1: Transaction management

A transaction is a sequence of operations performed as a single logical unit of work (for example transferring ₹500 from account A to B).

Transaction states: active → partially committed → committed, or active → failed → aborted.

A schedule is the order in which operations of concurrent transactions are executed. A serial schedule runs transactions one after another; a concurrent schedule interleaves them.

• A schedule is conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations. Test: draw a precedence graph; if it has no cycle, the schedule is serializable.
• Two operations conflict if they belong to different transactions, access the same item, and at least one is a write.
• A recoverable schedule commits a transaction only after every transaction it read from has committed. A cascadeless schedule reads only committed data, avoiding cascading rollbacks.

Problems of uncontrolled concurrency: lost update, dirty read (reading uncommitted data), unrepeatable read and the phantom problem.

• Shared lock (S): for reading; many transactions can hold it together.
• Exclusive lock (X): for writing; only one transaction can hold it.

Two-phase locking (2PL) guarantees conflict serializability:

Strict 2PL holds exclusive locks until commit, preventing cascading rollbacks. A lock manager keeps a lock table of granted and waiting requests. Locking has an overhead: more locking reduces concurrency and throughput.

A deadlock occurs when transactions wait for each other's locks in a cycle. Methods:

• Prevention: wait-die and wound-wait schemes based on timestamps.
• Detection: build a wait-for graph; a cycle means deadlock; then abort a victim transaction.
• Timeouts: abort a transaction that waits too long.

SQL controls transactions with BEGIN/START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT, and sets isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Crash recovery uses a log of all changes (write-ahead logging: log first, then data). After a crash, the system redoes committed transactions and undoes uncommitted ones. Checkpoints limit how much of the log must be scanned. The ARIES algorithm performs analysis, redo and undo phases.