Low isolation levels do allow for more users to be able to access the data so you can get greater concurrency, but they can also affect data integrity by creating the effect of lost updates and dirty reads.Dirty reads are a phenomenon where you read uncommitted data. That data could be wrong, because its transaction hasn’t been completed yet.
Higher isolation levels, like SERIALIZABLE, can greatly increase blocking, and this can be fairly unpredictable, because blocking is dependent on how many users you have concurrently accessing the system.
A long-running transaction can stay open for minutes or even hours, and all the other transactions just have to wait until they either time out or just stay waiting on that other blocking transaction.
Isolation levels are set at the transaction and session level. You can actually set this at the instance level as well. SQL Server has a default of READ COMMITTED. So if you don’t specify anything else, your transactions or sessions will use that isolation level.
READ UNCOMMITTED
- Equivalent of using NOLOCK and this
- Allows for dirty reads, reading data that has not been committed.
- You pretty much never want to use this in any sort of production application.
- It’s a common myth among developers that NOLOCK makes queries faster. It really doesn’t; in fact, it doesn’t even guarantee that no locks are taken.
READ COMMITTED
- Default isolation level in SQL Server.
- It will not allow for dirty reads, so if you have a pending transaction, you’ll not be able to read that.
REPEATABLE READ
- Goes a step beyond READ COMMITTED, guaranteeing that data read during an individual transaction that data will not change in the scope of a transaction.
- Select statement acquires a really long lock on reading that data until the read transaction is completed.
- New rows may be added under this isolation level, but those rows are unchangeable.
SERIALIZABLE
- Strongest isolation level, and will incur locks very aggressively, and will cause probably a great deal of resource contention.
- Takes REPEATED READ one step further in not allowing any changes to the view of your data in a read transaction
- You would also have a major locking problem, because the SERIALIZABLE isolation level would block every other concurrent transaction.
Example:
BEGIN TRAN
ROLLBACK or COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED – Will display data only when the the transaction has ended otherwise it will wait.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED – Will display uncommitted data right away.
Sources:
https://www.linkedin.com/learning/sql-server-performance-for-developers/transaction-isolation
Comments