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

A call to a VOLATILE function breaks atomicity of a SELECT statement

Given (PostgreSQL 15.3): create table test ( test_id int2, test_val int4, primary key (test_id) ); If these two transactions are run in parallel at read committed isolation level: -- Transaction 1 insert into test (select 1, 1 from…
Taras Serduke
  • 31
  • 5
2
votes
1 answer

REPEATABLE READ transaction isolation level dangerous for most apps

this question is broader than Rails but this is where most of my observations come from so using it as a basis. I discovered some transient issues in an app I'm working on, where REPEATABLE READ (the default for MySQL) transaction isolation level…
akostadinov
  • 17,364
  • 6
  • 77
  • 85
2
votes
1 answer

Read write problem on an append only table

Suppose I have this append-only SQL table representing deposits/withdraws from/to accounts. The 3 rows below will render the balance of account A to be -$10 ID Account Credit 1 A $100 2 A -$50 3 A -$60 If the inserts are concurrent,…
Xiaoguo Ge
  • 2,177
  • 20
  • 26
2
votes
1 answer

Atomically update flag in Postgres?

I am implementing a lightweight jobs system in my highly-concurrent application. For simplicity, I will be using Postgres to manage the state of all jobs in the system. I have a table where processes can mark specific jobs as "running" via a boolean…
vapor_cafe
  • 21
  • 2
2
votes
1 answer

Concurrently updating a field with READ_COMMITED

Assume we have the following row in a table already: INSERT INTO some_table (id, amount) VALUES (1, 0); Having the following queries running at the same time with READ_COMMITED: INSERT INTO some_table (id, amount) VALUES (1, 0) ON CONFLICT DO …
Dmitry
  • 73
  • 6
2
votes
1 answer

SQL: at REPEATABLE READ isolation level, are UPDATE locks held to the end of the transaction?

I have read about how REPEATABLE READ causes locks held by SELECT statements to be held to the end of the transaction. Is the same true for exclusive locks taken by UPDATE statements? Consequentially, is it the case that when I UPDATE a row in a…
kdt
  • 27,905
  • 33
  • 92
  • 139
2
votes
3 answers

Prevent lost updates with high transaction isolation levels: Is this a common misconception?

I noticed that my applications often write values to a database that depend on a former read operation. A common example is a bank account where a user could deposit money: void deposit(amount) { balance = getAccountBalance() …
fishbone
  • 3,140
  • 2
  • 37
  • 50
2
votes
1 answer

Can I change isolation level in Oracle?

I'm writting a transaction in Oracle. Can I change isolation level within this transaction for one select statement? Now I have Read Commit, and I want within transaction change this once to serializable and next back to the read commit. Best…
GrzesiekO
  • 1,179
  • 4
  • 21
  • 34
2
votes
1 answer

MySQL. What isolation level should be used?

There are many situations where I don't know what kind of isolation I should use in MySQL to avoid using corrupt data. For example, let's say an application to make reservations for a theater. A user selects a seat and, within a transaction, it is…
Alberto
  • 339
  • 4
  • 12
2
votes
1 answer

Hibernate and PostgreSQL: REPEATABLE_READ and use of @Version annotation to avoid write skews and other phenomena

I'm using Isolation.REPEATABLE_READ to copy a large entity graph in a Spring Boot application. It basically executes SELECTs and INSERTs. I also employ optimistic locking using @Version annotation on all of the sub-entities. I see that even if I try…
WilliamZ
  • 23
  • 6
2
votes
3 answers

Serializable Isolation Level Confusion - Write Skew (Postgres)

I'm running Postgres12 and confused about the behavior of the serializable transaction level. Tables: Events id difficulty Managers id level Intended behavior (within serialized transaction): check if there are 7 or more events of…
njz2104
  • 43
  • 6
2
votes
2 answers

Node.js single-thread mechanism

I learnt Node.js is single-threaded and non-blocking. Here I saw a nice explanation How, in general, does Node.js handle 10,000 concurrent requests? But the first answer says The seemingly mysterious thing is how both the approaches above manage to…
yeehaw
  • 159
  • 2
  • 11
2
votes
1 answer

Understanding InnoDB Repeatable Read isolation level snapshots

I have the following table: CREATE TABLE `accounts` ( `name` varchar(50) NOT NULL, `balance` int NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci And it has two accounts in it. "Bob" has a…
Aurast
  • 3,189
  • 15
  • 24
2
votes
1 answer

Default isolation level in Microsoft SQL Server 2005

What is the default isolation level in SQL Server 2005 and in which scenario would you want to change it ? And little explanation about the default isolation level would be appreciated. Thanks in anticipation
Sreedhar Danturthi
  • 7,119
  • 19
  • 68
  • 111
2
votes
3 answers

How to change transaction isolation level globally?

How can I change the default transaction isolation level for the database? The postgres docs show how to change it per transaction and per session - but not how to alter the default for the database or cluster. In MySQL the operation is SET GLOBAL…
Ulad Kasach
  • 11,558
  • 11
  • 61
  • 87
1 2 3
…
48 49