Four features of database transactions

Four features of database transactions: database transactions must meet four features: ACID, I .e., Atomic, Consistency, Isolation, and Durability ). In relational databases, these features need to be implemented through redolog, lock, snapshot, and other means. Isolation requires that each transaction is independent of each other.

Four features of database transactions: database transactions must meet four features: ACID, I .e., Atomic, Consistency, Isolation, and Durability ). In relational databases, these features need to be implemented through redo log, lock, snapshot, and other means. Isolation requires that each transaction is independent of each other.

Four features of database transactions

Database transactions must meet four requirements: ACID, I .e., Atomic, Consistency, Isolation, and Durability ).

In relational databases, these features need to be implemented through redo log, lock, snapshot, and other means.

Isolation requires that each transaction is independent of each other, and the operations performed will not affect each other. It is intended to ensure the consistency of each data read/write operation.

The standard SQL92 Specification defines four isolation levels and three Phenomena (Phenomena) to be banned ).

The higher the isolation level, the stronger the guarantee of data consistency, but the worse the database concurrency. Problems caused by concurrent database operations include:

1. Dirty read (Dirty read ).

Transaction A reads data that has not been committed in transaction B. If transaction B rolls back, A reads the wrong data.

2. Unrepeatable read ).

When transaction A reads data multiple times, transaction B modifies the data, resulting in inconsistent data read by transaction A multiple times.

3. Phantom read ).

When transaction A reads data multiple times, transaction B adds operations on the data, resulting in inconsistent data read by transaction A multiple times. The difference between phantom read and non-repeated read is that,

Record update operations cannot be repeated. You only need to add a write lock to the record to avoid this. Phantom read is an insert operation on the record,

To disable phantom read, you must add a global write lock (for example, add a write lock to the table ). There are also two types of missing updates:

4. Category 1 loss update (rollback loss, Lost update ).

During transaction A, transaction B updates the data. After transaction A is revoked, it overwrites the data committed by transaction B. SQL92 does not define this phenomenon. All isolated sectors defined in the standard do not allow the occurrence of the first type of missing updates.

5. The Second type is missing updates (overwrite loss, Second lost update ).

During transaction A, transaction B updates the data. After transaction A commits, it overwrites the data that transaction B has committed. The second type of loss update is actually the same as non-repeated read.

SQL92 defines four isolation levels:

1. Read uncommitted ).

Write operation and read operation are not locked. Disable the first type of update loss, but all other data concurrency problems may occur.

2. Read committed ).

Write operation plus write lock, read operation plus read lock. Disable the loss of updates and dirty reads in the first category. This is the default isolation level for most relational databases.

3. Read repeatable ).

When the read operation is locked to the end of the transaction, the update operation of other transactions can only be performed after the transaction ends. The difference between reading and submitting is that,

The read operation that commits the read operation adds a read lock to the end of this read operation, and the granularity of the locks that can be re-read is greater. Two types of update loss and non-repetition are prohibited, but Phantom read may occur.

4. Serializable ).

The read operation adds a table-level read lock to the end of the transaction. Phantom read is prohibited.

Type 1 lost updates cannot be repeated read dirty read type 2 lost update phantom read

Read uncommitted prohibited not prohibited

Read committed prohibited

Read repeatable prohibited

Serializable prohibited

Most relational databases use the Read committed isolation level by default, and Mysql InnoDB uses the Read repeatable isolation level by default,

This is related to the Statement log format used by the Mysql replication mechanism. The implementation of database isolation levels is also different,

For example, Oracle supports Read committed and Serializable isolation levels,

In addition, you can use Read snapshots to prohibit repeated Read at the Read committed level;

Mysql InnoDB uses the next-key locking policy at the Read repeatable level to avoid phantom Read.

Posted on