A Database Transaction is a logical unit of processing in a DBMS which entails one or more database access operation. In a nutshell, database transactions represent real-world events of any enterprise.
All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction in DBMS. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another.
Facts about Database Transactions
A transaction is a program unit whose execution may or may not change the contents of a database.
The transaction concept in DBMS is executed as a single unit.
If the database operations do not update the database but only retrieve data, this type of transaction is called a read-only transaction.
A successful transaction can change the database from one CONSISTENT STATE to another
DBMS transactions must be atomic, consistent, isolated and durable
If the database were in an inconsistent state before a transaction, it would remain in the inconsistent state after the transaction.
Why do you need concurrency in Transactions?
A database is a shared resource accessed. It is used by many users and processes concurrently. For example, the banking system, railway, and air reservations systems, stock market monitoring, supermarket inventory, and checkouts, etc.
Not managing concurrent access may create issues like:
Hardware failure and system crashes
Concurrent execution of the same transaction, deadlock, or slow performance
States of Transactions
The various states of a transaction concept in DBMS are listed below:
State Transition Diagram for a Database Transaction
Let's study a state transition diagram that highlights how a transaction moves between these various states.
Once a transaction states execution, it becomes active. It can issue READ or WRITE operation.
Once the READ and WRITE operations complete, the transactions becomes partially committed state.
Next, some recovery protocols need to ensure that a system failure will not result in an inability to record changes in the transaction permanently. If this check is a success, the transaction commits and enters into the committed state.
If the check is a fail, the transaction goes to the Failed state.
If the transaction is aborted while it's in the active state, it goes to the failed state. The transaction should be rolled back to undo the effect of its write operations on the database.
The terminated state refers to the transaction leaving the system.
What are ACID Properties?
ACID Properties are used for maintaining the integrity of database during transaction processing. ACID in DBMS stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity: A transaction is a single unit of operation. You either execute it entirely or do not execute it at all. There cannot be partial execution.
Consistency: Once the transaction is executed, it should move from one consistent state to another.
Isolation: Transaction should be executed in isolation from other transactions (no Locks). During concurrent transaction execution, intermediate transaction results from simultaneously executed transactions should not be made available to each other. (Level 0,1,2,3)
Durability: · After successful completion of a transaction, the changes in the database should persist. Even in the case of system failures.
ACID Property in DBMS with example:
Below is an example of ACID property in DBMS:
Transaction 1: Begin X=X+50, Y = Y-50 END
Transaction 2: Begin X=1.1*X, Y=1.1*Y END
Transaction 1 is transferring $50 from account X to account Y.
Transaction 2 is crediting each account with a 10% interest payment.
If both transactions are submitted together, there is no guarantee that the Transaction 1 will execute before Transaction 2 or vice versa. Irrespective of the order, the result must be as if the transactions take place serially one after the other.
Types of Transactions
Based on Application areas
Non-distributed vs. distributed
Compensating transactions
Transactions Timing
On-line vs. batch
Based on Actions
Two-step
Restricted
Action model
Based on Structure
Flat or simple transactions: It consists of a sequence of primitive operations executed between a begin and end operations.
Nested transactions: A transaction that contains other transactions.
Workflow
What is a Schedule?
A Schedule is a process creating a single group of the multiple parallel transactions and executing them one by one. It should preserve the order in which the instructions appear in each transaction. If two transactions are executed at the same time, the result of one transaction may affect the output of other.
Example
Initial Product Quantity is 10
Transaction 1: Update Product Quantity to 50
Transaction 2: Read Product Quantity
If Transaction 2 is executed before Transaction 1, outdated information about the product quantity will be read. Hence, schedules are required.
Parallel execution in a database is inevitable. But, Parallel execution is permitted when there is an equivalence relation amongst the simultaneously executing transactions. This equivalence is of 3 Types.
RESULT EQUIVALENCE:
If two schedules display the same result after execution, it is called result equivalent schedule. They may offer the same result for some value and different results for another set of values. For example, one transaction updates the product quantity, while other updates customer details.
VIEW EQUIVALENCE
View Equivalence occurs when the transaction in both the schedule performs a similar action. Example, one transaction inserts product details in the product table, while another transaction inserts product details in the archive table. The transaction is the same, but the tables are different.
CONFLICT EQUIVALENCE
In this case, two transactions update/view the same set of data. There is a conflict amongst transaction as the order of execution will affect the output.
What is Serializability?
Serializability is the process of search for a concurrent schedule who output is equal to a serial schedule where transaction ae execute one after the other. Depending on the type of schedules, there are two types of serializability:
1. Conflict Equivalent Schedule
When either of a conflict operation such as Read-Write or Write-Read or Write-Write is implemented on the same data item at the same time within different transactions then the schedule holding such transactions is said to be a conflict schedule. The prerequisites for such conflict schedule are:
The conflict operations are to be implemented on the same data item.
The conflict operations (RW, WR, WW) must take place within different transactions.
At least one of the conflict operations must be the write operation.
Two Read operations will not create any conflict.
Two schedules (one being serial schedule and another being non-serial) are said to be conflict serializable if the conflict operations in both the schedules are executed in the same order.
Example:
Consider 2 schedules, Schedule1 and Schedule2,
Schedule2 (a non-serial schedule) is considered to be conflict serializable when its conflict operations are the same as that of Shedule1 (a serial schedule).
2. View Equivalent Schedule
Two schedules (one being serial schedule and another being non-serial) are said to be view serializable if they satisfy the rules for being view equivalent to one another.
The rules to be upheld are:
Initial values of the data items involved within a schedule must be the same.
Final values of the data items involved within a schedule must be the same.
The number of WR operations performed must be equivalent for the schedules involved.
Example:
Consider 2 schedules, Schedule1 and Schedule2:
The (non-serial) Schedule2 is considered as a view equivalent of the (serial) Schedule1, when the 3 rules of view serializability are satisfied. For the example shown above,
The Initial transaction of read operation on the data items A and B both begin at T1
The Final transaction of write operations on the data items A and B both end at T2
The number of updates from write-read operations are 2 in both the cases
Hence satisfying all the rules required, Schedule2 becomes view serializable w.r.t Schedule1.
3. Result Equivalent Schedule
Two schedules, S1 and S2 are said to result equivalent if they produce the same output obtained when the schedules are serially executed.
Often, this kind of schedule is given the least significance since the result derived are mainly focused on the output which in some cases may vary for the same set of inputs or might produce the same output for a different set of inputs.
Benefit of Serializability
Serializability serves as a measure of correctness for the transactions executed concurrently.
It serves a major role in concurrency control that is crucial for the database and is considered to provide maximum isolation between the multiple transactions involved.
The process of Serializability can also help in achieving the database consistency which otherwise is not possible for a non-serial schedule.
Summary:
Transaction management is a logical unit of processing in a DBMS which entails one or more database access operation
It is a transaction is a program unit whose execution may or may not change the contents of a database.
Not managing concurrent access may create issues like hardware failure and system crashes.
Active, Partially Committed, Committed, Failed & Terminate are important transaction states.
The full form of ACID Properties in DBMS is Atomicity, Consistency, Isolation, and Durability
Three DBMS transactions types are Base on Application Areas, Action, & Structure.
A Schedule is a process creating a single group of the multiple parallel transactions and executing them one by one.
Serializability is the process of search for a concurrent schedule whose output is equal to a serial schedule where transactions are executed one after the other.
Source: guru99
The Tech Platform
Comentarios