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
0
votes
0answers
17 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 ...
1
vote
0answers
35 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 ...
0
votes
0answers
14 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 ...
0
votes
1answer
32 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: ...
6
votes
2answers
2k 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 ...
0
votes
0answers
16 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. ...
-1
votes
1answer
52 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 ...
2
votes
1answer
51 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 ...
2
votes
0answers
54 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....
0
votes
2answers
54 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) ...
0
votes
1answer
30 views

Is there an alternative to isolation level SERIALIZABLE?

I use MySQL 5.7 and want to run an export task on a products table. The process basically runs like this: SELECT * FROM products WHERE exported = 0 Loop over the result set in my programm and write ...
0
votes
1answer
51 views

MSSQL - what is the best isolation level for a website

My website is mostly informative, Users cannot Update or Delete, they can perform Insert but that almost doesn't happen. Most of the site uses SELECT queries and the information doesn't change often ...
9
votes
2answers
674 views

Does SQL Server's serializable isolation level lock entire table

Me and a colleague of mine discussed the implications of use of the serializable isolation level. He said it locked the entire table, but I disagreed to that telling him it potentially could but it ...
6
votes
1answer
143 views

Change transaction isolation level inside running transaction

I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction. It seems that ...
5
votes
1answer
178 views

SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?

If I have a User table: id | name | age 1 | Mateus | 27 The first transaction executes an update, and leaves the transaction open, without committing or rolling back: update User set name = 'John'...
4
votes
1answer
70 views

PostgreSQL Serialisation failure on different ids

Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2: begin ...
0
votes
0answers
184 views

How to avoid MYSQL procedure “transaction characteristics can't be changed while a transaction is in progress” error?

I am trying to call a MySQL procedure which is throwing the error "transaction characteristics can't be changed while a transaction is in progress" I am making the call to this procedure from a ...
1
vote
1answer
35 views

Difference in location of transaction isolation level declaration - inside/ouside a transaction?

I have been looking though some stored procedured on our system today when I saw the following structure: (pseudocode indicative of actual SP, server is using the default isolation level): CREATE ...
0
votes
1answer
99 views

Lock Escalation happening while inserting few million records into a table in production

I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing INSERT INTO TAB1 SELECT COL1,COL2,COL3 FROM TAB2 This table has an ...
5
votes
1answer
165 views

NOLOCK or Read Uncommitted locking / latching behaviours

I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken. When using a SELECT with NOLOCK or in the Read Uncommitted ...
0
votes
0answers
120 views

Deadlock / Isolation Level

I was analyzing a deadlock that I received when I noticed that one of the processes involved in the deadlock had the isolation level set to Read Uncommitted. I search the web... and searched in my ...
1
vote
1answer
174 views

Isolation level serializable not working as expected?

I have this table create table testing( c1 text, c2 text); I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order Transaction 1 BEGIN; -- t0 SET ...
-3
votes
1answer
45 views

System failure causes inconsistency between two transactions

Let's say the picture above the transaction 4 for user A. A updated a record from Account table, change the balance from 100 to 0, then a checkpoint occurred, so all dirty pages got written to disk. ...
0
votes
0answers
61 views

WAL buffer checkpoint/flush time during serialization isolation

I have a doubt regarding the flush/checkpoint time of WAL buffers during serialization isolation in Postgres. Is it possible to lose acknowledged writes even if transaction runs in serialization ...
4
votes
2answers
507 views

Gap Locking in Read Committed isolation level in Mysql

MYSQL VERSION : 5.7.X STORAGE ENGINE : Innodb I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
0
votes
1answer
32 views

Difference between isolation levels in locktable implementation

I'm trying to implement lock tables in database with c++ and mysql. i'm using tpc-a benchmarks where i simulate a mini bank database and i run a bunch of threads on it and each thread do 10 ...
1
vote
1answer
262 views

InnoDB undo logs vs history list

I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same. ...
0
votes
0answers
38 views

Understanding non repeatable read anomaly in databases

