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
1 answer
29 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 ...
user avatar
0 votes
1 answer
35 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 ...
user avatar
  • 21
2 votes
1 answer
33 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 ...
user avatar
1 vote
0 answers
25 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 ...
user avatar
0 votes
1 answer
306 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 ...
user avatar
  • 2,076
0 votes
1 answer
34 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 ...
user avatar
0 votes
0 answers
68 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 ...
user avatar
  • 1
7 votes
1 answer
628 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 ...
user avatar
  • 2,076
1 vote
1 answer
88 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 ...
user avatar
  • 446
1 vote
2 answers
64 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, `...
user avatar
  • 111
1 vote
1 answer
54 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 ...
user avatar
0 votes
0 answers
64 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 ...
user avatar
  • 43
3 votes
2 answers
539 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-...
user avatar
  • 41
0 votes
0 answers
29 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 ...
user avatar
1 vote
0 answers
94 views

SQL statements to ensure that the same location cannot be reserved more than once on the same day?

I have two tables one for venues (3 locations hall1, hall2 and hall3) and one for orders (a row could be 441,2004,50895,'Requested','2021-01-02 10:53','2021-03-06 11:46',7,3) as follow: CREATE TABLE ...
user avatar
  • 47
4 votes
2 answers
400 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 ...
user avatar
  • 77
2 votes
1 answer
239 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, ...
user avatar
0 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
97 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. ...
user avatar
0 votes
1 answer
31 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 ...
user avatar
  • 41
1 vote
2 answers
196 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 ...
user avatar
  • 125
1 vote
1 answer
502 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 ...
user avatar
  • 429
3 votes
2 answers
332 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 ...
user avatar
1 vote
1 answer
265 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 ...
user avatar
  • 115
0 votes
2 answers
63 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 ...
user avatar
  • 3
7 votes
1 answer
285 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 ...
user avatar
1 vote
1 answer
79 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 ...
user avatar
1 vote
1 answer
349 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 ...
user avatar
3 votes
2 answers
61 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 ...
user avatar
  • 153
1 vote
2 answers
145 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 ...
user avatar
  • 153
1 vote
3 answers
58 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 ...
user avatar
1 vote
2 answers
122 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 ...
user avatar
0 votes
1 answer
44 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 | ....
user avatar
  • 3
9 votes
2 answers
818 views

UPDATE statement behavior

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at ...
user avatar
1 vote
2 answers
417 views

PostgreSQL sometimes fails to detect serialization failures

I have an http-server in application language that looks roughly like this: item = SELECT item FROM table WHERE field = 'value' if (item) { UPDATE another field on item } else { INSERT item VALUE ...
user avatar
  • 151
0 votes
1 answer
53 views

Transaction isolation level/design for write-only ledger

This is a simplified example of a problem I've been working on. Say I have the following database schema: Table: Deposits +--------+------------+--------+---------+ | ID | Date | Amount | ...
user avatar
2 votes
1 answer
735 views

Are SqlServer Sequences, guaranteed to return different values for concurrent transactions?

In documentation it is told that: Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during ...
user avatar
0 votes
1 answer
121 views

View serializable schedule or not

Could you help me understand why the schedule on the picture is view serializable without any "blind write"? Also, should the results be the same if two schedules are view equivalent? I did create ...
user avatar
1 vote
1 answer
781 views

Does ISOLATION LEVEL READ UNCOMMITTED affect Deletes?

I have an exceptional circumstance that made me use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. (I am using sp_getapplock to ensure single process access to a given row.) I am trying my best ...
user avatar
  • 2,394
0 votes
1 answer
38 views

dynamic data masking read uncommited

I have read about Dynamic Data Masking and one question is still in my mind. I am wondering what will happen is such scenario: 1. Dynamic Data Masking is set up on table 2. ETL process is loading new ...
user avatar
0 votes
0 answers
38 views

What is correct way to ensure that row is not deleted in concurrent transaction

I have 2 tables: Parent (PK id) Child (PK id, parent_id) Note that parent_id is not defined a foreign key (actually they are not parent/child, I use those names just for example). I need to handle ...
user avatar
  • 121
2 votes
0 answers
52 views

Repro for missed rows during Allocation Order Scan + Page Splits

In Read Uncommitted, an index scan can execute as an allocation order scan. If a page split occurs during the scan, it is possible that a row will be read twice if it moves to a later position in the ...
user avatar
0 votes
1 answer
103 views

Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?

I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12. The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to ...
user avatar
  • 101
2 votes
1 answer
1k views

What standard SQL isolation levels do the SQLite isolation levels correspond to?

There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.) In SQLite, there are three isolation levels: ...
user avatar
  • 129
6 votes
2 answers
3k views

Will my SQL query use stale data? How can I prevent it?

I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record ...
user avatar
  • 163
1 vote
2 answers
437 views

Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

The MySQL doc for START TRANSACTION says: The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. ...
user avatar
  • 648
-1 votes
1 answer
838 views

Serializable Transactions vs SELECT FOR UPDATE in MySQL

I have a stock table with columns: id, available, sold, status fields. Before increasing sold field I have to check if sold + x is less and equal than available. To make sure every transaction uses ...
user avatar
  • 45
2 votes
1 answer
287 views

How the single SQL statement is isolated?

All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved. But what's about single statements? What level of isolation do they have? Is it ...
user avatar
  • 121
2 votes
0 answers
74 views

Choosing between RCSI and Snapshot Isolation [closed]

I have researched into this and I understand the general differences between the two isolation levels. I would like to explain our scenario and need help in deciding the correct isolation level to use....
user avatar
  • 23
0 votes
2 answers
70 views

Read uncommited does not work in UPDATE WHERE NOT EXISTS

I'm not able to force SQL Server (2016) to respect values updated in the same statement. What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) ...
user avatar
  • 53