Serializable Transactions
Transactions execute one at a time, rather than concurrently - ensure database is moved form one consistent state to another. Compromises transaction throughput. Ensures against 3 phenomena (all databases prevent against "dirty writes"):
- Dirty Read - reads data that has not yet been committed
- Non-Repeatable Read (Fuzzy) - re-reads data previously read and in the mean .time another committed transaction has modified or deleted it
- Phantom Read - re-execute query and in the mean time another committed transaction has inserted rows that satisfy it.
Use command SET TRANSACTION ISOLATION LEVEL level (or ALTER SESSION SET ISOLATION LEVEL level) so they see a fixed snapshot of the data seen at the beginning of the transaction. Use of have a lot of concurrently running transactions.
Isolation Levels
| Isolation Level |
Dirty Read |
Non-Repeatable Read |
Phantom Read |
| READ UNCOMMITTED |
Possible |
Possible |
Possible |
| READ COMMITTED |
Not Possible |
Possible |
Possible |
| REPEATABLE READ |
Not Possible |
Not Possible |
Possible |
| SERIALIZABLE |
Not Possible |
Not Possible |
Not Possible |
The default isolation level is READ COMIITTED - Oracle doesn't support READ UNCOMMITTED - so never dirty writes or dirty reads. SELECT FOR UPDATE is another method to use. SET TRANSACTION READ ONLY can also be used, but then it also can't INSERT, UPDATE, or DELETE statements unlike level SERIALIZABLE. Will sometimes get error "can't serialize access" if data becomes out of date by a "too-recently committed" transaction - then either commit up to that point, execute additional different statements, or rollback. The amount of concurrent activity a block can support is configured by changing INITTRANS and MAXTRANS in CREATE or ALTER TABLE commands. Can also spread "contentious" data around by increasing PCTFREE. In a distributed environment all servers that participate in a SERIALIZABLE transaction are require to support the transaction isolation mode used. Use READ COMMITTED for high performance, high transaction environments or very low transactions since then there is a very low risk of incorrect results - also don't need error checking and retry code for "can't serialize access" (limit the number of retries so not infinite loop). Use SERIALIZABLKE when have very long running primarily read-only transactions.
