Fundamental Properties of Transcactions
A transcation is the smallest dividable part in the operation. It cannot be performed partially.
begin transaction; if(failure) rollback transaction; // aborts commit transaction; // success
Transaction can only read or write an element that:
- got granted a lock on the element
- has not yet released the lock
All elements got locked must be later unlocked.
Concurrently happened transactions have to run in isolation. They are always serializable.
Values written or modified by committed transactions will remain committed, even in the face of system crashes or power outages.
Level of Isolation in SQL
- read uncommitted (totally happens at the same time)
- read committed (will read anything that commited instead of treate current transaction atomically)
- repeatable read (could treat single values but cannot treat large data such as a table)
- serialization (default)
- the default mode in SQL
- Starts with
- Ends with
- When user exits, transaction got committed.
set autocommit on;lets every operation treated as separate transaction and committed when completed
set [global | session] transaction isolation level level
start transaction read only;