Transaction control of SQL language

I. Overview

A transaction is a collection of database operations that consist of a set of related SQL statements (which can only be DML statements), which are an organic whole or are either all executed successfully or not. The basic unit of database concurrency control and recovery techniques in transactions.

A transaction has acid four features:

[1] atomicity (atomicity): A logical unit of work for a transaction when a logical operation in a transaction is either done or not.

[2] Consistency (consistency): The execution result of a transaction must be to move the database from one consistent state to another consistent state, no intermediate state.

[3] Isolation (isolation): The execution of one transaction in the database is not disturbed by other transactions, and each transaction does not feel that there are other transactions executing concurrently.

[4] Persistence (durability): Once a transaction is committed, changes to the data in the database are permanent, and subsequent operations or failures do not have any effect on the outcome of the transaction operation.

Second, the isolation level of the transaction

1. The operation of the art of war may lead to the following three problems:

[1] Loss of modification: Two transactions at the same time read the same data in the database and modify, the result of one transaction commits the results of another transaction committed, causing the first transaction to the data modification is lost.

[2] read "Dirty" data: A transaction is read by another transaction before committing to the modification of the data.

[3] Non-repeatable READ: After a transaction has read the data, the other transaction modifies the data and commits it, and when the transaction re-reads the data, it will get a different result than the previous one.

2, for the above problems, Oracle provides two levels of transaction isolation

[1] Read commited (Submit read)

The default isolation level for transactions, which sets statement-level consistency. Query operations performed by each transaction can only obtain data that has been committed before the start of the query, not before the start of the transaction. This isolation level prevents the problem of loss of modification and dirty reads, but does not prevent non-repeatable reads. DML operations can be performed in transactions of this level (if the data is locking, wait for other transactions to be unlocked).

[2] SERIALIZABLE (serialization)

Used to set transaction-level consistency, and each transaction can only see data that has been committed before the transaction started. This level of isolation prevents the above three issues. DML operations can be performed in transactions of that level (if the data is locking, no wait, and an error is returned).

The READ commited isolation level is appropriate if there is a large number of concurrent transactions in the database, and the transaction capability and responsiveness of the application is a critical factor. If multiple transactions in a database have a low probability of accessing data concurrently, and most transactions persist for a long time, the SERIALIZABLE isolation level is appropriate.

Set and modify the isolation level as follows:

SET TRANSACTION Isolation  Level isolation level; ALTER SET =

In an Oracle database, you can set read only (read-only), and each transaction can see only data that has been committed before the transaction starts, and it cannot perform DML operations on the data in that transaction, guaranteeing the acquisition of data at a specific point in time.

SET TRANSACTION  READ only;

Third, transaction processing

1. Transaction Submission

In an Oracle database, there are two ways:

[1] The user executes the COMMIT command;

[2] When performing a specific operation, the system commits automatically.

When a transaction commits, the log of the user's modifications to the database is written by the log buffer to the redo log file, releasing the system and database resources that the firm occupies. At this point, other sessions can see the results of the transaction's modifications to the database.

The system commits automatically when you execute commands such as CREATE, ALTER, DROP, RENAME, REVOKE, GRANT, CONNECT, DISCONNECT, and so on.

2. Transaction rollback

The operation in the transaction is canceled by the ROLLBACK command, and after the command is executed, all operations in the transaction are canceled, the database reverts to the state before the transaction started, and the system and database resources consumed by the transaction are freed.

If you want to cancel only some of the operations in a transaction, rather than canceling all operations, you can set up a savepoint inside a transaction, dividing a large transaction into several components so that the transaction can be rolled back to the specified savepoint.

Use the SavePoint statement to set the save point.

INSERT  into  ... SavePoint AUPDATE  ... SavePoint B

Rollback:

ROLLBACK  to A; ROLLBACK  to B; ROLLBACK;

Transaction control of SQL language