For questions about "isolation levels" - the setting that specifies the applicable guarantees of concurrency and consistency in multi-user database systems.
Questions tagged [isolation-level]
232 questions
50
votes
5 answers
ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT
Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question.
I guess the word SNAPSHOT in both setting get a…
Travis
- 2,345
- 2
- 20
- 25
29
votes
3 answers
Managing concurrency when using SELECT-UPDATE pattern
Let's say you have the following code (please ignore that it's awful):
BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the…
ErikE
- 4,355
- 4
- 29
- 39
17
votes
7 answers
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 might it be used?
Actually I read the answers…
user123215
16
votes
3 answers
Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the majority of my general SQL queries, mainly because this was drilled in to me when originally learning the language.
From my understanding, this isolation level acts the same way that WITH…
dmoney
- 270
- 1
- 2
- 7
15
votes
2 answers
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 tries to apply range locks and it doesn't apply…
mslot
- 427
- 5
- 10
13
votes
4 answers
MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preface
Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same rows from separate threads. However, per…
vitalidze
- 233
- 2
- 6
12
votes
1 answer
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 = @calculatedValue WHERE Id=@id
tradingThread (every…
Glebka
- 189
- 1
- 1
- 7
12
votes
2 answers
Where does InnoDB store transaction data before committing it?
I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology.
I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not yet committed (could perform an…
Shuzheng
- 221
- 2
- 4
11
votes
2 answers
Shared Lock issued on IsolationLevel.ReadUncommitted
I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock:
Resource_Type: HOBT
Request_Mode: S (Shared)
What is a HOBT lock? Something related to HBT (Heap or…
dsum
- 739
- 4
- 9
- 20
11
votes
2 answers
Switching to RCSI
The company I work for currently uses SQL Server databases (latest Enterprise version usually) for a product we develop.
I would describe it as an OLTP database that is somewhat equally write and read intensive with a lot of time critical apps. In…
Cole W
- 251
- 1
- 4
10
votes
3 answers
"When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"?
The MSDN online article "Snapshot Isolation in SQL Server" states:
"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed…
Fulproof
- 1,392
- 2
- 26
- 36
10
votes
1 answer
Why is READ COMMITTED a common default transaction isolation level?
I am wondering if anyone knows the history of why READ COMMITTED is the default transaction isolation level for PostgreSQL, SQL Server, Oracle, Vertica, DB2, Informix, and Sybase.
MySQL uses default REPEATABLE READ, at least with InnoDB, as do…
Josh Kupershmidt
- 2,516
- 16
- 19
9
votes
3 answers
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select statements from this table.
Under default isolation…
growse
- 375
- 2
- 6
- 13
9
votes
2 answers
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 the same time:
| Session 1 - Statement 1 |…
Vivek Kumar Batra
- 93
- 5
9
votes
1 answer
Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?
If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?
If I have a table where some of the rows will be updated, but I'm only selecting rows that are…
J.D.
- 40,776
- 12
- 62
- 141