| Lesson 4 || Isolation levels |
| Objective || Define isolation levels for transactions |
Define Isolation Levels for Transactions
An isolation level
is a term that defines how effectively one transaction is isolated from another.
You can control the isolation level in MS SQL Server 2000. There are four possible isolation levels:
- Read Uncommitted: Allows a transaction to read data from the database, whether it has been committed or not. This is the lowest level of isolation.
- Read Committed: Allows a transaction to read data from the database, but only data that has been committed. This ensures that no data will be read by one transaction while another transaction is in the process of updating.
This is the SQL Server default isolation level. It is possible for an update to be lost at this level if two transactions modify exactly the same row, but only if each transaction bases the modification on the values originally retrieved in the transaction.
- Repeatable Read: Similar to Read Committed, except that SQL Server ensures that if a transaction re-queries the same data, no other transaction, whether committed or not,
will negatively affect being able to repeat reading the exact same values. This is because no other transaction can modify data that is contained within your transaction. On the other hand, new rows can be added to the database. This level of isolation only protects existing data.
If another transaction inserts new records, these are called phantom records.
- Serializable: Similar to Repeatable Read, except that this level of isolation protects not only existing data, but no new rows can be added either. Every statement in the transaction occurs serially (or one after another), hence the name.
This isolation level prevents phantom records. This is the highest level of isolation.
Changing the isolation level
You change the isolation level by issuing one of the following four Transact-SQL statements:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL
Transact-SQL statement changes the isolation level for all select statements within a transaction for the session. Therefore, the isolation level remains set until either the connection is terminated or the isolation level is changed again.
The next lesson discusses nested transactions.
How It Works
When you executed the BEGIN TRAN statement, you began writing your work into the transaction log but not into the database. Starting when you begin a transaction, your changes are isolated (within parameters) from other users. What you are doing is visible to you only, and is not really done until you COMMIT.
After you execute the UPDATE statement, you can still query the database as if your change was complete, but only you can see that result. Anyone else attempting to look will get results based on their TRANSACTION ISOLATION LEVEL
They could have to wait for your lock to be released, they could see the old value, or if they are daring they could get a dirty read. Once you ROLLBACK, the database reverts to a state as if you had never started (and woe to the user who got a dirty read).