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
0 answers

DeadLock in mysql using Spring TransactionTemplate

I'm using spring transationTemplate , and run some queries by doInTransaction method. I got exception about deadLock, on 2 queries that executed sequentially into the transaction. REPLACE INTO TABLE(A,B,C) VALUES ('AAA','BBB','CCC'); UPDATE TABLE…
Tal Eden
  • 145
  • 1
  • 3
  • 9
3
votes
2 answers

What type of Transaction IsolationLevel should be used to ignore inserts but lock the selected row?

I have a process that starts a transaction, inserts a record into Table1, and then calls a long running web service (up to 30 seconds). If the web service call fails then the insert is rolled back (which is what we want). Here is an example of the…
Jeff Widmer
  • 4,746
  • 6
  • 37
  • 51
3
votes
1 answer

Mysql deadlock on "SELECT ... FOR UPDATE" and insert

I am receiving deadlocks when running this piece of code below. The purpose of the code is to insert a new Title into the Title table with the end result being that I need to set the defaultTitle bit if no other title has the defaultTitle bit set…
Brad Baskin
  • 1,265
  • 2
  • 14
  • 20
3
votes
1 answer

Setting Transaction Isolation Level in .NET / Entity Framework for SQL Server

I am attempting to set the Transaction Isolation Level in .NET/C# for a Transaction. I am using the following code to set up the transaction: using (var db = new DbContext("ConnectionString")) { using (var transaction = new…
log4code
  • 133
  • 1
  • 7
3
votes
1 answer

optimistic lock with a "version" field vs snapshot isolation level

I was wondering what are the advantage/inconvenient for these two optimistic lock solutions : Use a "version" field and detect changes during updates (i.e. using hibernate @Version annotation) Use the Snapshot isolation level on transactions If…
Quentin
  • 3,150
  • 4
  • 24
  • 34
3
votes
1 answer

Why do the concurrent DB connections see each others uncommitted changes though isolation is set to "read committed"?

I am trying to make a few tests to understand how transaction isolation levels can be used to address the various concurrency concerns. I've started with TRANSACTION_READ_COMMITED, but the simplest scenario doesn't behave the way I expect. Here's…
3
votes
1 answer

Change isolation level in individual ADO.NET transactions only

What is the best way to implement different isolation levels for individual transactions when using a client framework, ORM or similar to build queries, which does not support query hints like WITH(NOLOCK)? Imagine an application which uses…
3
votes
3 answers

Oracle transaction read-consistency?

I have a problem understanding read consistency in database (Oracle). Suppose I am manager of a bank . A customer has got a lock (which I don't know) and is doing some updating. Now after he has got a lock I am viewing their account information…
user319280
  • 757
  • 1
  • 8
  • 14
3
votes
1 answer

Cannot use READPAST in snapshot isolation mode

I have a process which is called from multiple threads which does the following: Start transaction Select unit of work from work table with by finding the next row where IsProcessed=0 with hints (UPDLOCK, HOLDLOCK, READPAST) Process the unit of…
3
votes
1 answer

SELECT INTO with SELECT FOR UPDATE in PostgreSQL

Assume I have a message relation where I save messages that was created with this command: CREATE TABLE message ( id serial primary key, foo1 integer, foo2 integer, foo3 text ) And we have a function that gets a message and…
insumity
  • 5,311
  • 8
  • 36
  • 64
3
votes
1 answer

What lock type is used in READ COMMITTED isolation level?

I found a contradiction in a Wikipedia article and not sure where the mistake is (or maybe I don't understand it correctly). According to Wikipedia in Read committed isolation level: "In this isolation level, a lock-based concurrency control…
medvedev1088
  • 3,645
  • 24
  • 42
3
votes
1 answer

MySQL: Transaction isolation levels, deadlocks

I have a long running process which import products and from time to time I get deadlock errors. According to my knowledge I thought that if during script execution I'll switch isolation level to Serializable I will solve an issue with deadlocks.…
3
votes
1 answer

Can't see rows inserted by a running transaction when isolation level is READ_UNCOMMITTED

I have applications that insert rows into table A concurrently. Each application inserts rows in batch mode (using a JDBC prepared statement) using a single transaction per batch (to avoid rebuilding index after each INSERT). The rows present in…
manash
  • 6,985
  • 12
  • 65
  • 125
3
votes
2 answers

DB2 Read committed without locking?

We have a transaction that is modifying a record. The transaction must call a web service, rolling back the transaction if the service fails (so it can't commit it before hand). Because the record is modified, the client app has a lock on it. …
Entropy
  • 1,219
  • 6
  • 21
  • 45
3
votes
2 answers

Scripts running concurrently against table in SQL Server

I have identical Python scripts I need to run on multiple servers all targeting the same table on a DB server. The script takes 5-20 seconds to run, and must run every 5 minutes. Server1 ---> ------------- | DB Table | Server2 ---> …
Jeff
  • 227
  • 1
  • 10