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

SQL Server locking for an UPDATE with WHERE clause and sub-SELECT

It seems that this topic is asked often, and I think I found an answer to my original question, but now I'm curious about a difference between that (and a few other answers I found) and my SQL: update Foos set Owner = 'me' OUTPUT INSERTED.id where…
Josh
  • 6,944
  • 8
  • 41
  • 64
0
votes
0 answers

SQL SELECT and ISOLATION LEVEL

I've been asked for a school exercise to write the following query: SELECT ACCOUNT_ID, AVG(AMOUNT) FROM ACCOUNTS A INNER JOIN TRANSACTIONS T ON A.ACCOUNT_ID = T.ACCOUNT_ID GROUP BY ACCOUNT_ID The focus of the exercise is to perform the whole…
Shepard
  • 801
  • 3
  • 9
  • 17
0
votes
1 answer

Protecting against transaction concurency (Transaction type; Transaction IsolationLevel)

Middle-tier component will execute the data access routines in application. The component will call several SQL Server stored procedures to perform database updates. All of these procedure calls run under the control of a single transaction. The…
vts123
  • 1,736
  • 6
  • 27
  • 41
0
votes
2 answers

READ UNCOMMITTED and INNER JOIN: can a query failed?

I have two tables in InnoDB: user (id PRIMARY KEY, name VARCHAR(255)) key (id PRIMARY KEY, key INTEGER, a_id FOREIGN KEY REFERENCES user.id) One user can have multiples keys. To display results, I do joins like: SELECT k.id, k.`key`, u.id,…
NicolasCanac
  • 57
  • 1
  • 5
0
votes
1 answer

guidance please with usage of transactions

I seek some guidance regarding transactions. The case: I have table with a few regular fields and 2 foreign keys. There's a unique index over the 2 foreign keys. The table has several 100.000's records. Two threads: Thread 1 is some maintenance…
0
votes
2 answers

Save an entity with a one-to-many relationship using C# and TSQL - race conditions

I have an entity with a one-to-many relationship as follows public class Car { public int ID; public string Color; public List Passengers; } public class Passenger { public int ID; public string Name; } This entity…
Justin J Stark
  • 490
  • 1
  • 8
  • 17
0
votes
2 answers

how to pass a connection to Mybatis

in a project I need to set the transaction isolation level to SNAPSHOT, I know that it is possible to set it in java with System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: Execute a…
0
votes
1 answer

How to improve code to safely process in READ COMMITTED isolation level in Oracle?

I wondered if there is anyway to avoid corruption of data in READ COMMITTED isolation level. Here is a sample of my issue: two sessions working with the same tables. SSN1> ALTER TABLE APPLICANT ADD( AVGSLEVEL2 NUMBER(5,2) ) Meanwhile in another…
Bernard
  • 4,240
  • 18
  • 55
  • 88
0
votes
1 answer

How to set Isolation levels with java jersey?

I am using Java Jersey https://jersey.java.net/ for a simple project with a MySQL DB. I found how to set the lockmode but I would like to also know how to set the transactions isolation levels. I can't find it anywhere ! rXp
rXp
  • 617
  • 2
  • 12
  • 25
0
votes
1 answer

DB2 Cursor Stability - deadlocks

I've always thought that READ_COMMITTED (DB2 calls it Cursor Stability (CS)) meant that you do NOT lock on reads, and that you only read the committed data. A situation just came up that makes me realize that DB2 is locking, albeit very briefly, on…
Entropy
  • 1,219
  • 6
  • 21
  • 45
0
votes
2 answers

SQL Transaction Oddity with concurrent processes from C#

We have a C# system throwing up an oddity we can't get to the bottom of with SQL (SQL2k5). The situation is that we have two separate processes running simultaneously looking at the same table, both running inside their own transaction in two…
Rob
  • 1
  • 1
0
votes
1 answer

Change isolation level in command line shell in sqlite

I would like to change the isolation level in SQLite. Can I do this in the command line shell?
ericj
  • 2,138
  • 27
  • 44
0
votes
1 answer

How to read uncommitted data in nHibernate transaction?

I would like to know to know how to read uncommitted data. I have an application which is saving an entry into table1 and then tries to read some entry from same table. I am unable to do that because the data gets locked between begin transaction…
Arti
  • 2,993
  • 11
  • 68
  • 121
0
votes
1 answer

Conflict serializability and why 2 reads are not conflicting?

From what I read to analyze transactions the basic approach is the concept of conflict serializable. So the database system must ensure that the scheduling of transaction is equivalent to a serializable via this approach. But I can not see how this…
Jim
  • 18,826
  • 34
  • 135
  • 254
0
votes
1 answer

SQL Server - Same query, same database, different results

I have a database in SQL Server 2008 with 2 tables. I'm running the following query on Management Studio: select M.FD_BROWSER_SHORT_NAME AS X, Count(1) AS Y from TB_EVENTS E INNER JOIN TB_MACHINES M ON E.FD_ID = M.FD_ID AND E.FD_ID_NET =…
walteram
  • 382
  • 1
  • 4
  • 9