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.
134
questions
5
votes
1answer
159 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'...
2
votes
1answer
39 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
34 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
31 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
57 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
152 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
61 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
24 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
40 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
33 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
233 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
27 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
157 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
25 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
201 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 ...
0
votes
0answers
40 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
27 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
110 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
166 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 ...
-1
votes
1answer
156 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(...
4
votes
2answers
396 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
76 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
115 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
129 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
406 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
452 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
49 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
178 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 ...
6
votes
1answer
3k 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
25 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
38 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
592 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 ...
11
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 ...
4
votes
1answer
1k 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
2k 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
...
1
vote
2answers
572 views
SQL Difference Optimistic Reading and Optimistic Writing
What is the difference between optimistic read and optimistic write?
Or probably more so, can someone define these 4?
(a) Optimistic Reading
(b) Pessimistic Reading
(c) Optimistic Writing
(d) ...
-1
votes
1answer
75 views
SQL Snapshot Isolations Phantom Rows
Does Read Committed Snapshot Isolation prevent phantom row issue? Does Snapshot Isolation prevent phantom row issue?
I am seeing different results in researching online:
Akira Yamamoto states: ...
1
vote
1answer
976 views
How to write validation trigger which works with all isolation levels
Background
I need to write validation trigger for a table. Due to nature of validation I can not use builtin integrity checks. For purpose of this question let's say that validation counts number of ...
0
votes
1answer
39 views
Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible?
Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes?
Here is an example of what I am seeing from the audit logs:
...
3
votes
1answer
604 views
SQL Server Snapshot Isolation Level: How to create an Update Conflict?
Does anyone have an example of how to create an "Update Conflict" using Transaction Level Snapshot (Not ReadCommmittedSnapshotIsolation)?
Error message example:
Snapshot isolation transaction ...
0
votes
1answer
313 views
Read Committed Snapshop Isolation Level - Effect of NOLOCK
As I understand it, with Read Committed Snapshot, readers don't block writers and writers don't block readers. The only blocking that can occur is writers blocking writers. By default our database is ...
2
votes
2answers
644 views
Snapshot Isolation vs Read Committed - OLTP and Reporting Databases
I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database ...
8
votes
1answer
729 views
Duplicate records returned from table with no duplicates
I have a stored procedure that queries a busy queue table that is used to distribute work in our system. The table in question has a primary key on WorkID and no duplicates.
A simplified version of ...
2
votes
1answer
104 views
What happens when the lock limit is reached?
My question is about the default isolation level: "read committed".
As I understand, during a read committed transaction, when we update a record, the system puts a lock on that record, so other ...
0
votes
2answers
116 views
UPDATE-SELECT Consistency Issue In Read Committed
In Read Committed isolation level, I would expect the following to deadlock since UPDATE statement will mark the row dirty and SELECT should not be able to read the row before transaction is committed ...
0
votes
1answer
709 views
MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT
The documentation states that without the GLOBAL or SESSION keyword, the isolation level will be valid for the next transaction and will be reverted to the session default after the transaction was ...
0
votes
0answers
2k views
A read operation on a large object failed while sending data to the client
A few days ago, in one of my servers, I had the following alert pop out:
A read operation on a large object failed while sending data to the
client. A common cause for this is if the application ...
9
votes
4answers
739 views
Do I add read committed after SET TRANSACTION ISOLATION LEVEL SERIALIZABLE?
Inside a stored procedure, I have the following : ( sql server 2008 )
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION getStuff
BEGIN TRY
/* some selects, updates, etc, etc. */
...
0
votes
1answer
43 views
Help troubleshooting mysql deadlock
We have a query, which is trying to update a row, placing a user at the end of a queue.
To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+...
