DBMS transactions
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 Aleksey will share different things that are not included in the course itself.
Go!
A transaction can be defined as a set of tasks, the execution of which is a prerequisite for the correct completion of a transaction. A single task is the minimum indivisible data change block.
We give an example of a simple transaction. Suppose a bank employee transfers 500 rupees from account A to account B. This small and simple transaction involves several low-level tasks.
')
Account A
Account B
ACID properties
A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability - abbreviated as ACID - to ensure accuracy, completeness, and integrity of data:
Serialization
When multiple transactions are executed by the operating system in a multiprogram environment, it is likely 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 may 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 after execution give the same result, 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 in the event that 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 the open lesson with Alexey .
Go!
A transaction can be defined as a set of tasks, the execution of which is a prerequisite for the correct completion of a transaction. A single task is the minimum indivisible data change block.
We give an example of a simple transaction. Suppose a bank employee transfers 500 rupees from account A to account B. This small and simple transaction involves 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 maintain Atomicity, Consistency, Isolation, and Durability - abbreviated as 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 its execution.
- 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 recent 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 in progress, but the system crashes before writing data to disk, the data must be updated immediately after the system restarts.
Serialization
When multiple transactions are executed by the operating system in a multiprogram environment, it is likely 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 completely 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 may 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 after execution give the same result, 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 in the event that 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 recording of values in S1, then it also performs the final recording 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 fails. 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 interrupted. The database recovery module can choose one of two operations after interrupting a transaction:- 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 the open lesson with Alexey .
Source: https://habr.com/ru/post/423309/
All Articles