Stack Overflow
Stack Overflow

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

What specific exceptions represent a serialization failure when Django is using serializable transaction isolation level with postgresql?

Sometimes it's desirable to use a higher isolation level than the default "read committed" for database operations in Django. The docs warn that: Under higher isolation levels, your application should be prepared to handle exceptions raised on…
user85461
  • 6,510
  • 2
  • 34
  • 40
6
votes
1 answer

How do I set transaction isolation level in Laravel 5.5?

In laravel 5.5 with MySQL I use \Illuminate\Support\Facades\DB to create transactions this way: DB::transaction(function() { ... }); What is the isolation level for such transaction and is there a way to set it explicitly?
Nikolay 'Alagunto' Tkachenko
  • 813
  • 8
  • 19
6
votes
1 answer

How Pessimistic lock works in database,does Isolation level has to do any thing with it?

I was reading about database locking(pessimistic,optimistic) mechanism, session 1: t1: open transaction: t2: sleep(3 sec) t5: updte user set name='x' where id =1 session 2: t2:update user set name='y' where id=1 my doubts are: 1. What will happen…
Nishat
  • 881
  • 1
  • 17
  • 30
6
votes
3 answers

Default isolation level for transaction (@atomic) with Django and PostgreSQL

I was wondering what's the default isolation level when using Django with PostgreSQL. Serializable Isolation? (https://www.postgresql.org/docs/9.1/static/transaction-iso.html#XACT-SERIALIZABLE) There is a discussion about MySQL (Django transaction…
Alex Vyushkov
  • 650
  • 1
  • 6
  • 21
6
votes
1 answer

Sql client transactions from code vs database-controlled transactions

I've always done transactions from within stored procedures but now I need to wrap a bunch of "dynamic" statements executed from code against sp_executesql in a transaction. Specifically I need the READ UNCOMMITED isolation level for these in some…
kprobst
  • 16,165
  • 5
  • 32
  • 53
6
votes
1 answer

SQL Server Trigger Isolation / Scope Documentation

I have been looking for definitive documentation regarding the isolation level ( or concurrency or scope ... I'm not sure EXACTLY what to call it) of triggers in SQL Server. I have found the following sources which indicate that what I believe is…
reidLinden
  • 4,020
  • 4
  • 31
  • 46
6
votes
1 answer

MySQL Workbench session does not see updates to the database

I have MySQL Workbench (community-6.2.3) installed in a Ubuntu system using .deb. Workbench session does not seem to see updates (DML) to the database done by other sessions (applications/command line client). A new session is able to see correct…
Sithsu
  • 2,209
  • 2
  • 21
  • 28
6
votes
1 answer

Transaction isolation levels and subqueries

if we have an UPDATE with a sub-SELECT, can the subquery execute concurrently or not under READ COMMITTED isolation? In other words, is there a race condition present in the following: update list set [state] = 'active' where id = (select top 1…
The Dag
  • 1,811
  • 16
  • 22
6
votes
1 answer

TransactionScopeAsyncFlowOption and Isolation Level?

I'm using Entity Framework 6.1 in a WCF service and wanted to surround my SELECT query with a READ UNCOMMITTED Isolation level since other batch updates will be inserted into the table I'm reading and don't want to lock those batch updates from…
sagesky36
  • 4,542
  • 19
  • 82
  • 130
6
votes
6 answers

Django transaction isolation level in mysql & postgresql

Do you know the default isolation level of the transactions used in Django? Is it possible to set the isolation level in the database independent way? I'm mainly interested in mysql and postgres.
Piotr Czapla
  • 25,734
  • 24
  • 99
  • 122
6
votes
2 answers

Oracle equivalent of SQL Server Snapshot isolation

In Microsoft SQL Server, I use the READ_COMMITTED_SNAPSHOT ISOLATION ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON In Session 1,update the Principal from 4000 to 5000 BEGIN…
VenVig
  • 645
  • 1
  • 10
  • 14
6
votes
1 answer

Can multiple threads cause duplicate updates on constrained set?

In postgres if I run the following statement update table set col = 1 where col = 2 In the default READ COMMITTED isolation level, from multiple concurrent sessions, am I guaranteed that: In a case of a single match only 1 thread will get a…
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
6
votes
2 answers

How to specify default transaction isolation level in Grails

I cannot figure out how to specify default transaction isolation level in Grails application . Please help and point where my mistake is. Following are the details. Grails: 1.3.7 Database: Sql Server 2008. DataSource.groovy: dataSource { ... …
Andrey.Kozyrev
  • 579
  • 8
  • 17
5
votes
1 answer

Sql Notification Supported Isolation Levels for Transactions

I am running multiple inserts using transactions. I am using the SqlDependency class to let the client machine know when the server has been updated. The problem I am having is that whenever I insert using a transaction, no matter what isolation…
Stuart
  • 801
  • 1
  • 9
  • 18
5
votes
1 answer

Transactions, locks, isolation levels

I have a few questions regarding subject from the title. First of all, lets assume that we work with JDBC, and there we have 2 transactions T1 and T2. In T1 we execute select statement on one particular row. Then we execute update on that row. In…
Kovasandra
  • 555
  • 1
  • 6
  • 15
1 2 3
…
48 49