- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
What is a Transaction?
A transaction is an operation or series of operations that are being executed by an individual user or application program that reads or updates the contents of the database.
A transaction can be represented as a logical unit of work on the database. This may be a whole program, a part of the program, or an individual command (like the SQL command such as insert or update), and it can be engaged in several operations on the database. In the database context, the implementation of an application program can be thought of as one or more transactions with non-database transforming taking place in between.
Suppose an employee of bank transfers Rs.400 from P’s account to Q’s account. This small transaction contains various low-level functions:
- Old_Balance = P.balance
- New_Balance = Old_Balance – 400
- P.balance = New_Balance
- Old_Balance = Q.balance
- New_Balance = Old_Balance + 400
- Q.balance = New_Balance
Operations of Transaction
Transactions have two common operations, which are as follows.
Read (P): Read operation is applied to read the cost of P from the database and save it in the buffer in the main memory.
Write (P): Write operation is applied to write the cost back to the database from the buffer.
Let’s take an example of a transaction that transfers Rs. 400 from P account to Q account. This transaction can be represented as:
- Read (P);
- P = P – 400;
- Write (P);
- Read (Q);
- Q: =Q+400;
- Write (Q);
Let’s assume the value of both P and Q before starting of the transaction is 3200.
First operation reads P’s cost from the database and saves it in the buffers.
Second operation will reduce the cost of P by 400. So the buffer will include 2800.
Third operation will write the buffer’s cost to the database. So, P’s final cost will be 2800.
Fourth operation reads Q’s cost from the database and saves it in a buffer.
Fifth operation will increase the cost of Q by 400. So the buffer will include 3600.
Sixth operation will write the buffer’s cost to the database. So, B’s final cost will be 3600.
But it can be feasible that because of the loss of hardware, software or potential, etc. that transaction can fail earlier completed all the operations in the set.
For example: In the above transaction, the debit transaction decline after implementing the second operation, then P’s cost will remain 3200 in the database, which is not agreeable by the bank.
To solve this problem, we have two essential operations:
Commit Operation: A transaction is said to be committed when it has ended successfully, and all its updates are made permanent in the database, and the database is in a consistent state.
Rollback Operation: A transaction is said to be rolled back when it is ended unsuccessfully due to some error (logical or physical), and in this case, all the updates done by that transaction are undone.
What is a Transaction Log?
In a transaction log, all the transactions that update the database are stored, and this information is used by the DBMS in case of recovery when either a program’s abnormal termination or a system failure such as a network discrepancy or a disk crash take place. Some RDBMS applied the transaction log to recover a database leading to a directly consistent state. After a server loss, for example, ORACLE necessarily rolls back uncommitted transactions and rolls forward transactions that were devoted but not yet reported to the physical database.
While the DBMS implements the transactions that change the database, it also necessarily updates the transaction log. The transaction log saves the following.
- Data for the starting of the transaction.
- For every transaction component (SQL Statement).
- Types of operation being implemented (update, delete, and insert).
- Names of objects concerned by the transaction.
- The “before\”” and “after\”” values for the area are updated.
- Pointers to the old and next transaction log entries for a similar transaction.
- Ending (commit) of the transaction.
Although using transaction log increases the processing overheads of a DBMS, the ability to restore a corrupted database is worth the price.
Enroll Yourself in Live Training: DBMS Training