Things in the database __ database

Affairs

Concept: A transaction is a logical unit of work in the data, and a transaction consists of an SQL statement that has a number of related behaviors to complete a set. The transaction mechanism ensures that the operations performed by this set of SQL statements are either completely successful or not implemented at all.

Key features: Ensuring the integrity of the database

Acid Properties of transactions:

A is the atomicity of a transaction: All operations contained in a transaction are either wholly or wholly inactive, which means that all activities are either fully reflected or not reflected in the data.

C is the consistency of transactions: the database must meet the rules of the business before transaction operations and transactions are processed.

I is the isolation of a transaction: the ability of a database to allow multiple concurrent objects to read or write data at the same time, and isolation can prevent the concurrent execution of multiple objects, resulting in inconsistencies in data due to cross execution of their operation commands

D is the persistence of the transaction: After the transaction is finished, it should be permanent to the modification. Even if the system is not lost in the event of a failure, this is the importance of the data decision.

Data exception:

Isolation defines the degree to which a thing is isolated from other things, in order to better understand the isolation layer. The first discussion of what might happen when a concurrent transaction accesses the same database, in general, in a concurrent transaction, occurs in 3:

Wrong read (dirty read), non-duplicate read (not repeatable), pseudo-read (Phantom Read)

Dirty reads: When one thing modifies a database, another transaction reads the data, but the first transaction is taken for some reason

Eliminate the data modification, so that the data returned to the original state, when the second thing read data and database inconsistent with the data, which is dirty read.

Non-repeat read: After one transaction reads data from the database, another transaction updates the data, and when the first transaction reads the data again, it finds that the data has changed, and that is, not duplicate reading. The result of a non-duplicate read is that the data read two times before and after a transaction is different.

False reads: If a transaction reads data based on a condition, another transaction updates the data in the same table, when the first transaction reads the data again, the different rows are returned based on the search criteria, which is the false read

These exceptions encountered in transactions are related to the isolation settings of the transaction, the more the transaction's isolation settings, the less the exception appears, but the lower the concurrency effect, the less the separation of the transaction, the more the exception appears, the higher the concurrency effect.

Select Isolation Layer

For inconsistencies in the data read in 3, the isolation level of 4 transactions is defined in Ansisql Standard 92. The following illustration shows:

Isolation Layer Error Reading | Dirty read non-repeat read | Can't repeat read false read |

READ UNCOMMITTED (non-submission) Yes Yes

Read Committed (Submit read) No Yes Yes

REPEATABLE READ (REPEATABLE) No NO is

Serializable (Serial read) No no No

Oracle supports two of the above four isolation layers: readcommitted and serializable. In addition Oralce also defines read only and read write isolation layers.

ReadCommitted This is the Oracle default isolation layer.

Serializable: When setting the isolation layer of a thing, the transaction is completely separated from the transaction, and the transaction executes serially, which does not mean that a transaction must end in order to start another transaction, but rather that the result of the execution of these transactions is consistent with the result of the transaction executed once.

Read only and read write when using read only, transactions cannot have any action statements that modify the data in the database, including INSERT, UPDATE, DELETE, create statements. Read Only is a subset of serializable, except that read only reads only, and serialzable can perform DML operations. Read Write it is the default setting, which means that you can have access statements and modify statements in a transaction. But it is not used frequently.

Execute statement:

Commit to commit the statement;

Rollback ROLLBACK TRANSACTION;

The SavePoint statement creates a save point in the current transaction:

ROLLBACK to SavePoint rolls back to the save point.