Skip to main content

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
1 vote
2 answers
938 views

Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?

I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the ...
J. Mini's user avatar
  • 537
1 vote
1 answer
193 views

How can I verify if Read Committed Snapshot Isolation is causing application problems that Read Committed would not?

Suppose that I've turned on RCSI and my server is doing just fine; There have been no spikes in resource usage and the increased load on tempdb has been minimal. This is all good, but how can I tell ...
J. Mini's user avatar
  • 537
0 votes
0 answers
45 views

Reduce varchar column size in a production database

We have a database running on AWS Aurora Mysql with version 8.0.mysql_aurora.3.04.1 as a regional database. There is a table in it with a column with varchar size 200. We want to reduce it to varchar(...
Tanmay Sule's user avatar
1 vote
1 answer
104 views

What happens when RCSI is enabled in one database but not another, and you query both?

Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
J. Mini's user avatar
  • 537
-4 votes
2 answers
219 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
  • 537
0 votes
0 answers
54 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
44 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
2 answers
350 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
  • 121
-1 votes
1 answer
215 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
  • 549
0 votes
1 answer
348 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
54 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
116 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
387 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
324 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
62 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
128 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
40 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
200 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
739 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
  • 131
0 votes
1 answer
180 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
903 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
524 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
345 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
462 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
4 votes
1 answer
3k 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
  • 153
1 vote
1 answer
840 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
192 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
64 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
2k 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
303 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
  • 209
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
720 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
988 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
415 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,468
-1 votes
2 answers
122 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,468
3 votes
2 answers
951 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
328 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
64 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
245 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
90 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
847 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,468
0 votes
1 answer
1k 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
3k 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,468
1 vote
1 answer
410 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
88 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
192 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
216 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
2k 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
40 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

1
2 3 4 5