Questions tagged [isolation-level]

For questions about "isolation levels" - the setting that specifies the applicable guarantees of concurrency and consistency in multi-user database systems.

Filter by
Sorted by
Tagged with
-4 votes
2 answers
59 views

What do developers need to know about Read Committed Snapshot Isolation? [closed]

My developers have only ever worked with Read Committed, but my new server has Read Committed Snapshot Isolation (RCSI) enabled. What do they need to do differently when writing application code?
J. Mini's user avatar
  • 93
0 votes
0 answers
44 views

Are SERIALIZABLE deadlocks avoided or just detected?

SERIALIZABLE is the highest standard isolation level. It avoids dirty reads, phantoms,... by ensuring that, whatever happens, the ultimate result of a transaction schedule is equal to the result of a ...
A. Darwin's user avatar
  • 119
1 vote
1 answer
37 views

Is READ UNCOMMITTED equivalent to non-ACID (BASE) transaction management?

Relational databases are often associated to the ACID properties, which "protect" data but may hinder performance, even on a single node. Isolation levels (the I in ACID) shield transactions ...
A. Darwin's user avatar
  • 119
2 votes
1 answer
297 views

In Postgres, can an isolation level be set as mandatory?

At https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation, it says Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. ...
Andrew's user avatar
  • 123
-1 votes
1 answer
137 views

Single-statement transactional consistency

I ran into this Q&A on StackOverflow the other day. To quote comments from the question author, the situation seems "somewhat horrifying". A similar situation is set up in this Q&A. ...
Steve's user avatar
  • 319
0 votes
1 answer
128 views

Do READ ONLY transactions ever cause serialisation anomalies (or, is SERIALIZABLE ever useful for READ ONLY)?

I am using PostgreSQL. I had attempted to build a system that utilized SERIALIZABLE transactions exclusively, in the expectation that it would simplify my application development. I had marked ...
user22476690's user avatar
0 votes
2 answers
51 views

Changing Isolation Level after retiring database from active/production use

We have a product that uses SQL Server (currently 2019). We're moving to a different product, but will keep the database from the old product around for several years for reporting purposes. Part of ...
Joel Coehoorn's user avatar
0 votes
1 answer
71 views

Rolling back a transaction if a read would return a different value at commit time

I want a certain behavior for transactions in Postgresql, but I can’t tell if it is possible. I would describe this behavior as an “Optimistic Read Lock”, but that does not seem to be the right term ...
Labs's user avatar
  • 3
1 vote
2 answers
191 views

Locking/Isolation - MariaDB

In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB. I also have a situation where I need to ...
Daniel Benzie's user avatar
0 votes
1 answer
187 views

How does MVCC work under read committed isolation level in Postgres

Let's say I have two transactions A and B. Transaction A starts first. then transaction B starts, inserts some data, and commits. So from transaction A (not committed yet), I can see data were ...
Quốc Khánh Bùi's user avatar
0 votes
0 answers
51 views

How to check Default Isolation Level at server/instance level

We have installed SQL Server 2017 in 2 servers. Then created 2 databases same way in both servers. In one server I see my database snapshot isolation level is ON and another server its OFF. So, how ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
1 answer
75 views

EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON

I am running this query, SELECT name , snapshot_isolation_state , is_read_committed_snapshot_on FROM sys.databases s name snapshot_isolation_state is_read_committed_snapshot_on master ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
1 answer
38 views

Prohibit requesting of transation isolatoin level below specified one in Postgres

Is there a way to prohibit usage of isolaton levels below specified one in Postgres? I want isolation level of all transactions in DB to be a REPEATABLE READ at least, and raise error if user tries to ...
Nikita Glukhov's user avatar
0 votes
1 answer
136 views

How do two transactions with different levels of isolation interact with each other?

this is specifically for SQLserver: I want to know if I have a transaction with isolation level SERIALIZABLE and another one with READ UNCOMMITTED how will they interact concurrently? will the second ...
Achraf Cheikh Mohamed's user avatar
3 votes
0 answers
591 views

Does Serializable isolation eliminate the need for explicit locks?

Postgres documentation on Serializable isolation level counterposes Serializable with explicit locking, and makes it sound as though explicit locking should not be needed when using Serializable. I'm ...
Kit Isaev's user avatar
0 votes
1 answer
162 views

What is the transaction isolation level for SP_EXECUTE_REMOTE

The system I am administering is deadlocking very frequently on a specific stored procedure. The application is made up of an Azure SQL Database (ASD) that is connected to a web application. The ASD ...
tommyfly's user avatar
  • 191
