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

What is the default ADO.NET isolation level?

I know that if I use System.Transactions.TransactionScope and don't specify an isolation level, it will default to Serializable. However, what if I'm not using transaction scope, and am just using an old-fashioned table adapter? What is the default…
Neil Barnwell
  • 41,080
  • 29
  • 148
  • 220
9
votes
4 answers

inno db isolation levels and locking

I am reading a manual about innodb transactions but still, there is lots of unclear stuff to me. For instance, I don't quite understand to the following behaviour: -- client 1 -- client 2 mysql> create table simple (col…
clime
  • 8,695
  • 10
  • 61
  • 82
9
votes
3 answers

InnoDB SELECT ... FOR UPDATE statement locking all rows in a table

MySQL Server version 5.1.41 with InnoDB plugin enabled. I have the following three tables for invoices: invoices, invoice_components and invoice_expenses. Table invoices has invoice_id primary key. Both invoice_components and invoice_expenses are…
Miloš Rašić
  • 2,229
  • 5
  • 24
  • 43
9
votes
2 answers

Does H2 support the serializable isolation level?

Wikipedia describes the Phantom read phenomenon as: A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. It also states…
Andrey Agibalov
  • 7,624
  • 8
  • 66
  • 111
9
votes
1 answer

MySQL repeatable read and lost update/phantom reads

I tried this with MySQL Server 5.5: 1) ensured that transaction isolation level is repeatable_read 2) started shell-1, started a transaction in it, then read a value through select 3) started shell-2, started a transaction in it, then read the same…
shrini1000
  • 7,038
  • 12
  • 59
  • 99
8
votes
2 answers

Why should I use Repeatable Read(or higher) isolation level if I need to read version the second time?

I've read Martin Fowler's book chapter Optimistic Offline Lock Author describes following example(if I understood it correctly): There are 2 entites: Order and Client. There are 2 transactions(business) involved: First transaction calculates…
8
votes
2 answers

Do MySQL transactions for INSERT lock foreign key referenced tables?

I am trying to do a huge transaction in my Java application and doing single insert entries (volume in thousands) for user_account_entry table which has foreign key reference to user table. When the transaction is running, i am not able to update…
Paras Diwan
  • 333
  • 1
  • 5
  • 13
8
votes
1 answer

How can you see what transaction isolation level an arbitrary oracle session is using

I am trying to find out what isolation level a particular session (not my own) has on an oracle server. Is there a v$.. view to get this?
kon5ad
  • 139
  • 1
  • 1
  • 5
8
votes
0 answers

spring nested transactions with different isolation levels

I have two transactional methods A and B. A has isolation level of READ_COMMITTED and B has isolation level of SERIALIZABLE. if B was called inside A, what would be the default behavior here? is spring going to create a new transaction for B or it…
Yahya
  • 321
  • 3
  • 3
8
votes
2 answers

Mixing isolation levels in PostgreSQL

Does it matter for a SERIALIZABLE transaction if any other session uses e.g. autocommit or the READ COMMITED isolation level? In other words is there any danger in mixing isolation levels (& autocommit) when accessing a database from multiple…
Tomasz Zieliński
  • 16,136
  • 7
  • 59
  • 83
8
votes
6 answers

Using NOLOCK Hint in EF4?

We're evaluating EF4 and my DBA says we must use the NOLOCK hint in all our SELECT statements. So I'm looking into how to make this happen when using EF4. I've read the different ideas on how to make this happen in EF4, but all seem like a work…
John
  • 121
  • 1
  • 2
  • 4
8
votes
1 answer

using @Transactional for thread safety

I have a question about how @Transactional annotation alone manages code and transactions execution. Given a correctly setup Spring application and the following code: @Transactional public void withdraw(int amount) { if(isEnoughFunds(amount))…
8
votes
2 answers

Use SqlTransaction & IsolationLevel for lengthy read operation?

I am executing several long-running SQL queries as part of a reporting module. These queries are constructed dynamically at run-time. Depending on the user's input, they may be single or multi-statement, have one or more parameters and operate on…
Bradley Smith
  • 13,353
  • 4
  • 44
  • 57
8
votes
3 answers

Dirty Reads in Postgres

I have a long running function that should be inserting new rows. How do I check the progress of this function? I was thinking dirty reads would work so I read http://www.postgresql.org/docs/8.4/interactive/sql-set-transaction.html and came up with…
User1
  • 39,458
  • 69
  • 187
  • 265
8
votes
2 answers

Using IsolationLevel.Snapshot but DB is still locking

I'm part of a team building an ADO.NET based web-site. We sometimes have several developers and an automated testing tool working simultaneously a development copy of the database. We use snapshot isolation level, which, to the best of my…
Eamon Nerbonne
  • 47,023
  • 20
  • 101
  • 166