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.
205
questions
2
votes
1
answer
26
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 ...
-1
votes
0
answers
39
views
Can a delete operation fail due to a concurrent update?
For a given table, I am seeing scenarios where one thread is trying to update (not upsert) a row by unique ID, while another thread is trying to delete that same row.
Assuming both transactions start ...
- 107
2
votes
1
answer
46
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 = ...
- 123
0
votes
1
answer
42
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 ...
- 103
0
votes
1
answer
65
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
2
votes
1
answer
273
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 ....
- 133
0
votes
1
answer
157
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 ...
- 197
3
votes
0
answers
87
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
629
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 ...
- 747
1
vote
2
answers
32
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
2
votes
1
answer
365
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 ...
- 135
1
vote
1
answer
90
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 ...
- 165
0
votes
1
answer
350
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/...
- 3
2
votes
1
answer
190
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 ...
- 29
2
votes
1
answer
371
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
169
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,133
-1
votes
2
answers
91
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,133
3
votes
2
answers
236
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
160
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
42
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 ...
- 33
2
votes
1
answer
64
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
52
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
...
- 149
0
votes
1
answer
450
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,133
0
votes
1
answer
231
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 ...
0
votes
0
answers
253
views
Why is read committed Isolation slower than repeatable read?
I have a stored procedure to do a daily archive job:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
insert into _temp_merge_table(.......)
select /*omit...*/
from Order
where ...
- 1
7
votes
1
answer
997
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,133
1
vote
1
answer
216
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 ...
- 446
1
vote
2
answers
77
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
1
vote
1
answer
67
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 ...
0
votes
0
answers
142
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 ...
- 43
3
votes
2
answers
681
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-...
- 41
0
votes
0
answers
33
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 ...
- 1
4
votes
2
answers
973
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 ...
- 77
3
votes
1
answer
829
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, ...
0
votes
1
answer
82
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 ...
0
votes
1
answer
186
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. ...
- 193
0
votes
1
answer
55
views
Out-of-order results in the READ UNCOMMITTED isolation level?
On the Wikipedia page for Isolation (database systems), when explaining dirty reads, it says:
The only thing that may be prevented in the READ UNCOMMITTED isolation level is updates appearing out of ...
- 41
2
votes
2
answers
523
views
MariaDB event to automatically move data older than a specific time to another table without temporary table?
I am trying to setup an event, that automatically moves data older than a specified amount of time from a heavily used table to some "archive" table to improve performance.
So far I've only ...
- 135
1
vote
1
answer
1k
views
What is isolation level of autocommit?
I'm trying to make sense from "autocommit" concept.
Originally I've heart about it in the context of Java JDBC API Connection.setAutoCommit(boolean autoCommit)
Quick search revealed that the ...
- 459
3
votes
2
answers
624
views
Blocking on readable secondary replica
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the ...
- 765
1
vote
1
answer
798
views
How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?
I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within ...
- 115
0
votes
2
answers
258
views
Read Committed Shared Lock
I wanted to know when you are at read committed isolation level and in one session someone started an explicit transaction which has a select statement, say it takes 20 seconds. Can someone from a ...
- 3
7
votes
1
answer
405
views
Missing rows under read committed isolation
It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one:
The Read ...
- 555
1
vote
1
answer
84
views
RCSI & ETL processing compatibility
The Situation:
I have a write-heavy, read-light ETL workload with the basic process of:
get chunk of raw data (generally 1000-5000 records) from an import landing table.
pass the chunk of data from ...
- 35
1
vote
1
answer
680
views
Accessing Memory Optimised Tables Under Snapshot Isolation
We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.
Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view ...
- 405
3
votes
2
answers
75
views
Isolation levels and rows not yet read
Under RR in transaction T1 I do a
SELECT * FROM mytable WHERE city='London'
In another T2 I do
UPDATE mytable SET price=100 WHERE city='London' and id=5000
Is it possible that T2 reads,updates and ...
- 163
2
votes
2
answers
332
views
Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level
There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels.
I get the RC example but not the RCSI one.In particular how ...
- 163
1
vote
3
answers
89
views
How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions
Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating ...
- 13
1
vote
2
answers
179
views
Dirty read or phantom row
We are having dirty reads or phantom rows in our data warehouse. My question is not how to solve it (it is a long story and I am currently working on it), but I want to know what exactly happens to ...
- 467
0
votes
1
answer
101
views
Enforcing maximum number of relations for a record
I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system. The following is an example use case.
// Job table
job_id | ... | num_positions
xx-xx-xx | ....
- 3