Unit 4: Database protection and distributed databases

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.

• Shadow paging: keeps a shadow page table unchanged during a transaction; on commit the new table replaces it, on failure the shadow is restored — no log needed but causes fragmentation.

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.

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.

• Integrity constraints keep data correct and consistent.

sqlCREATE TABLE result (
  roll   INT REFERENCES student(roll) ON DELETE CASCADE,
  code   VARCHAR(10) REFERENCES course(code),
  marks  INT CHECK (marks BETWEEN 0 AND 100),
  PRIMARY KEY (roll, code)
);

• Control: concurrency control, access control, audit trails and backup policies together keep data controlled.

• Backup types: full (everything), incremental (changes since the last backup), differential (changes since the last full backup); hot backup while the database runs, cold backup when it is shut down.

• Distributed database: a single logical database stored across several sites connected by a network, managed by a distributed DBMS that hides the distribution from users (transparency).

• Advantages: local autonomy, reliability and availability (other sites keep working), faster local access, scalable growth. Disadvantages: complexity, cost, security, harder concurrency and recovery (two-phase commit).

• Two-phase commit (2PC): a coordinator asks all sites to prepare (vote) and then tells all to commit or all to abort, ensuring atomicity across sites.