Questions tagged [isolation-level]

Isolation level defines what data an SQL transaction can view or access while other transactions work with the same data.

ACID standard defines 4 isolation levels - read uncommitted, read committed, repeatable read and serializable. The higher the isolation level is, the more is guaranteed that another transaction can't break yours, but the lower amount of concurrency the database can handle. MySQL and MSSQL support all isolation levels, while PostgreSQL and Oracle support only the 2 most common, read committed and serializable

Read uncommitted means that the transaction works with the latest data available. In this isolation level it's possible that a transaction reads data that is not yet committed and possibly will be rolled back and never exist.

Read committed is the most basic isolation level, which ensures that transactions only read data that is already saved. It is possible however for another transaction to modify the data after the first transaction has read it but before it has modified it.

Repeatable read ensures that any subsequent read of the data will return the same result as the first read, therefore eliminating the race condition described above.

Serializable ensures that transactions are run in such a way that the result is the same as they were run in sequence, not in parallel.

725 questions
4
votes
4 answers

TRANSACTION ISOLATION LEVEL REPEATABLE READ in SQL Server

What are the risks or performance degradation when using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ? We have some SP that are called from BizTalk services. We are getting deadlock sometimes. If we change the isolation level, what are the risks…
Mariano
  • 2,928
  • 6
  • 25
  • 28
4
votes
1 answer

Why does my SQL Server UPSERT code sometimes not block?

I have a table ImportSourceMetadata which I use to control an import batch process. It contains a PK column SourceId and a data column LastCheckpoint. The import batch process reads the LastCheckpoint for a given SourceId, performs some logic (on…
Fabian Schmied
  • 3,885
  • 3
  • 30
  • 49
4
votes
1 answer

How are serializable transactions implemented?

I fail to see how the serializable isolation level can be implemented without locking the entire tables in question - something that appears to be possible as all material on the subject I could find talks about row and range locks. I found an…
John
  • 6,693
  • 3
  • 51
  • 90
4
votes
2 answers

What is an SQL Cursor?

In my mind I always thought of a cursor in databases as a pointer used to access a result set. But I heard that there are isolation levels for cursors. So perhaps a cursor is not just a pointer? What exactly is then an SQL cursor that seems to have…
Jim
  • 18,826
  • 34
  • 135
  • 254
4
votes
1 answer

Why is isolation level ignored when using TransactionScopeOption.Suppress in .net

I'm trying to execute an query with the isolation level read uncommitted within an existing transaction using LINQ TO SQL. If I use the option to suppress this transaction from the parent transaction, it seems I lose the ability to specify the…
Nick
  • 43
  • 1
  • 3
4
votes
3 answers

How to change the default TRANSACTION ISOLATION LEVEL in SQL Server 2005?

I know the default TRANSACTION ISOLATION LEVEL in SQL Server is "read committed". If I want to change it to "READ UNCOMMITTED", how may i make this configuration change? note: I cannot use SET TRANSACTION ISOLATION LEVEL, which only apply in the…
4
votes
1 answer

SQL Server deadlock under NHibernate

I have a multithread application, that works with large database (file size >1 Gb, database has 38 tables, more than 500 K of entities per table). It uses Castle 3.1.0.0, NHibernate 3.3.1.4000, FluentNibernate 1.3.0.733, SQL Server 2012. NHibernate…
user809808
  • 779
  • 1
  • 10
  • 23
4
votes
1 answer

How can I set transaction isolation level per session using Play, Hibernate and MySql

It can be useful to have repeatable read for some types of web requests, while others are easiest to implement with read committed. How can I specify which isolation level to use per Http request or per session or per transaction?
maya
  • 41
  • 1
  • 2
4
votes
2 answers

Default SQL Server IsolationLevel Changes

we have a customer that's been experiencing some blocking issues with our database application. We asked them to run a Blocked Process Report trace and the trace they gave us shows blocking occurring between a SELECT and UPDATE operation. The trace…
Graham
  • 305
  • 1
  • 5
  • 11
4
votes
1 answer

psycopg2 out of shared memory and hints of increase max_pred_locks_per_transaction

While inserting a lot of data into postgresql 9.1. using a Python script, we are getting the following error on this query: X: psycopg2.ProgrammingError in /home/hosting/apps/X X_psycopg.py:162 in : Execute 'execute' ( …
Brambo76
  • 191
  • 2
  • 8
3
votes
2 answers

Setting a transaction isolation level in Squeryl

How can I set a transaction isolation level using Squeryl? For instance, right now I am using Postgresql and need serializable isolation for specific single transactions. I use both plain Squeryl and Squeryl-Record with the Lift web…
Dr.Haribo
  • 1,778
  • 1
  • 31
  • 43
3
votes
1 answer

Scenario where one should use Read committed or Serializable as Isolation level?

I am trying to figure out which isolation level (among serializable and read committed )is better in what scenarios..At link http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#i17894, I was going thru topic choice of…
M Sach
  • 33,416
  • 76
  • 221
  • 314
3
votes
2 answers

Prevent a MySQL dirty read from blocking an explicit write lock?

I have a situation involving a large point-of-sale / reservations system I run. Customers have running balances which are updated on a trigger when a purchase transaction occurs. That's to say that there are triggers on insert, update and delete on…
joshstrike
  • 1,753
  • 11
  • 15
3
votes
3 answers

What are dirty writes? What happens if they are not allowed?

I read about database isolation levels and transactional phenomena. Dirty reads are obvious but I don't understand dirty writes. All descriptions of dirty write say something like: A dirty write is when a process save[s to] a file data that has…
3
votes
1 answer

Changing MongoDB Isolation level when Mongo Sessions involved

Whats is the default Isolation level for MongoDB4.0 when transactions are used. One Document say READ UNCOMMITTED is default isolation level.https://docs.mongodb.com/manual/core/read-isolation-consistency-recency/ Another document says snapshot is…
Selvakumar Ponnusamy
  • 5,363
  • 7
  • 40
  • 78