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
3
votes
1 answer

SERIALIZATION FAILURE (40001)

I already know that transactions at the serializable level should be retried in case of a serialization failure. I'm just curious about 2 scenarii where a 40001 error is raised : When running an INSERT command. When issuing a PREPARE TRANSACTION…
anon
3
votes
0 answers

Is there any way to set default isolation level of EF Code First(earlier versions than 6) to READ_COMMITTED_SNAPSHOT?

According this link in Entity Framework web site, default transaction isolation level of EF6 changes to READ_COMMITTED_SNAPSHOT. Default transaction isolation level is changed to READ_COMMITTED_SNAPSHOT for databases created using Code First,…
3
votes
3 answers

Sql isolation levels, Read and Write locks

A bit lame question but I got confused... Difference between isolation levels as far as I understood is how they managed their locks (http://en.wikipedia.org/wiki/Isolation_(database_systems)). So as mentioned in the article there are Read, Write…
EnTrERy
  • 151
  • 2
  • 3
  • 11
3
votes
2 answers

Keep SELECT from retrieving rows commited after the start of the transaction

Given this scenario: TIME TRANSACTION 1 TRANSACTION 2 1sec BEGIN 2sec INSERT RECORDS INTO T1 3sec (doing other things) BEGIN 4sec (doing other things) (doing other things) 5sec COMMIT (doing other…
Christian
  • 7,062
  • 9
  • 53
  • 79
3
votes
1 answer

java.sql.Connection Isolation Level

I am writing some spike code that isn't giving me the the results I am expecting. I have a table that is basically rows of counters. Other tables use these rows to generate what should be unique ID's. When I run the code below what I excepted is…
Medu
  • 135
  • 2
  • 10
3
votes
2 answers

Database for long running transactions with huge updates

I build a tool for data extraction and transformation. Typical use case - transactionally processing lots of data. Numbers are - about 10sec - 5min duration, 200-10000 row updated (long duration caused not by the database itself but by outside…
Alex Craft
  • 13,598
  • 11
  • 69
  • 133
3
votes
1 answer

What do I expect from changing default transaction isolation level from READ_COMMITTED_SNAPSHOT to READ_COMMITTED?

In SQL Server the default isolation level is READ_COMMITTED but in SQL Azure the default level is READ_COMMITTED_SNAPSHOT. Suppose I change the default level in my SQL Azure server to READ_COMMITTED_SNAPSHOT (using SET TRANSACTION ISOLATION LEVEL)…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
3
votes
2 answers

SQL Server 2012 insert block reads

I have a big table in SQL Server 2012, it contains about 34 million records. I have a batch program that insert/updates the table every 5 minutes, and I have a web application that reads from the table any time. Every time the batch program is…
3
votes
2 answers

Can I set the JDBC isolation level from a Tomcat Context?

I have a web application running in Tomcat 6, and I've managed to configure it to use the built-in DBCP connection pooling, and all is working very well, however I suspect it is running in the wrong isolation level on the database. I'd like it to…
banjollity
  • 4,490
  • 2
  • 29
  • 32
3
votes
3 answers

SQL Server snapshot isolation level issue

I am studying snapshot isolation level of SQL Server 2008 from the below link. My confusion is, http://msdn.microsoft.com/en-us/library/ms173763.aspx It is mentioned "Data modifications made by other transactions after the start of the current…
George2
  • 44,761
  • 110
  • 317
  • 455
3
votes
1 answer

How to check isoloation level?

How do I check what IL is being set in my oracle DB. How do I change it? Many thanks in advance.
groovydallas
  • 31
  • 1
  • 3
2
votes
3 answers

What's the lowest isolation required to ensure only one SQL UPDATE makes a change?

Here's a SQL UPDATE command with parameters... UPDATE MyTable SET MyField = @newvalue, @success = 1 WHERE Id = @id AND MyField = @oldvalue Many clients will be running this command with the same @id and @oldvalue parameter values…
billpg
  • 3,195
  • 3
  • 30
  • 57
2
votes
1 answer

MySQL Isolation levels, Measuring their impact on deadlocks

I'm trying to generate a few graphs using the sysbench benchmark (default configuration) trying to show the relationship between deadlocks and isolation level in MySQL. But I get some strage results: I was under the impression that repeatable read…
user1018513
  • 1,682
  • 1
  • 20
  • 42
2
votes
1 answer

SQL Server 2008 stored proc running concurrently causing delays

I have a problem where it seems that when running the same stored procedure at the same time from two different instances of the same application it is timing out, and wondered if there was anything I could do to resolve it? I believe the problem…
2
votes
6 answers

Two threads reading from the same table:how do i make both thread not to read the same set of data from the TASKS table

I have a tasks thread running in two separate instances of tomcat. The Task threads concurrently reads (using select) TASKS table on certain where condition and then does some processing. Issue is ,sometimes both the threads pick the same task ,…
Sudhakar
  • 4,823
  • 2
  • 35
  • 42