Database transaction acid attribute, database concurrency problem and four isolation levels

Time:2021-10-14

Database transaction acid attribute, database concurrency problem and four isolation levels

Database transaction

A database transaction is a set of logical operation units that transform data from one state to another

A set of logical operation units; One or more DML operations

Transaction processing principles

Ensure that all transactions are executed as a unit of work. Even if there is a failure, this execution mode cannot be changed.
When a transaction performs multiple operations, or all transactions are committed, it is permanently saved; Either discard all modifications and roll back the entire transaction to its original state

Once the data is submitted, it cannot be rolled back

Those actions will result in automatic submission

Once the DDL operation is executed, it will be submitted automatically
By default, DML is automatically submitted once executed
You can cancel DML automatic submission by setting autocommit = false
By default, data is automatically submitted when the connection is closed

Acid attribute

Transactions must satisfy four attributes, namely atomicity, consistency, isolation and durability, namely acid.

Atomicity

A transaction is an indivisible whole. In order to ensure the overall goal of the transaction, the transaction must be atomic, that is, when the data is modified, either all or no execution is performed. That is, transactions are not allowed to complete partially, avoiding the error caused by executing only part of these operations.

uniformity

Before and after a transaction is executed, the database data must be consistent. The consistency state of the database should meet the constraints specified by the schema lock. After the transaction is fully executed, the database is still in the consistency state.

For example, for bank transfer, the sum of the two accounts before and after the transfer should remain unchanged.

Isolation

Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transactions. When a transaction views the database, the state of the data is either the state before another concurrent transaction modifies it or the state after another transaction modifies it. The transaction will not view the data in the intermediate state.

For example, for any pair of transactions T1 and T2, for T1, T2 either ends before T1 starts or starts execution after T1 completes.

persistence

It is also called permanence. After the transaction is completed, the DBMS (database management system) ensures that its modification to the data in the database is permanent. In case of system or media failure, the modification is also permanent. Persistence is generally guaranteed by database backup and recovery.

  • be careful      Strictly speaking, the database transaction attributes are guaranteed by the database management system. In the running process of the whole application, the application does not need to consider the acid implementation of the database.

Typically, a transaction is terminated by executing a commit or rollback statement. When the commit statement is executed, all changes made to the database since the transaction was started become permanent, that is, they are written to disk. When the rollback statement is executed, all changes made to the database since the transaction was started will be revoked, and the contents in the database will return to the state before the transaction started. In any case, the consistency state can be guaranteed when the transaction is completed.

Database concurrency problem

If there is no lock and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Data inconsistencies caused by concurrent operations include missing data updates, reading “dirty” data (dirty reads), and non repeatable reads.

Update lost

  • Both transactions update a row of data at the same time. The update of data by one transaction overwrites the update of data by another transaction. This is because the system does not perform any lock operations, so concurrency is not isolated.

Dirty reading

  • One transaction read the uncommitted data operation result of another transaction. This is quite dangerous because it is likely that all operations will be rolled back.

Non repeatable reading

  • Non repeatable reads: a transaction repeatedly reads the same row of data twice, but gets different results.

Include the following:

  • Virtual read: after transaction T1 reads some data, transaction T2 modifies it. When transaction T1 reads the data again, it gets a different value from the previous time.
  • Phantom reading: the transaction is queried twice during the operation. The result of the second query contains the data that does not appear in the first query or is missing the data that appears in the first query. This is because another transaction inserts data during two queries.

Four isolation levels for database transactions

There are four isolation levels for database transactions, from low to high: read uncommitted, read committed, repeatable read and serializable. These four levels can solve dirty reading, non repeatable reading and unreal reading one by one.
Different isolation levels have different transaction processing.

  • Read uncommitted: only deal with lost updates. If a transaction has started to write data, other transactions are not allowed to write at the same time, but other transactions are allowed to read this row of data. It can be realized through “exclusive write lock”.
  • Read committed: deal with missing updates and dirty reads. Transactions that read data allow other transactions to continue to access the diverted data, but uncommitted write transactions will prohibit other transactions from accessing the diverted data. It can be realized through “instant shared read lock” and “exclusive write lock”.
  • Repeatable read: handles lost updates, dirty reads, and non repeatable reads. Transactions that read data prohibit write transactions, but read transactions are allowed, and write transactions prohibit any other transactions. It can be realized through “shared read lock” and “exclusive write lock”.
  • Serializable: provides strict transaction isolation. Serialization execution is required to be lost. Transactions can only be executed one by one and cannot be executed concurrently. Transaction serialization cannot be realized only through “row level lock”. Other mechanisms must be used to ensure that the newly inserted data will not be accessed by the transaction just executing the query operation.

The higher the isolation level, the more data integrity and uniformity can be guaranteed, but the greater the impact on concurrency performance. For most applications, setting the isolation level of the database system to read committed is a priority. It can avoid dirty reading and has good concurrency performance. Although it will lead to concurrency problems such as non repeatable reads, unreal reads and the second type of lost updates, in individual cases where such problems may occur, the application can use pessimistic locks or optimistic locks to control them.

  • Oracle supports two transaction isolation levels: read committed and sequential. Oracle’s default transaction isolation level is read committed
  • MySQL supports the transaction isolation level in 4. The default transaction isolation level of MySQL is repeatable read

Transaction isolation level settings

//View current transaction level:
SELECT @@tx_isolation;
//Set the isolation level of MySQL:
//Set session transaction isolation level sets the transaction isolation level

//Set the read uncommitted level:
set session transaction isolation level read uncommitted;

//Set the read committed level:
set session transaction isolation level read committed;

//Set repeatable read level:
set session transaction isolation level repeatable read;

//Set serializable level:
set session transaction isolation level serializable;

Java code obtains and sets the isolation level (conn below is the connection object, and the specific implementation will not be sorted out)

//Gets the database transaction isolation level
int transactionIsolation = conn.getTransactionIsolation();
//Set the database transaction isolation level; Transaction isolation level: transaction_ READ_ COMMITTED
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);