Isolation Level (Magic xpa 3.x)
The Isolation level determines what happens during the concurrent (simultaneous) use of the same transaction. The user can change the Isolation level in the database only. Changes made in the DBMS Properties dialog box have an effect on the system.
Three phenomena define SQL Isolation levels for a transaction:
Dirty Reads return different results within a single transaction when an SQL operation accesses an uncommitted or modified record created by another transaction. For example, one user can view the changes made to the data by another user before the transaction is committed. If a rollback occurs, the data viewed will still reflect the change. Dirty Reads increase concurrency, but reduce consistency.
Non-Repeatable Reads return different results within a single transaction when an SQL operation reads the same row in a table twice. Non-Repeatable Reads can occur when another transaction modifies and commits a change to the row between reads within the same transaction. Non-repeatable reads increase consistency, but reduce concurrency.
Phantoms return different results within a single transaction when an SQL operation retrieves a range of data values twice. Phantoms can occur if another transaction inserted a new record and committed the insertion between executions of the range retrieval.
Each Isolation level behaves differently for the situations described above.
|
#
|
Isolation Level
|
Dirty Read
|
Non-Repeatable
|
Phantom
|
|
0
|
Read uncommitted
|
X
|
X
|
X
|
|
1
|
Read committed
|
|
X
|
X
|
|
2
|
Repeatable read
|
|
X
|
X
|
|
3
|
Can be serialized
|
|
|
|
Read Committed: Specifies that 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 or phantom data. This option is the SQL Server default.
Can Be Serialized: Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four Isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
When the database has an Isolation level of 0, all locks will be granted to this session. No user will wait, even if a table is locked exclusively. This Isolation Level setting allows for maximum concurrency, but low data integrity. Depending on the data structure of your project, this Isolation level maximizes concurrency but to the detriment of data integrity. DB2 and MSSQL support Isolation level = 0, but Oracle does not.
Oracle supports Isolation level = 1 for a user’s request to read data that is locked by another user. Oracle will display the previously unmodified data to this user, data that has been committed.
