A Transaction is a set of operations that satisfy ACID, either committed through a COMMIT or rolled back with ROLLBACK.
1. Atomicty 原子性
Transactions are treated as the smallest indivisible unit, and all operations of a transaction are either committed successfully or rolled back if they fail.
2. Consistency 一致性
The database remains in a consistent state before and after the transaction is executed. In a consistent state, all transactions read the same data.
eg. let's say there is a constraint
a+b==10, if one transaction changes
b should be changed to match the constraint.
3. Isolation 隔离性
Before one transaction commits his changes, other transactions can not know these changes. Operations between transactions are independent.
4. Durability 持久性
Once we commit a transaction, changes it makes will be persistently stored in database, even though a system crash occurs.
- The execution result of a transaction is correct only if consistency is satisfied.
- In the case of no concurrency, transactions execute serially, isolation will be satisfied automatically. In this case, if you can satisfy atomicity, then you can satisfy consistency.
- In the case of concurrency, where multiple transactions are executed in parallel, transactions must not only meet atomicity, but also need to meet isolation in order to meet consistency.
- Transactions are persisted, in order to handle database crashes.
Concurrency Problem (隔离级别)
A transaction reads uncommitted data from another transaction during processing.
幻读 (Phantom Read)
Transaction A reads data according to A certain condition, during which transaction B inserts new data of the same search condition, and when transaction A reads again according to the original condition, it finds the newly inserted data of transaction B, which is called A magic read
Multiple queries on only one specific data tuple within one transaction will return different results.
How to Solve these bullshit?
Lock, that is, to lock a critical zone, so that it can not be modified by other transactions.
- Pessimistic lock悲观锁: It refers to the conservative attitude that the data is modified by the outside world (including other current transactions of the system, as well as transaction processing from the external system), so that the data is locked during the whole process of data processing
- Exclusive lock: When a transaction locked a block, the other can not read/edit.
- Shared lock: When a transaction T locked a block, it can ONLY read the block，while the other transaction can also lock the block with shared locks and read the block. Before T release the shared lock, everyone, include T, can NOT edit the block.
- Optimistic locking: Assuming no concurrency conflicts occur, only check for data integrity violations when the operation is committed. Optimistic locks do not solve the problem of