ADBMS: Transactions

Transaction

refers to a sequence of one or more operations (such as read, write, update, or delete) performed on the database as a single logical unit of work.

Transaction

single logical unit of work.

Trnsaction

ensures that either all the operations are successfully executed (committed) or none of them take effect (rolled back).

Committed

Rollback

A transaction ensures that either all the operations are successfully executed (____) or none of them take effect (______).

Transaction

are designed to maintain the integrity, consistency and reliability of the database, even in the case of system failures or concurrent access.

Inconsistent

During the transaction the database is ____

Committed

Only once the database is ____ the state is changed from one consistent state to another.

Transaction

is a program unit whose execution may or may not change the contents of a database.

Single unit

The transaction is executed as a

read-only transaction

If the database operations do not update the database but only retrieve data, this type of transaction is called a

CONSISTENT STATE

A successful transaction can change the database from one _____ to another.

Atomic, consistent, isolated and durable

DBMS transactions must be

read operation/Read(X)

is used to read the value of a particular database element X and stores it in a temporary buffer in the main memory for further actions such as displaying that value.

write operation/Write(X)

stores updated data from main memory back to the database. It usually follows a read, where data is fetched, modified

(e.g., arithmetic changes) and then written back to save the updated value

Read

SELECT balance FROM accounts WHERE account_id = 'A123’;

Write

•UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A123’;

Commit

This operation in transactions is used to maintain integrity in the database. Due to

some failure of power, hardware, or software, etc., a transaction might get

interrupted before all its operations are completed. This may cause ambiguity in the database, i.e. it might get inconsistent before and after the transaction

committed

Once the transaction is _____, the changes to the database are permanent and

the transaction is considered successful.

rollback

undoes all changes made by a transaction if an error occurs,

restoring the database to its last consistent state. It helps prevent data

inconsistency and ensures safety.

Rolled back

Example: Suppose during the money transfer process, the system encounters

an issue, like insufficient funds in the sender’s account. In that case, the

transaction is ___&

Rollback

This will undo all the operations performed so far and ensure that the database remains consistent.

Atomicity

Consistency

Isolation

Durability

ACID Properties of Transaction

Atomicity

A transaction is all or nothing. If any part fails, the entire

transaction is rolled back.

Atomicity

Example: While transferring money, both

debit and credit must succeed. If one fails, nothing should change.

Consistency

A transaction must keep the database in a valid state,

moving it from one consistent state to another.

Consistency

Example: If balance is ₹1000 and ₹200 is withdrawn, the new balance should be ₹800.

Isolation

Transactions run independently. One transaction’s

operations should not affect another’s intermediate steps.

Isolated

Example: Two users withdrawing from the same account must not interfere with each other’s balance updates.

Durability

Once a transaction is committed, its changes stay even if

the system crashes

Serial and Non-Serial Schedule

Transaction Schedules

Serial Schedule

transactions execute one at a time, ensuring

database consistency but increasing waiting time and reducing system

throughput.

Serial schedule

To improve throughput while maintaining consistency,

concurrent schedules with strict rules are used, allowing safe

simultaneous execution of transactions.

Non-Serial Schedule

multiple transactions are executed concurrently, interleaving their operations, instead of running one after another

Non-Serial Schedule

It improves system efficiency but requires concurrency control to maintain database consistency.

Transaction Recovery Techniques in DBMS

When a system failure occurs during a transaction (such as power

failure, hardware malfunction, software error, or deadlock), the

database can become inconsistent or partially updated.

Recovery Techniques

are designed to restore the database to a consistent state, ensuring the ACID properties are maintained.

Checkpointing

is a snapshot of the database state at a specific point in time.

Checkpointing

During recovery, the system does not have to start from scratch; it can

roll back to the last checkpoint and replay committed transactions.

Undo and Redo

Recovery involves two essential types of operations:

Undo

Revert the effects of incomplete or uncommitted transactions.

Redo

Reapply committed transactions that might not have been written to disk at the time of failure.

Write-Ahead Logging (WAL)

principle ensures that log records

describing changes are written to stable storage before the changes themselves are applied to the database.

WAL

Write-Ahead Logging

Deferred Update

In this approach, the system does not write changes to the database immediately

Deferred Update

No Undo/Redo approach

Deferred Update

In this approach, the system does not write changes to the database immediately.

Deferred Update

Changes are made in temporary memory (buffer).

Deferred Update

Only after a COMMIT is the data written to the database.

Shadow Paging

•Instead of modifying the actual database pages directly, the system

maintains a shadow copy of the database.

https://www.geeksforgeeks.org/dbms/transaction-in-dbms/

Reference

Shadow Paging

Once the transaction successfully commits, the new version replaces the old version atomically.

Shadow Paging

All operations are performed on a copy.