Questions tagged [isolation-level]

Isolation level defines what data an SQL transaction can view or access while other transactions work with the same data.

ACID standard defines 4 isolation levels - read uncommitted, read committed, repeatable read and serializable. The higher the isolation level is, the more is guaranteed that another transaction can't break yours, but the lower amount of concurrency the database can handle. MySQL and MSSQL support all isolation levels, while PostgreSQL and Oracle support only the 2 most common, read committed and serializable

Read uncommitted means that the transaction works with the latest data available. In this isolation level it's possible that a transaction reads data that is not yet committed and possibly will be rolled back and never exist.

Read committed is the most basic isolation level, which ensures that transactions only read data that is already saved. It is possible however for another transaction to modify the data after the first transaction has read it but before it has modified it.

Repeatable read ensures that any subsequent read of the data will return the same result as the first read, therefore eliminating the race condition described above.

Serializable ensures that transactions are run in such a way that the result is the same as they were run in sequence, not in parallel.

725 questions
362
votes
9 answers

Difference between "read commited" and "repeatable read" in SQL Server

I think the above isolation levels are so alike. Could someone please describe with some nice examples what the main difference is?
Fore
  • 5,726
  • 7
  • 22
  • 35
269
votes
10 answers

Why use a READ UNCOMMITTED isolation level?

In plain English, what are the disadvantages and advantages of using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in a query for .NET applications and reporting services applications?
Kip Real
  • 3,319
  • 4
  • 21
  • 28
250
votes
12 answers

What is the difference between Non-Repeatable Read and Phantom Read?

What is the difference between non-repeatable read and phantom read? I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom…
198
votes
6 answers

How to find current transaction level?

How do you find current database's transaction level on SQL Server?
dance2die
  • 35,807
  • 39
  • 131
  • 194
143
votes
3 answers

How to detect READ_COMMITTED_SNAPSHOT is enabled?

In Microsoft SQL Server, is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON;? I cannot find a simple way to detect this in either T-SQL or via…
Chris Driver
  • 2,245
  • 2
  • 19
  • 14
123
votes
4 answers

Transaction isolation levels relation with locks on table

I have read about 4 levels of isolation: Isolation Level Dirty Read Nonrepeatable Read Phantom Read READ UNCOMMITTED Permitted Permitted Permitted READ COMMITTED -- Permitted …
lowLatency
  • 5,534
  • 12
  • 44
  • 70
91
votes
10 answers

How long should SET READ_COMMITTED_SNAPSHOT ON take?

How long should it take to run ALTER DATABASE [MySite] SET READ_COMMITTED_SNAPSHOT ON I just ran it and it's taken 10 minutes. How can I check if it is applied?
Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689
84
votes
5 answers

Read committed Snapshot VS Snapshot Isolation Level

Could some one please help me understand when to use SNAPSHOT isolation level over READ COMMITTED SNAPSHOT in SQL Server? I understand that in most cases READ COMMITTED SNAPSHOT works, but not sure when go for SNAPSHOT isolation. Thanks
user173552
  • 1,169
  • 2
  • 12
  • 13
82
votes
3 answers

Why is System.Transactions TransactionScope default Isolationlevel Serializable

I am just wondering what a good reason to use Serializable as the default Isolationlevel may be when creating a System.Transactions TransactionScope, because I cannot think of any (and it seems that you cannot change the default via web/app.config…
Bernhard Kircher
  • 4,132
  • 3
  • 32
  • 38
65
votes
5 answers

What is (are) difference between NOLOCK and UNCOMMITTED

I use SQL Server 2012. I write two queries but what is a different between NOLOCK and UnCommitted ? SELECT lastname, firstname FROM HR.Employees with (READUNCOMMITTED) SELECT lastname, firstname FROM HR.Employees with (NoLock)
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
42
votes
6 answers

Transaction Isolation Level Scopes

What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality…
SurroundedByFish
  • 2,900
  • 2
  • 22
  • 17
37
votes
4 answers

How to set transaction isolation level (MySQL)

How do I set the isolation level of MySQL 5.1 InnoDB? By entering: mysql> show variables like '%isola%'; The default level set for InnoDB is repeatable read. How do I change the isolation level?
Wen Jun
  • 522
  • 3
  • 9
  • 19
30
votes
4 answers

set isolation level for postgresql stored procedures

Hopefully a simple question, but one for which I haven't readily found a decent answer. I'm reliably informed that stored procedures (user-defined DB functions) in PostgreSQL (specifically, version 9.0.4) are inherently transactional, inasmuch as…
beldaz
  • 4,299
  • 3
  • 43
  • 63
30
votes
2 answers

BeginTransaction with IsolationLevel in EF Core

I'm trying to rewrite old library to use EntityFramework Core and I can't figure out how to begin transaction with specific isolation level. Previously I was able to do something like…
Pavel
  • 1,015
  • 3
  • 13
  • 27
30
votes
8 answers

How to produce "phantom read" in REPEATABLE READ? (MySQL)

Using "repeatable read", it should be possible to produce a phantom read, but how? I need it for an example teaching CS-students. I think that I must make a "SELECT ... WHERE x<=888" on a non-indexed field x, with an upperlimit 888 not present, and…
Erik
  • 4,120
  • 2
  • 27
  • 20
1
2 3
48 49