DBMS transactions
- Transfer
All beaver! We are actively expanding our, so to speak, range of courses, and now we are pleased to present a new one: “Relational DBMS” . The course was created by one of the leading teachers of the Linux Administrator course , Alexey Tsykunov . Otherwise, everything will be as usual: usefulness and open lessons , in which Alexey will share different things that are not included in the course itself.
Go!
A transaction can be defined as a set of tasks whose execution is a prerequisite for the correct completion of a transaction. A single task is the minimum indivisible data change block.
Let's give an example of a simple transaction. Suppose a bank employee transfers 500 rupees from account A to account B. This is a small and simple transaction involving several low-level tasks.
Account A
Account B
ACID Properties A
transaction in a database system must retain Atomicity, Consistency, Isolation, and Durability - abbreviated ACID - to ensure accuracy, completeness and integrity of data:
Serialization
When multiple transactions are performed by the operating system in a multiprogram environment, there is a chance that the instructions of one transaction will be mixed with the instructions of another.
In a multitransactional environment, a consistent schedule is considered a benchmark. The order of execution of instructions in a transaction cannot be changed, but the execution of instructions of two transactions can be random. Execution does not do any harm when the two transactions are mutually independent and work on different data segments; but if they use the same data, the results may differ, which may lead the database to an inconsistent state.
To solve this problem, we allow parallel execution of a transaction schedule if transactions are serialized or have some equivalence relation.
Equivalent schedules
Equivalent schedules can be of the following types:
Equivalence of the result
If two schedules give the same result after the execution, they are considered equivalent in result. Results may be the same for some values and different for another set of values. Therefore, this equivalence is usually not considered significant.
Representation Equivalence
Two schedules are considered equivalent in representation if transactions in each of the schedules perform similar actions in a similar way.
For example:
Conflict Equivalence
Two schedules will conflict if they have the following properties:
Two schedules with multiple transactions with conflicting operations are considered equivalent to a conflict only if:
Note - Schedules equivalent in representation are serializable in representation, and conflict-equivalent are conflict-serializable. All conflict-serializable schedules are serializable by submission.
Transaction States A transaction
in a database can be in the following states:
THE END
As always, we are waiting for comments, a question that can be asked both here and in an open lesson with Alexey .
Go!
A transaction can be defined as a set of tasks whose execution is a prerequisite for the correct completion of a transaction. A single task is the minimum indivisible data change block.
Let's give an example of a simple transaction. Suppose a bank employee transfers 500 rupees from account A to account B. This is a small and simple transaction involving several low-level tasks.
Account A
Open_Account(A)
Old_Balance = A.balance
New_Balance = Old_Balance - 500
A.balance = New_Balance
Close_Account(A)
Account B
Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500
B.balance = New_Balance
Close_Account(B)ACID Properties A
transaction in a database system must retain Atomicity, Consistency, Isolation, and Durability - abbreviated ACID - to ensure accuracy, completeness and integrity of data:
- Atomicity - this property indicates that a transaction should be considered as an atomic unit, that is, all its operations are performed, or none of them. The database should not have states in which the transaction is partially completed. States must be set either before the start of the transaction, or after the completion / interruption / failure of the transaction.
- Consistency - this property indicates that the database must remain in a consistent state after any transaction. No transaction should have a negative effect on the data in the database. If the database was in a consistent state before the transaction, then it should be in it after it has been completed.
- Isolation — in a database system where several transactions are performed simultaneously and in parallel, the isolation property indicates that each transaction will be executed and executed as if it were the only transaction in the system. No transaction should affect the existence of other transactions.
- Resilience - this property indicates that the database must be sufficiently stable to store all the latest updates, even if the system makes a mistake or reboots. If carrying out a transaction leads to updating a piece of data in the database, then these changes should continue to be stored in the database. If a transaction is made, but the system crashes before writing data to disk, the data should be updated immediately after the system restarts.
Serialization
When multiple transactions are performed by the operating system in a multiprogram environment, there is a chance that the instructions of one transaction will be mixed with the instructions of another.
- A schedule is a chronological execution of a series of transactions. There can be many transactions in the schedule, each of which consists of several instructions / tasks.
- A sequential schedule is a schedule in which transactions are arranged so that each of the transactions is fully executed. The schedule is called sequential just because of the sequential way of execution.
In a multitransactional environment, a consistent schedule is considered a benchmark. The order of execution of instructions in a transaction cannot be changed, but the execution of instructions of two transactions can be random. Execution does not do any harm when the two transactions are mutually independent and work on different data segments; but if they use the same data, the results may differ, which may lead the database to an inconsistent state.
To solve this problem, we allow parallel execution of a transaction schedule if transactions are serialized or have some equivalence relation.
Equivalent schedules
Equivalent schedules can be of the following types:
Equivalence of the result
If two schedules give the same result after the execution, they are considered equivalent in result. Results may be the same for some values and different for another set of values. Therefore, this equivalence is usually not considered significant.
Representation Equivalence
Two schedules are considered equivalent in representation if transactions in each of the schedules perform similar actions in a similar way.
For example:
- If T reads the original data in S1, then it also reads the original data in S2.
- If T reads the value written J in S1, then it also reads the value written J in S2.
- If T performs the final record of values in S1, then it also performs the final record of values in S2.
Conflict Equivalence
Two schedules will conflict if they have the following properties:
- They belong to different transactions.
- They refer to the same data.
- At least one of them is a “write” operation.
Two schedules with multiple transactions with conflicting operations are considered equivalent to a conflict only if:
- Both schedules contain the same set of transactions.
- The order of conflicting pairs of operations is maintained in both schedules.
Note - Schedules equivalent in representation are serializable in representation, and conflict-equivalent are conflict-serializable. All conflict-serializable schedules are serializable by submission.
Transaction States A transaction
in a database can be in the following states:
- Active (Active) - in this state, the transaction starts to run. This is the initial state of any transaction.
- Partially Perfect (Partially Committed) - when a transaction performs its final operation, it is considered that it is in a partially perfect state.
- Failed — A transaction is considered failed if one of the checks performed by the database recovery system failed. Such a transaction cannot proceed.
- Interrupted - if some check failed and the transaction went into a failed state, the recovery manager will roll back all write operations in the database to return it to its original state before starting the transaction.
Transactions in this state are considered aborted. The database recovery module can choose one of two operations after a transaction is interrupted:- Restart transaction;
- Cancel transaction.
- Perfect - if the transaction has successfully completed all operations, it is considered perfect. All its effects are permanently recorded in the database system.
THE END
As always, we are waiting for comments, a question that can be asked both here and in an open lesson with Alexey .