Unit 4 of 4 · M.Sc IT Sem 1

Unit 4: Transaction management

Relational Database Management System notes · PTU syllabus (PGCA1904)

4 min read8 topics10 exam questions
On this page
  1. Unit summary
  2. Query processing
  3. Transactions and ACID properties
  4. Concurrency control
  5. Lock-based protocols and two-phase locking
  6. Timestamp ordering
  7. Deadlock handling
  8. Database security
  9. Database recovery
  10. Key terms
  11. Quick revision
  12. Important questions

Unit summary

Behind every query and transaction the DBMS optimises, coordinates, protects and recovers. This unit covers query processing, concurrency control, database security and database recovery.

After this unit you can

  • Explain the steps of query processing and optimisation
  • Explain concurrency control protocols
  • Explain database security mechanisms
  • Explain database recovery techniques

PTU syllabus topics

  • Query processing
  • concurrency control
  • database security
  • database recovery
1

Topic 1

Query processing

ProcessSteps in query processing
  1. 1Parsing and translation

    Check syntax and names; convert SQL to relational algebra

  2. 2Optimisation

    Choose the cheapest evaluation plan using statistics

  3. 3Evaluation

    Execute the plan with algorithms for each operator

  4. 4Result

    Return rows to the user

Key termsQuery evaluation algorithms
Selection
Linear scan, or index scan (B+ tree, hash)
Sorting
External merge sort for large relations
Join
Nested-loop, block nested-loop, indexed nested-loop, merge join, hash join
Pipelining
Pass results between operators without writing to disk
  • 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.

Example

σ marks > 90 (student ⋈ course) is rewritten as (σ marks > 90 (student)) ⋈ course, so far fewer rows are joined.

2

Topic 2

Transactions and ACID properties

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

ClassificationACID properties
Transaction
  • Atomicity

    All operations happen, or none

  • Consistency

    Takes the database from one valid state to another

  • Isolation

    Concurrent transactions don't interfere

  • Durability

    Committed changes survive failures

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

Example

ACID in a transfer: both debit and credit happen or neither (atomicity); total money stays the same (consistency); another user does not see the half-finished transfer (isolation); once committed, it survives a power failure (durability).

Exam tip

Atomicity — recovery manager; isolation — concurrency control; durability — logs and backups.

3

Topic 3

Concurrency control

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.

Example

A lost update: T1 and T2 both read a balance of ₹1,000; T1 adds ₹200 and writes ₹1,200; T2 subtracts ₹100 and writes ₹900. T1's update is lost. Concurrency control produces a serialisable result (₹1,100).

Exam tip

A schedule is serialisable if its result equals some serial order of the transactions.

4

Topic 4

Lock-based protocols and two-phase locking

  • 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:

ProcessTwo-phase locking
  1. 1Growing phase

    Acquire locks, release none

  2. 2Lock point

    All locks held

  3. 3Shrinking phase

    Release locks, acquire none

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.

Example

Two-phase locking: a transaction acquires locks on A and B (growing phase), does its work, then releases them (shrinking phase), never acquiring a new lock after releasing one. This guarantees conflict-serialisable schedules.

Exam tip

Strict 2PL holds exclusive locks until commit, preventing cascading rollbacks.

5

Topic 5

Timestamp ordering

  • 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.

Example

Rejecting a late write: T1 (timestamp 10) wants to write item X, but T2 (timestamp 20) has already read X. Writing now would break timestamp order, so T1 is rolled back and restarted with a new timestamp.

Exam tip

Timestamp ordering avoids deadlocks because transactions never wait for locks.

6

Topic 6

Deadlock handling

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.

Example

Wait-die: an older transaction T1 requesting a lock held by a younger T2 waits; a younger T2 requesting a lock held by an older T1 dies (rolls back) and restarts with its original timestamp.

Exam tip

Methods: prevention (wait-die, wound-wait), detection with a wait-for graph, and timeouts.

7

Topic 7

Database security

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.

Example

SQL injection: a login query built by joining strings can be fooled by entering ' OR '1'='1 as the password. Using parameterised queries and limited database privileges blocks the attack.

Exam tip

Security measures: authentication, authorisation, views, encryption, auditing and input validation.

8

Topic 8

Database recovery

Key termsTypes of failure
Transaction failure
Logical error (bad input) or system error (deadlock) aborts one transaction
System crash
Power or software failure loses main memory, disk survives
Disk failure
Head crash destroys stored data
Catastrophe
Fire, flood or earthquake destroys the site
ProcessARIES recovery
  1. 1Analysis

    Scan the log from the last checkpoint; find dirty pages and active transactions

  2. 2Redo

    Repeat history — reapply all logged updates from the earliest needed point

  3. 3Undo

    Roll back transactions that had not committed, writing compensation log records

Example

Recovery after a crash: the log shows T1 committed and T2 did not. On restart, the system redoes T1's changes (to ensure durability) and undoes T2's changes (to ensure atomicity).

Exam tip

Write-ahead logging: write the log record to stable storage before the data change.

Key terms

Query optimisation
Choosing the most efficient evaluation plan
Serializability
Concurrent schedule equivalent to a serial one
Two-phase locking
Growing phase then shrinking phase of locks
MVCC
Concurrency control keeping multiple data versions
Write-ahead logging
Log written before data changes reach disk

Quick revision

  • Parsing, optimisation, evaluation; selection, sort and join algorithms; heuristics and cost.
  • ACID; conflict and view serializability; recoverable and cascadeless schedules.
  • 2PL, strict 2PL; timestamp ordering; deadlocks; MVCC; validation.
  • DAC, MAC, RBAC; GRANT, REVOKE; SQL injection; encryption; auditing.
  • Failures; log-based recovery; deferred and immediate update; checkpoints; shadow paging; ARIES.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Name the steps in query processing.
  2. Q2.State two heuristic optimisation rules.
  3. Q3.What is conflict serializability?
  4. Q4.Distinguish 2PL and strict 2PL.
  5. Q5.What is MVCC?
  6. Q6.What is a checkpoint?

Long-answer questions

  1. Q1.Explain query processing and optimisation.
  2. Q2.Explain lock-based and timestamp-based concurrency control.
  3. Q3.Explain database security mechanisms.
  4. Q4.Explain log-based recovery and the ARIES algorithm.

Stuck on this unit?

Message SBS on WhatsApp for help with Relational Database Management System, or to ask about studying M.Sc IT at Synetic.

Planning your M.Sc IT in Ludhiana? See the M.Sc IT at SBS →

WhatsApp us