2 votes
1 answer
651 views

How two phases locking ensures serializable?

I have read many sources, and they say one of the ways to implement serializable in the database is to use two-phase locking. But I don't really understand how two-phase locking could ensure ...
Quốc Khánh Bùi's user avatar
3 votes
1 answer
432 views

MySQL Locking and Isolation Level

Could someone help me to understand this MySQL behavior? I was trying to add a new column on a table using this statement: ALTER TABLE `tb_example` ADD COLUMN `score` INT NULL, ALGORITHM = ...
viniciuswebdev's user avatar
1 vote
1 answer
265 views

SQLite database isolation level on the same connection

SQLite documentation https://www.sqlite.org/isolation.html states that on the same connection: "there is no isolation between operations that occur within the same database connection." As ...
Kurovsky's user avatar
  • 113
0 votes
1 answer
409 views

Phantom read during single statement execution in PostgreSQL

I wonder could there ever be a concurrent situation when phantom read occurs during single SQL statement execution? Given: tx_test is a table with 3 rows and id column is primary key, value is some ...
peremeykin's user avatar
3 votes
1 answer
2k views

HOLDLOCK required in MERGE with UPDATE

Within a .NET 6 application the EntityFramework is used to keep track of entities and to update related fields. To improve bulk updates, the EfCore.BulkExtensions package has been added. During a ....
Odrai's user avatar
  • 143
0 votes
1 answer
645 views

Postgres Repeatable Read vs Selecting rows that are changed by another ongoing transaction

Let's say I have a set of select statements that query important field values in a for-loop. The goal is to make sure that the rows are not updated by any other transaction so that this set of selects ...
Don Draper's user avatar
3 votes
0 answers
176 views

Missed row when running SELECT with READCOMMITTEDLOCK

I have a T-SQL code that delta-copies data from the source table (SrcTable) to the destination table (DestTable). The data is inserted into the source table by multiple sessions and copied to the ...
SerMal's user avatar
  • 31
5 votes
2 answers
2k views

Understanding of SQL Server version store

I'm trying to understand SQL Server version store and related isolation levels. As I understand it, when a database enabled read committed snapshot option, this could happen: An item (id = 1) has ...
Fajela Tajkiya's user avatar
1 vote
2 answers
61 views

Concurrency Control B-Tree between Hash

I am new to transaction. When I read the book "Transactional Information Systems", it is mentioned that B-tree uses key-range locking to guarantee serializability. Is that true all accesses ...
GenGG's user avatar
  • 15
5 votes
1 answer
1k views

Is a serialization anomaly only possible with SUM/COUNT?

I have read Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL and especially the part "Serialization anomaly in Postgres". I think I've understood the problem ...
Martin Thoma's user avatar
1 vote
1 answer
290 views

A query, safe against concurrent writes with READ COMMITTED, also safe with SERIALIZABLE isolation level?

I have asked this question with a wonderfully informative answer: How to have uniqueness constraints for structures across multiple tables? The answer from Erwin Brandstetter suggests this: WITH ...
Lance's user avatar
  • 199
0 votes
1 answer
2k views

PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish

We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
K. Anye's user avatar
  • 13
3 votes
1 answer
610 views

How do databases guarantee that two transactions with different isolation levels run concurrently correctly

I wonder how databases guarantee that two transactions with different isolation levels run concurrently correctly. That is, different sessions are allowed to use different isolation levels. For ...
user18676624's user avatar
2 votes
1 answer
799 views

Reading from table being created in transaction

I would like to view data in a table that is undergoing a transaction. I will use this super simple temporary table as an example: SELECT 5 AS NUMBER INTO ##temptable I will create a transaction to ...
Mads's user avatar
  • 31
-1 votes
1 answer
373 views

Understanding read uncommitted isolation level's relation with clustered index and page splits

Missing one or more rows that were not the target of update When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if ...
variable's user avatar
  • 3,561
-1 votes
2 answers
116 views

How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

I understand that the read committed snapshot isolation level holds the row versions in the tempdb. I enabled this feature on a test database and I was expecting to see a new column (guid) get ...
variable's user avatar
  • 3,561
3 votes
2 answers
779 views

Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE

I have recently stumbled upon a seemingly odd behavior of SELECT ... FOR UPDATE when combined with (LEFT) JOIN. Here is the table structure as well as a scenario to reproduce the result: Table ...
auxdevelopment's user avatar
1 vote
1 answer
298 views

