To understand what database transactions are, you need to understand why a database needs transaction management in the first place.

The simplest example of A transaction is A bank transfer. A transfers 100 to B. The transfer is completed by subtracting 100 from A and adding 100 from B. However, there may be various uncontrollable factors in the operation of the program. If after 100 is subtracted from A, the bank fails to perform operation on B account due to power failure or earthquake, the program stops and 100 is subtracted from A, but B does not add. This is where transaction management comes in.

Four characteristics of transactions

1. Generally speaking, a transaction must satisfy four ACID conditions (ACID)

Autmic: The transaction is in execution, that is, the transaction is not allowed to be executed separately. A transaction is an indivisible unit of work. Even if the transaction cannot be completed due to a failure, rollback must be performed to rollback to the state before operations were performed on the database.

Consistency: The transaction must change the database from one consistent state to another. Consistency is closely related to atomicity. For example, after account A and account B transfer each other, the total amount remains the same.

Isolation: The execution of a transaction cannot be interfered by other transactions. When multiple transactions are executed concurrently, each transaction does not affect the other.

Durability: Also called permanence, they refer to the fact that once a transaction is committed, its changes to data in the database should be permanent.

The four properties of a transaction (ACID) are implemented by a relational database management system (RDBMS). Database management systems use logging to ensure atomicity, consistency and persistence of transactions. A log records the updates made by a transaction to the database. If an error occurs during the execution of a transaction, you can undo the updates made by the transaction to the database according to the log, making the database return to the initial state before the execution of the transaction.

Database management system uses lock mechanism to realize transaction isolation. When multiple transactions update the same data in the database at the same time, only the transaction holding the lock is allowed to update the data, and the other transactions must wait until the previous transaction releases the lock before the other transactions have a chance to update the data.

Two, on the dirty reading, not repeated reading, magic reading

1. DirtyReads: DirtyReads are reads of Drity Data, which are uncommitted Data. A transaction is making changes to a record, the data is in the pending state (either committed or rolled back) until the transaction completes and commits, and a second transaction reads the uncommitted data and performs further processing accordingly, resulting in uncommitted data dependencies.

Non-repeatablereads: a transaction reads the same record successively, during which another transaction modifies the data and commits, so the data read twice is different, which is called non-repeatable read. PhantomReads: a transaction reads the same table in sequence while other transactions insert new data and commit. This phenomenon is called PhantomReads. The difference between it and unrepeatable reading: unrepeatable reading focuses on modification, while unreal reading focuses on addition and modification.

Iii. Isolation level

Since you know that transactions can have dirty reads, unrepeatable reads, and phantom reads, you need to control them, hence the isolation level. There are four levels of general isolation:

READ UNCOMMITTED: Magic READ, unrepeatable READ, and dirty READ are allowedCopy the code

If the isolation level of the database is REAE UNCOMMITTED, other threads can see the UNCOMMITTED data and therefore dirty reads occur.

READ COMMITTED: Magic and unrepeatable reads are allowed, but dirty reads are not allowedCopy the code

If the database isolation level is set to READ_COMMITTED, which means that uncommitted data is invisible, dirty reads are avoided. However, the data being read only acquires a read lock, which is unlocked after reading, regardless of whether the current transaction is finished or not, allowing other transactions to modify the data being read by the transaction. The result is unrepeatable read.

REPEATABLE READ: Unreal READ is allowed, but unrepeatable and dirty READ is not allowedCopy the code

REPEATABLE READ Prevents unrepeatable reads because the data being operated is locked and is not released until the end of the transaction.

SERIALIZABLE: Unreal, unrepeatable and dirty reads are not allowedCopy the code

SERIALIZABLE guarantees that phantom reads will not occur because a range lock is acquired and transactions are executed sequentially.

Isolation level Dirty read probability Non-repeatable read possibility Illusory possibility Read lock
READ UNCOMMITTED YES YES YES NO
READ COMMITTED NO YES YES NO
REPEATABLE READ NO NO YES NO
SERIALIZABLE NO NO NO YES

Therefore, the READ UNCOMMITTED level is the lowest and SERIALIZABLE level is the highest. A higher level is certainly better for maintaining the four features of transactions, but it sacrifices database efficiency because the implementation of SERIALIZABLE is similar to thread locks in Java.

ORACLE COMMITTED transaction management is enabled by default. Data operations performed in DML require a COMMIT and can be rolled back. REPEATABLE READ (REPEATABLE READ); set autocommit (REPEATABLE READ);

Mysql provides two transactional storage engines: InnoDB and NDB Cluster. SET the ISOLATION LEVEL by executing the SET TRANSACTION ISOLATION LEVEL command. The new isolation boundary takes effect at the start of the next transaction, or you can set the isolation level for the entire database in the configuration file.

mysql>SET SESSION TRANSACTION ISOLATION LEVEL  READ COMMITTED
Copy the code

Implicit and explicit locking

1.InnoDB uses a two-phase lockout protocol. A transaction can be locked at any time during execution and released only when a COMMIT or ROLLBACK is performed. InnoDB also automatically locks when needed, depending on the isolation level.

2.InnoDB also supports display locking through specific statements.

SELECT XXX LOCK IN SHARE MODE

SELECT XXX FOR UPDATE
Copy the code

MYSQL also supports LOCK TABLES and UNLOCK TABLES, which are implemented at the server level, independent of the storage engine.