Unit 4: Transaction management

• Query optimisation: heuristic rules (perform selections and projections early, replace Cartesian products with joins, choose a good join order) and cost-based estimation (disk block transfers and seeks, using table statistics). EXPLAIN shows the chosen plan.

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.

• Each transaction gets a timestamp; conflicting operations must follow timestamp order. If an older transaction tries to read data already written by a younger one, it is rolled back and restarted. Free from deadlock.

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.
• Multiversion concurrency control (MVCC): keeps several versions of data so readers never block writers — used by PostgreSQL, Oracle and MySQL InnoDB. Validation (optimistic) protocols check for conflicts only at commit.

Security goals: secrecy (only authorised users read data), integrity (only authorised changes) and availability. Discretionary access control (DAC) lets owners grant and revoke privileges:

sqlGRANT SELECT, INSERT ON student TO clerk;
REVOKE INSERT ON student FROM clerk;
GRANT SELECT ON student TO hod WITH GRANT OPTION;

Mandatory access control (MAC) uses system-wide security labels (top secret, secret …); role-based access control grants privileges to roles rather than individuals.

• Threats: unauthorised access, SQL injection, privilege abuse, stolen backups, malware. Measures: authentication, encryption at rest and in transit, views to hide data, auditing, least privilege, statistical database controls.
• SQL injection: attackers insert SQL into inputs; prevent it with prepared statements and least-privilege accounts. Encryption protects data at rest (transparent data encryption) and in transit (TLS); auditing records who did what.