What's new
- Screen sharing
- Quote a message in chat
- Localized time formats
- Support for UI styles
- Report abuse
Isolation levels provide a degree of control of the effects one transaction can have on another concurrent transaction. Since concurrent effects are determined by the precise ways in which, a particular database handle locks and its drivers may handle these locks differently.
Isolation level | Phantom records | Non-repeatable reads | Dirty reads | Deadlocks |
Serializable | - | - | - | + |
Repeatable read | + | - | - | - |
Read committed | + | + | - | - |
Read uncommitted | + | + | + | - |
Strongest level of isolation. Places a range locks on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. Can produce deadlocks.
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom records can be inserted into the data set by another user and are included in later reads in the current transaction.
Can't read uncommitted data by another transactions. Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction resulting in non-repeatable reads and phantom records.
Can read uncommitted data (dirty reads) by another transaction, and non-repeatable read and phantom records are possible. Least restrictive of all isolation levels. No shared locks are issued and no exclusive locks are honoured.
As the transaction isolation level increases, likely performance degradation follows, as additional locks are required to protect data integrity. If the underlying data doesn't require such a high degree of integrity, the isolation level can be lowered to improve performance.
This post is created with OpenOffice.org.