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
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 = ...
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 ...
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 ...
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 ...
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 ...
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 ...
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/...
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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

1
2 3 4 5