Database Transaction Control

Transactions Origin:

In DBMS, when multiple users access or modify the sameDatabaseTable may cause invalid data used by another user due to the behavior of one user. To solve this problem, SQL introduces transactions, transaction control, concurrent processing, and lock operations.

Transaction:

A transaction refers toWorkUnit. The Unit can contain multiple steps to complete the required tasks. In SQL, a series of inseparable database operations are executed as a whole through transactions, thus ensuring the integrity and effectiveness of the database.

A database transaction is an operation sequence that contains a group of database operation commands. All the commands in the transaction are used as a whole to submit or cancel the operation request to the system, that is, the database command is successfully executed or fails.

Features of transactions:

Atomic (Atomicity): either all succeed or all fail.
Consestent (consistency): Any data changes in the transaction comply with the data defined rules. When the transaction is completed, the data must be consistent. Transactions cannot store data in an unstable state, that is, modifications made to data through transactions cannot damage data. In relational databases, all rules must be applied to transaction modifications, to maintain data integrity.
Isolated (isolation): multiple transactions are performed at the same time, and they interfere with each other. If anyOthersIf the process uses the same data at the same time, the modification to the data will be valid until the transaction is successfully committed.
Durable (persistence): Once a transaction changes, the work done after the transaction is committed will be permanently saved. Even if hardware and ApplicationsProgramWhen an error occurs, make sure that the changes made to the data are not affected.

Transaction category:

Display transaction: a transaction that supports custom transactions is a transaction that defines its start and end. When the begintran and commit statements are used, the transaction is displayed.
Implicit Transaction: the transaction is automatically started after the current transaction or rollback. This transaction requires the commit statement or rollback statement to roll back or end the current transaction. After the rollback, a transaction is automatically started. (OracleAny DML statement in will start a transaction until the user executes the commit or rollback operation .)
An automatic transaction is a transaction that can be automatically executed and rolled back. In this transaction, the transaction is automatically committed. When an error occurs during the execution, the transaction is rolled back. (1. when the next DDL statement is executed, the Oracle system automatically submits the current transaction and starts a new transaction even if the user does not execute the commit or rollback operation. 2. when the DML statement starts to be executed, Oracle will execute a transaction by executing create table, And the cmmit command is automatically issued after the create table statement is executed)
 
Transaction Control:

Transaction control is to ensure the integrity and effectiveness of the database data, and a series of inseparable database operations as the overall operation. Transaction control only includes two operations: start transaction and end transaction.
SET transaction: used to set the attributes of the next transaction to be executed.
Start transaction: Start transaction execution.
Ser constrains: used to set the constraint mode in the current transaction.
Savepoint: The storage point set in the transaction. The storage point can be understood as a breakpoint to identify the position when the transaction starts again.
Release savepoint: indicates the release and save point.
Rollback: indicates that the transaction is terminated.
Commit: Indicates committing a transaction.

Concurrency Control:

When multiple users operate on the same data, they need to control the concurrency of the operation.

Concurrent operations:

Loss update: when multiple transactions operate on one row of data at the same time and are changed based on the originally selected value, each transaction does not know the existence of other transactions. The last update will overwrite the updates made by other firms, which will lead to data loss.
Dirty read: when another transaction is being updated in the second transaction, unconfirmed correlation issues will occur, the data being read by the second transaction has not been confirmed and may be changed by the transaction that updates this row.
Non-duplicate read: Non-duplicate read is similar to dirty read, which means that the second transaction accesses the same row of Data multiple times and will encounter inconsistency each time it reads different data.
Virtual read: When an insert or delete operation is performed on a row and the row is within the range being read by a transaction, virtual read occurs.

Transaction isolation level

The transaction isolation level can be understood as locking the transaction to reduce the impact of other transactions on these databases and ensure data consistency in multi-user systems, multiple transactions often need to operate on the data in a database at the same time. To prevent the interaction between transactions and improve the security and integrity of database data, the database system provides an isolation mechanism.

Isolation level:

Is the degree to which a transaction must be isolated from other transactions.

1. uncommitted read (read uncommitted): uncommitted read is the lowest transaction isolation, and only the physically damaged data is not read.
2. read committed: the level of committed read isolation is higher than that of uncommitted read isolation. The level of committed read isolation is the default isolation level of most mainstream databases, it ensures that one transaction does not read the modified but committed data of another parallel transaction, avoiding "Dirty reading ".
3. repeatable read: The Repeatable read isolation level ensures that one transaction does not modify the data that has been read but rolled back by another transaction, this prevents "dirty reads" and "non-repeated reads.
4. serializable: serializable isolation is the highest transaction isolation level. It connects transactions in a middle order to prevent one transaction from affecting other transactions.