This paper discusses in detail ANSI isolation levels discussed in ANSI SQL standard on page 84. Paper introduces following shorthand for describing histories which contain reads, writes, commits and ...
2
votes
1answer
359 views

Does using a read committed isolation level and a NOLOCK query hint impact performance?

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev ...
2
votes
1answer
132 views

Understanding repeatable read isolation level

Wikipedia defines following terms: With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be ...
1
vote
0answers
33 views

What does postgresql do when two transactions with different isolation levels run simultaneously?

According to this, postgresql supports different isolation levels per connection and even different ones in the same connection. Let's say one connection is using serializable and another is using ...
0
votes
1answer
172 views

Aws RDS cpu Spike to 100% because of running set isolation command and set

My application running with the AWS RDS as a backend. currently i am doing a performance test. While doing that i am seeing RDS cpu usage goes 100% after 20min. While seeing the performance insights ...
2
votes
2answers
274 views

Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?

The default isolation level of my database is "read committed snapshot". Please see the below screenshot. I have the table "Tickets' with the following data in my database. Now in connection 1, I ...
0
votes
1answer
240 views

How to Set the Transaction Isolation level in a Table Value Multi Function

How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure. create function dbo.Test() RETURNS @TestTable Table (test varchar(...
6
votes
2answers
633 views

Serializable range deadlocks

Help required on an inherited deadlock problem! The code given below appears to have been written some time back, in an attempt to address contention issues around the assigning of new ID values on ...
1
vote
1answer
166 views

Locks and dirty writes for SQL dbs

Lets say I have two transactions, t1 and t2, that write to the same two rows, r1 and r2. I understand a kind of “dirty write” would be if t1 overwrites t2 on r1 last while t2 overwrites t1 on r2. Is ...
5
votes
1answer
136 views

RCSI and index maintenance

I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes ...
0
votes
1answer
161 views

SQL Server Version Store Performance differences between RCS and ASI

I understand that Read Committed Snapshot (RCS) operates at statement level, and Allow Snapshot Isolation (ASI) operates at transaction level, and both store row versions in tempdb. I also understand ...
5
votes
1answer
447 views

Read Committed Snapshot, stale data reads?

I'm running SQL Azure and I have a following case. I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction ...
1
vote
2answers
671 views

Setting isolation level at database level

We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus ...
1
vote
1answer
53 views

Isolation level issue

I need some help in choosing the correct isolation level on my transactions. My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets ...
1
vote
1answer
53 views

Missing rows (gaps) on range read from the end of the table

On a data processing pipeline, getting gaps in returned data. Process A - inserts sequentially into a table. Table has an auto-increment primary key id. Process B - selects max(id) [into X] and then ...
1
vote
2answers
298 views

Are duplicates in Read Commited standard behaviour?

This refers to this question: Duplicate records returned from table with no duplicates The "read commited" isolation level assumes that one transaction could read same record differently, but it does ...
7
votes
1answer
4k views

Is snapshot isolation good?

My situation: Table: User { Id, Name, Stone, Gold, Wood } I have "write" threads: miningThread (every minute) UPDATE User SET Stone = @calculatedValue WHERE Id=@id UPDATE User SET Wood = @...
1
vote
1answer
28 views

Can sub-selects change in one single query in a read committed transaction?

I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are: BEGIN; WITH first AS (...
0
votes
1answer
39 views

Does READ COMMITED commited blocks any rows?

I know that there are four diffrent types of isolation levels in databases. I've read many articles about that but still, I don't get the idea of Read Commited level. Does this level lock the rows ...
0
votes
2answers
663 views

the role of “ Intent lock ” in transaction and concurrency

According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency): If an exclusive page lock is held in one page of a table, another process can not get even a shared ...
10
votes
6answers
4k views

Best situation to use READ UNCOMMITTED isolation level

As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons ...
5
votes
1answer
2k views

Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF

I experimented with various configurations of SQL Server databases and ended up setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF. I noticed that when enabling this, many ...
8
votes
1answer
3k views

Finding the default isolation level in SQL Server 2017 and Azure SQL DB

I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that: In on-premises SQL Server instance the default isolation level is Read-Committed based on locking ...