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

Phantom read in a SELECT / UPDATE scenario

Pseudocode: var data = ExecuteMSSQLQuery( "SELECT Id FROM Table WHERE Status='not_processed'"); if(ProcessData(data)) { ExecuteMSSQLQuery( "UPDATE Table SET Status='processed' WHERE Status='not_processed'"); } I want to make sure that…
Jefim
  • 3,017
  • 4
  • 32
  • 50
0
votes
0 answers

DB2 select obtain deadlock with concurrent batch process (delete/insert) on the same table

I'm developing 2 applications, one backend and a frontend web application, over DB2 as dbms. The web application access at the db through services exposed by the backend, that is the only who can modify data on the tables. In the backend application…
blkid
  • 21
  • 6
0
votes
2 answers

locking the same table twice

I am using SELECT...FOR UPDATE queries to lock a table. Unfortunately, I have a situation where I need to lock two different sets of rows in the same table, like so: SELECT * FROM mytable WHERE attribute1 = 'something' FOR UPDATE SELECT * FROM…
Igor Serebryany
  • 3,307
  • 3
  • 29
  • 41
0
votes
2 answers

SQL Server Isolation Level issues

I looked at all the isolation types. But I could not find the mode I wanted. It can be read by other transaction during the transaction. But, it will not add update and delete data. For example (pseudo code): create table abc (id uniqueidentifier…
Nazlı
  • 3
  • 4
0
votes
0 answers

MariaDB isolation levels

I am working on MariaDB.I have a scenario of 2 sessions working in parallel and understand their actions and things i get when i use SELECT COUNT(). However, in the last SELECT COUNT() i get 6 as a count, but i expected a 5. I believe my problem…
Tasoulis Livas
  • 75
  • 1
  • 1
  • 9
0
votes
1 answer

unique key exception on multiple inserts/updates

I have application that receives multiple requests from external sources (invoices from point-of-sale units). It gets tens of requests per second, and some of those requests are the same (have same request body). request data is transformed and…
Mario Tadić
  • 13
  • 1
  • 4
0
votes
1 answer

non-repeatable read vs. phantom read

I know that A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. and A phantom read occurs when, in the course of a transaction, two identical queries…
La Carbonell
  • 1,976
  • 5
  • 22
  • 52
0
votes
2 answers

Different Behavior between SQL Anywhere 9 and 16

I'm having trouble figuring out SQL Anywhere 16 behavior compare to Sybase 9 they both has an identical database set as dirty read or set transaction isolation level 0 Even from an Delphi application (using TAsaSession) or thru SQL_anywhere_XX, I´m…
Marisco
  • 125
  • 1
  • 3
  • 16
0
votes
2 answers

IsolationLevel, Lock Modes, deadlocks and sp_getapplock in MS SQL Server

I am new to SQL server and the deadlocking like issues. I read articles about it. I want to understand following things: SQL server uses appropriate Lock Mode depending on the IsolationLevel I set while beginning the transaction. If this…
Learner
  • 4,661
  • 9
  • 56
  • 102
0
votes
0 answers

Repeatable read and lock compatibility table

I think I have understood the answers from that question : Repeatable Read - am I understanding this right? but I have a complementary question for the repeatable read scenario. It is said : REPEATABLE READ 1 SELECT -- places a shared lock and keeps…
epanu
  • 1
  • 1
0
votes
1 answer

How do I isolate transactions in Apache Ignite

I'm trying to synchronize access to an object stored in Ignite using transactions, and finding that the results of one transaction often overwrite the results of another. I've written up a simpler version of what I'm trying to do as a JUnit test for…
F. Dall
  • 28
  • 4
0
votes
0 answers

Repeatable read islation level sql

I have some problems in understanding sql isolation level. I have table R with attribute A and values 1 and 2 T1: UPDATE R SET A=2*A INSERT INTO R VALUES(6) T2: SELECT AVG(A) FROM R SELECT AVG(A) FROM R What will be the result if the second…
Darius Ionut
  • 49
  • 8
0
votes
1 answer

SQL Optimistic Concurrency - Involving several tables across db

Imagine scenario where we have Products table where we define ProductId, Name, Price. Also, we have InvoicesHeader and InvoicesLines tables which gets filled as users create Invoices and add items to it. InvoicesLine table had reference to Products…
dee zg
  • 13,793
  • 10
  • 42
  • 82
0
votes
1 answer

Does MySQL Queue Inserts/Updates?

Suppose I have an update query such as: UPDATE accountstable SET bal = bal - 0.5 where bal >= 0.5 and id = 1 which subtracts 0.5 from an users account only if its balance is greater or equal to 0.5. If the query is run twice, with both instances…
Oo Dee
  • 145
  • 1
  • 11
0
votes
2 answers

ISOLATION LEVEL SERIALIZABLE locking postgresql 9.6

DO $$ BEGIN raise notice '%', (SELECT * from public.clientcalledthisfunction(1,2)); END $$; CREATE OR REPLACE FUNCTION public.clientcalledthisfunction(userid1_ integer, userid2_ integer) RETURNS integer AS $$ DECLARE result…
sommeguyy
  • 77
  • 1
  • 8
1 2 3
…
48 49