Database transactions, deadlocks, and storage engines

Storage engine Transactions 1. Characteristics of a transaction

If a database supports transactions, the database must have the following four transaction-related attributes (ACID):

⑴ atomicity (atomicity)

Atomicity means that all the operations that a transaction contains are either all successful or all fail to roll back. Therefore, the operation of a transaction must be fully applied to the database if it succeeds, and it cannot have any effect on the database if the operation fails.

⑵ Consistency (consistency)

Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

⑶ Isolation (Isolation)

Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

The isolated database on transactions provides a variety of isolation levels, which are described later.

⑷ Persistence (Durability)

Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost.

Understanding of the transaction:

If the transaction is not turned on, the default single SQL statement exists in a transaction, the statement executes successfully, the transaction is automatically committed, so it can be assumed that all statements are executed in the transaction regardless of whether the transaction is open or not, except that the scope of the transaction is large or small.

2. Problems with the database in the concurrency Process 1. Dirty Read

Externally read UNCOMMITTED changes within a transaction.

2. Non-repeatable reading

Externally read the committed change within a transaction, which refers to a change in the value in the column.

3. Virtual Reading

Externally read committed changes within a transaction, which refers to changes in the number of rows (adding rows, deleting rows).

Isolation level of a transaction

How to avoid the above problem, which is related to the isolation level of the transaction. The default isolation level for MySQL is: repeatable read.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Understanding of the Transaction isolation level: The isolation level in the current database connection represents the rigor of the data in the connection, regardless of how the isolation level in the current connection changes, without affecting the other connections, only affecting themselves.

Common directives

To modify the isolation level of a database

SET [session| GLOBAL] TRANSACTION Isolation level [READ uncommitted| READ committed| Repeatable read| SERIALIZABLE]

To view the transaction isolation level for the current database connection

SELECT @ @tx_isolation;

1. Demo READ UNCOMMITTED

For connection A, under the default isolation level, open the transaction, modify the Test1 data (the value of the field since increment is 3), and do not commit the transaction.

Then create the connection B, modify the isolation level to read uncommited, and then query the TEST1 data, dirty read, read out the data is 3.

2. Demo Read Committed

In the example above, proceed to modify the isolation level of connection B to read commited, read the data again to 2, and the dirty read disappears.

Next, the B connection opens the transaction, the other side commits the transaction (query data is 3), B queries the data in the transaction, the result is 3, there is a "non-repeatable read" problem, and B commits the transaction

3. Demo REPEATABLE Read

(1) a start the transaction again, self-increment the data, read the data is 4, the transaction does not commit.

(2) on the other side B modify the isolation level to repeatable read, open the transaction, and query the data to 3. (Dirty reads do not appear).

(3) A commits a transaction, then B appears "non-repeatable read problem". b Read data is 4

4. Demo Serializable deadlock

Database transactions, deadlocks, and storage engines