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
1answer
28 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 ...
3
votes
2answers
86 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
1answer
21 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 ...
0
votes
2answers
33 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 ...
7
votes
1answer
205 views

Missing rows under read committed isolation

It's well known that Read Committed isolation is prone to different anomalies. I read a great Paul White's series on isolation levels (the one relevant to the discussion is this one: https://...
1
vote
1answer
72 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
1answer
45 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
2answers
53 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 ...
1
vote
2answers
74 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 ...
1
vote
3answers
44 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
2answers
92 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
1answer
32 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 | ....
8
votes
2answers
510 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 ...
1
vote
2answers
198 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 ...
0
votes
1answer
26 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 | ...
2
votes
1answer
161 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 ...
0
votes
0answers
13 views

Do `SELECT` statements within a transaction return rows it has previously `INSERT`/`UPDATE`, when using the REPEATABLE READ isolation level of MySQL?

Do SELECT statements within a transaction return rows it has previously INSERT/UPDATE? What confuses me is the description of REPEATABLE READ given below, where it says that the first SELECT ...
0
votes
1answer
55 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 ...
0
votes
1answer
318 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 ...
0
votes
1answer
36 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 ...
0
votes
0answers
32 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
50 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
1answer
56 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
491 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 ...
1
vote
2answers
269 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
461 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
118 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
57 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
58 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
80 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
57 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 ...
11
votes
2answers
2k 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
417 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
261 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
243 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 ...
1
vote
1answer
40 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
321 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
185 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
220 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
1k 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
51 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
78 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 ...
5
votes
2answers
1k 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
49 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 ...
2
votes
1answer
524 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. ...
2
votes
1answer
749 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
457 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 ...
2
votes
0answers
55 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
379 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 ...