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.
226 questions
0
votes
2
answers
129
views
What wait types would terrible RCSI performance cause?
Read Committed Snapshot Isolation (RCSI) is well-understood. The key way that it can cause performance bottlenecks is if the version chains get too long. Are there any wait type that indicate this ...
- 823
0
votes
2
answers
110
views
How can a transaction view uncommitted changes made by another transaction in UNCOMMITTED READ
I know this is probably a silly question, It is my first, It seems so fundamental that I can't find the answer anywhere, because it must be so straight forward no one explained it.
I want to ...
- 103
4
votes
3
answers
185
views
Prevent inserting duplicate entries under READ COMMITTED Isolation
We have a SQL Server table called dbo.Dim_Target. Multiple threads try to INSERT new data into this table in parallel. Each parallel thread first checks for matching data and then only inserts new ...
- 707
0
votes
1
answer
49
views
Why MySQL repeatable read doesn't raise serialization error
I'm looking at MySQL MVCC, and it has strange behavior at Repeatable Read isolation:
Regular SELECT returns the version from the snapshot
SELECT FOR SHARE returns the latest version of the record
...
2
votes
2
answers
1k
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 ...
- 823
1
vote
1
answer
297
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 ...
- 823
0
votes
0
answers
82
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(...
1
vote
1
answer
124
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 ...
- 823
-4
votes
2
answers
342
views
What do developers need to know about Read Committed Snapshot Isolation?
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?
- 823
0
votes
0
answers
74
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 ...
- 119
1
vote
1
answer
54
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 ...
- 119
2
votes
2
answers
403
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. ...
- 121
-1
votes
1
answer
255
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.
...
- 672
2
votes
1
answer
545
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 ...
- 133
0
votes
2
answers
57
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 ...
- 966
0
votes
1
answer
218
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 ...
- 3
1
vote
2
answers
635
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 ...
- 113
0
votes
1
answer
436
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 ...
0
votes
0
answers
79
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 ...
0
votes
1
answer
200
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 ...
0
votes
1
answer
43
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 ...
- 169
0
votes
1
answer
289
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 ...
4
votes
0
answers
839
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 ...
- 141
0
votes
1
answer
212
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 ...
- 191
2
votes
1
answer
1k
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 ...
3
votes
1
answer
600
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 = ...
- 133
1
vote
1
answer
382
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 ...
- 113
0
votes
1
answer
491
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 ...
- 188
4
votes
1
answer
4k
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 ....
- 153
1
vote
1
answer
988
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 ...
- 209
3
votes
0
answers
211
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 ...
- 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 ...
- 1,219
1
vote
2
answers
77
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 ...
- 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 ...
- 165
1
vote
1
answer
328
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 ...
- 221
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/...
- 13
4
votes
1
answer
806
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 ...
- 49
2
votes
1
answer
1k
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 ...
- 31
-1
votes
1
answer
442
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
...
- 3,499
-1
votes
2
answers
128
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 ...
- 3,499
3
votes
2
answers
1k
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 ...
1
vote
1
answer
360
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 ...
- 111
0
votes
1
answer
73
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 ...
- 133
2
votes
1
answer
270
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 ...
1
vote
0
answers
99
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
...
- 263
0
votes
1
answer
921
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 ...
- 3,499
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 ...
- 196
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 ...
- 3,499
1
vote
1
answer
450
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 ...
- 546
1
vote
2
answers
91
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,
`...
- 111