UNIT III TRANSACTIONS

 3.1 Transaction Concepts

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

• A transaction ensures that either all the operations are successfully executed (committed) or none of them take effect (rolled back).
• Transactions are designed to maintain the integrity, consistency and reliability of the database, even in the case of system failures or concurrent access.

Transaction

All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another

Example: Let’s consider an online banking application:

Transaction: When a user performs a money transfer, several operations occur, such as:

• Reading the account balance of the sender.
• Writing the deducted amount from the sender’s account.
• Writing the added amount to the recipient’s account.

Operations of Transaction

A user can make different types of requests to access and modify the contents of a database. So, we have different types of operations relating to a transaction. They are discussed as follows:

1) Read(X)

A read operation 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.

Example: For a banking system, when a user checks their balance, a Read operation is performed on their account balance:

SELECT balance FROM accounts WHERE account_id = 'A123';

This updates the balance of the user's account after withdrawal.

2) Write(X)

A write operation 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.

Example: For the banking system, if a user withdraws money, a Write operation is performed after the balance is updated:

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

This updates the balance of the user’s account after withdrawal.

3) 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.

Example: After a successful money transfer in a banking system, a Commit operation finalizes the transaction:

COMMIT;

Once the transaction is committed, the changes to the database are permanent and the transaction is considered successful.

4) Rollback

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

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 rolled back:

ROLLBACK;

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