Introduction to Postgres transaction
1, What is transaction?
A transaction is a unit of work which bundles multiple steps into a single operation. It is treated in a coherent and reliable way independent of other transactions, and is also the foundation for concurrent execution and recovery from system failures.
2, Usage
BEGIN [TRANSACTION] [transaction_mode];
commands;
COMMIT/ROLLBACK [TRANSACTION]
3, Properties: ACID
— -Atomicity — -
What is Atomicity?
Actions in transactions are carried out all or none.
— -Consistency — -
What is Consistency?
A transaction must change affected data only in allowed ways, according to all defined rules.
— -Isolation — -
1, What is Isolation?
When there are several transactions happen concurrently, users should be able to understand a transaction without considering the effect of other concurrently existing transaction.
2, Concurrency
- Interleaved Transaction
Interleaving transactions allows multiple users of the database to access it at the same time. When there are multiple transactions, actions (reading, writing, aborting or committing) in transactions could be interleaved to improve the performance.
- Transaction schedule
Transaction schedule describes a list of actions from a set of transactions as seen by the DBMS.
- Serial Schedule : If the actions of different transactions are not interleaved and are executed from start to finish, one by one.
- Serializable Schedule : Over a set of committed transactions is a schedule whose effect is guaranteed to be identical to serial schedule.
3, Conflicts
- Dirty Read:
Transaction T2 could read a database object A that has been modified by another transaction T1 which has not yet committed.
- Unrepeatable Read:
A transaction T2 could change the value of an object that has been read by a transaction T1, while T1 is still in progress.
- Phantom Read:
If the collection of database objects is not fixed, but can grow and shrink through the insertion and deletion of objects, the results differs from serial execution of T1 and T2.
- Lost Update:
For concurrent transactions, a transaction T2 could overwrite the value of an object A which has already been modified by a transaction T1.
4, Isolation Levels
READ UNCOMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
— -Durability — -
1, What is Durability?
Once a transaction has been successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk.
2, The machenism?
WAL: write-ahead logging
Any change to a database object is first recorded in the log before the change to the database object is written to disk.
