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
0
votes
0 answers

Parallel insert stored procedure fails in SQL SERVER

The below SP fails when running parallely in multiple threads. Tried to use different isolation levels in the SP, but still i face the same error. Violation of UNIQUE KEY constraint …
0
votes
1 answer

Multiple isolation levels needed for a TransactionScope?

I am running into situations in my application where I need to use table lock hints or set the transaction isolation level to something other than the default Read Committed, in order to resolve deadlock issues. I am using a service oriented…
0
votes
2 answers

Is a single DELETE statement (with WHERE( ...SELECT...)) referentially safe?

I know that any single SQL statement is implicitly run inside a transaction. But is this enough to guarantee relational integrity for a delete from ... where (... select... ) statement? How does the isolation level play into this? Here's my…
Cristian Diaconescu
  • 34,633
  • 32
  • 143
  • 233
0
votes
1 answer

Spring transactions and inserting into database

I have a design question. Consider an application running in multiple cluster environment, lets say there are 3 cluster. The application listens to a directory, and processes all the new files exported there and sends it to a document management…
Maverick Riz
  • 2,025
  • 5
  • 19
  • 23
0
votes
0 answers

Make transaction fail with conflict if a row previously read is changed by a concurrent transaction

If a row is modified by a transaction that uses the SNAPSHOT isolation level, any concurrent update (by another transaction) to the same row causes the SNAPSHOT transaction to fail with a conflict. Is there a way to have the same behavior happen…
Flavien
  • 7,497
  • 10
  • 45
  • 52
0
votes
0 answers

mysql single-session, multiple concurrent create temp table, isolation REPEATABLE-READ

In a single session I'd like to have two different routines that each creates their own temp tables (and insert values into them) inside of their own transactions. The db's tx_isolation is REPEATABLE-READ. Under what circumstances would the routines…
lf215
  • 1,185
  • 7
  • 41
  • 83
0
votes
1 answer

how to change Cypher isolation level to uncommitted

it seems that neo4j opens a transaction for simple match queries. for example: match (h:Event) return min(h.date_time),max(h.date_time) is it possible to change it, so it will read from uncommitted data?
Lior Goldemberg
  • 866
  • 13
  • 26
0
votes
1 answer

isolation levels exists in spring or hibernate

I would like to know Why do isolation levels exists in spring or hibernate if these are concepts of database? Aren't those handled by database so that we need to explicitly specify them in the spring application? what is the reason behind it?
eatSleepCode
  • 4,427
  • 7
  • 44
  • 93
0
votes
0 answers

How to do a write and read operation inside the same transaction?

I have a big ASP.NET transaction, with many operations. (using sql server) Transaction started Application updates a table (Let's name it T1) Application performs some other actions Application calls a webservice (which performs many…
0
votes
1 answer

SQL Server - Order of generating PK field values by parallel transaction

I have two threads that update table A at the same time. Each thread updates its own range of rows (they don't have intersections). Each thread works in READ UNCOMMITTED transaction. There is an AFTER UPDATE trigger on table A which adds records to…
Random
  • 3,807
  • 2
  • 30
  • 49
0
votes
1 answer

Can this update cause a deadlock in oracle 10g

I came across this update statement and was wondering how the internal working is. It updates a column which also is used in the where clause of the update. Should this be ideally done in two steps, or does oracle takes care of it…
dbza
  • 316
  • 1
  • 5
  • 19
0
votes
0 answers

high concurrency database application

I created a window-based application (C#) that connects to SQL Server (using SQL client). There are many users using this app at the same time. I have a form/screen that display a parent-child tables. When user click "Save" on this screen, I start a…
0
votes
1 answer

Is it possible silently run something as Administrator

How to trick UAC in Vista and Windows 7? How can I run something that needs not just administrator rights, but especially must be told to run as Administrator. I've tried start a process with given admin credentials, and even tried to use win32…
iLemming
  • 34,477
  • 60
  • 195
  • 309
0
votes
1 answer

Database locks and isolation level in JPA

I am not sure in understanding the Database Locks. I am using the repeatable read isolation level. According to wikipedia it keeps read and write locks (acquired on selected data) until the end of the transaction. Let's consider the following…
FilipR
  • 1,218
  • 4
  • 22
  • 39
0
votes
1 answer

SQL Server Isolation level real world example

Let's say we need to develop a bidding application such as one in eBay. We don't want one user's bidding to block another user's bidding, which will result in slow response. Also, when I place a bid based on the highest price I see, I don't want to…
Kenny
  • 1,902
  • 6
  • 32
  • 61