Unit-4

Unit-4. Transaction Processing and Database Security.

ACID Properties in DBMS. Transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability even in case of failures / errors. This is where the ACID properties come into play. ACID stands for Atomicity, Consistency, Isolation, and Durability..

(ALL or NONE) 1. Atomicity This property ensures that either the transaction occurs completely or it does not occur at all. • In other words, it ensures that • It is the responsibility of Transaction Control Manager to ensure atomicity of the transactions. • The operations either get Abort or Commit..

2. Consistency (Before & After the Transection, Sum same) • This property ensures that integrity constraints are maintained. • It ensures that the database remains consistent before and after the transaction. • It is the responsibility of DBMS application programmer to ensure consistency of the database. Example: After Commit 2000 Save in ACC.A ACC. A 3000 R(A) - 3000 A-A-I OOO W(A) - 2000 Commit Before 7000 Sent ACC. B 4000 R(B) - 4000 B +1000 W(B) - 5000 Commit After Commit 5000 Save lnACC. B.

3. Isolation (Multiple Transections Occur Independently) • The term 'isolation' means separation. • It means if two operations are being performed on two different databases, they may not affect the value of one another. • In the case of transactions, when two or more transactions occur simultaneously, the consistency should remain maintained. Any changes that occur in any particular transaction will not be seen by other transactions until the chan e is not committed in the memo Example: Read (X) x: 100 Write (X) Read (Y) Y: Y - 50 Write (Y) T2 Read (X) Read (Y) Write (4) DB.

Isolation. 1. Transaction T: T wants to transfer $50 from X to Y. T reads Y (value: 500), deducts $50 from X (new X = 450), and adds $50 to Y (new Y = 550). 2. Transaction T'': T'' starts and reads X (500) and Y (500). It calculates the sum: 500 + 500 = 1000. Meanwhile, values of X and Y change to 450 and 550 respectively. So, the correct sum should be 450 + 550 = 1000. Isolation ensures that T'' does not read outdated values while another transaction (T) is still in progress. Transactions should be independent, and T'' should access the final values only after T commits. This avoids inconsistent results, like the incorrect sum calculated by T''..

4. Durability (Successful Transection / Permanently Data Stored even if failure occures) • Durability ensures the permanency of something. • This property ensures that all the changes made by a transaction after its successful execution are written successfully to the disk. • It also ensures that these changes exist permanently and are never lost even if there occurs a failure of any kind. • The COMMIT command must be used every time we make changes. • It is the responsibility of Recovery Manager to ensure durability in the database. Data Saved Successfully / Permanently.

Lock Based Concurrency Control Protocol in DBMS. A lock in DBMS controls concurrent access, allowing only one transaction to use a data item at a time. This ensures data integrity and prevents issues like lost updates or dirty reads during simultaneous transactions. Lock Based Protocols in DBMS ensure that a transaction cannot read or write data until it gets the necessary lock. Here's how they work: These protocols prevent concurrency issues by allowing only one transaction to access a specific data item at a time. Locks help multiple transactions work together smoothly by managing access to the database items. Locking is a common method used to maintain the serializability of transactions. A transaction must acquire a read lock or write lock on a data item before performing any read or write operations on it..

Types of Lock Shared Lock (S): Shared Lock is also known as Read-only lock. As the name suggests it can be shared between transactions because while holding this lock the transaction does not have the permission to update data on the data item. S-lock is requested using lock-S instruction. Exclusive Lock (X): Data item can be both read as well as written. This is Exclusive and cannot be held simultaneously on the same data item. X-lock is requested using lock-X instruction..

Timestamp-Based Scheduling. Each transaction is assigned a unique timestamp at its start. Rules: Older transactions get priority System maintains: Read Timestamp (RTS) Write Timestamp (WTS) If a transaction violates timestamp order → it is rolled back. Advantages: Deadlock-free Ensures serializability Disadvantages: More rollbacks possible.

If T1 started before T2, then T1 must be executed first. Example: T1 → Read(A) T2 → Write(A) Since T1 has earlier timestamp, T2 must wait or be rolled back if it violates the order. Transactions: T1 (TS=5) T2 (TS=10) Operations: T1: Read(A) T2: Write(A) Since T1 is older, it executes first. If T2 writes before T1 reads, the system may rollback T2 to maintain order..

Database Recovery. Database recovery ensures the database remains consistent after failures. Types of Failures: Transaction failure System crash Disk failure Recovery Techniques: Log-based recovery Checkpointing Shadow paging.

Log-Based Recovery. The system maintains a log file to record all transactions. Log Entry Format: <Transaction ID, Data Item, Old Value, New Value> Write-Ahead Logging (WAL): Log must be written before actual database update. Types: Deferred Update (Redo only) Immediate Update (Undo/Redo) Recovery Process: Undo → Rollback incomplete transactions Redo → Reapply committed transactions.

Database security protects data from unauthorized access. 🔹 Authentication Verifies user identity. Examples: Username & password OTP Biometric 🔹 Authorization Determines access rights after authentication..

Access Control Models: Discretionary Access Control (DAC) Mandatory Access Control (MAC) Role-Based Access Control (RBAC) Security Threats: SQL Injection Data breaches Privilege escalation.