Serializable isolation fails even for unrelated rows

I have this table create table "tasks" (id SERIAL PRIMARY KEY, user_id int REFERNCES "user"(id), title TEXT); I also created index on "tasks"(user_id) Then I open two ...
Le Hoang Long's user avatar
0 votes
1 answer
58 views

Force all queries within a batch to get data as at the start of the batch

In SQL Server, when executing a batch of SELECT queries, is it possible to force all queries run during the course of a batch to get data as it was at the start of the batch, to ensure consistency ...
Thomas's user avatar
  • 133
2 votes
1 answer
181 views

Isolation Level Conflict

Hi i was asked to look at a third party app recently. There were a few queries doing giant reads and that was easy enough to fix. However, while i was working on the queries i kept seeing different ...
Simon Bradley's user avatar
1 vote
0 answers
85 views

Do the PostgreSQL pg_* catalog tables respect transactions?

Do the pg_* catalog tables and the has_table_privilege function respect transaction isolation levels? Say in a default READ COMMITTED transaction, does the below have a race condition? SELECT ...
Michal Charemza's user avatar
0 votes
1 answer
765 views

Are missed rows and duplicate rows the symptoms due to allocation order scan or due to the no lock and page splits?

NOLOCK results in an allocation order scan rather than an index order scan. NOLOCK doesn't block writes because it doesn't take shared locks on the table. During the NOLOCK's scan, since there are no ...
variable's user avatar
  • 3,561
0 votes
1 answer
939 views

Allow create table (dynamic name) and insert but nothing else

I'm trying to find a way to allow an application to create tables and insert data into them on a SQL Server 2019 while protecting from injection attacks in case the app credentials would leak. My ...
Martin Riddar's user avatar
7 votes
1 answer
2k views

What are the problems with using NOLOCK given an approach to handle the short comings?

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking ...
variable's user avatar
  • 3,561
1 vote
1 answer
379 views

Isolation level with SQL Server and Azure Sql Database Read-Only replica

I read here and here that, when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking ...
Clement's user avatar
  • 536
1 vote
2 answers
83 views

How are SELECT queries isolated from each other with MariaDB 10.1?

Given the following tables.. CREATE TABLE `event` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(50) COLLATE utf8_general_ci NOT NULL, `data` longblob NOT NULL, `...
Sebastian's user avatar
  • 111
1 vote
1 answer
153 views

What's the correct database behavior when two transactions change the same row/value?

I'm writing a database LRU cache in C++ for an embedded NoSQL database to solve a performance problem, and I'm trying to understand the correct assumed behavior and philosophy behind it. Say there's a ...
The Quantum Physicist's user avatar
0 votes
0 answers
200 views

Deadlock due to serializable isolation level(from my knowledge)

Deadlock occurring on update. Both the updates holding RangeS-S lock and waiting for X lock. Is there any other possible way without changing the isolation level or using the retry logic? Database ...
ASh's user avatar
  • 43
3 votes
2 answers
1k views

Read-only transaction anomaly

I've learned about this anomaly from these papers: https://www.cs.umb.edu/~poneil/ROAnom.pdf (original) https://johann.schleier-smith.com/blog/2016/01/06/analyzing-a-read-only-transaction-anomaly-...
Max's user avatar
  • 41
0 votes
0 answers
39 views

Tracking locked rows using dm_tran_locks

I use Azure SQL DB (Basic, DTU, Provisioned). Isolation is default read committed snapshot. To handle concurrent transactions, I use table hints to lock the row(s). The requirement is that during the ...
Iniyavan's user avatar
4 votes
2 answers
2k views

Transaction strategy for UNIQUE constraint?

How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day? It was suggested to me that it will different for every isolation level. Could ...
PakLui's user avatar
  • 77
3 votes
1 answer
2k views

How does PostgreSQL handle parallel transactions with different transaction isolation levels?

I have a typical Java Spring + Postgres environment (the project is legacy). A persistence layer of backend has a mix of declared isolation levels - some of them are the default i.e. READ COMMITED, ...
Nikita Glukhov's user avatar
0 votes
1 answer
165 views

Pessimistic locking and isolation levels

How is pessimistic locking with read and write locks related to isolation levels? Readers block writers and writers block readers. This behaviour is always constant irrespective of isolation level ...
Manikandan Kbk DIP's user avatar
0 votes
1 answer
416 views

Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

I want to atomically update a single row and get its value. I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. ...
Jan Żankowski's user avatar

1
2 3 4 5