Stack Overflow
Stack Overflow

Questions tagged [transaction-isolation]

Transaction isolation relates to the visibility and impact of changes made in one transaction on concurrent transactions.

256 questions
250
votes
12 answers

What is the difference between Non-Repeatable Read and Phantom Read?

What is the difference between non-repeatable read and phantom read? I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom…
user1357722
  • 7,088
  • 13
  • 34
  • 43
61
votes
2 answers

Atomic UPDATE .. SELECT in Postgres

I'm building a queuing mechanism of sorts. There are rows of data that need processing, and a status flag. I'm using an update .. returning clause to manage it: UPDATE stuff SET computed = 'working' WHERE id = (SELECT id from STUFF WHERE computed IS…
kolosy
  • 3,029
  • 3
  • 29
  • 48
28
votes
7 answers

Minimum transaction isolation level to avoid "Lost Updates"

With SQL Server's transaction isolation levels, you can avoid certain unwanted concurrency issues, like dirty reads and so forth. The one I'm interested in right now is lost updates - the fact two transactions can overwrite one another's updates…
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
28
votes
5 answers

What are the conditions for encountering a serialization failure in PostgreSQL?

The PostgreSQL manual page on the Serializable Isolation Level states: [Like] the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures. What are the conditions for encountering a…
Daniel Trebbien
  • 38,421
  • 18
  • 121
  • 193
27
votes
1 answer

What is default isolation level hibernate uses if not explicitly set?

I have an application that uses hibernate version 3.6.4, and c3p0 version 0.9.1.2 for connection pooling. My underlying RDBMS is MySql version 5.0.67. My installation of MySql indicates that the default transaction isolation level is…
Argyro Kazaki
  • 631
  • 2
  • 6
  • 15
20
votes
5 answers

Does MySQL/InnoDB implement true serializable isolation?

It is not entirely clear from MySQL documentation whether the InnoDB engine implements true serializable isolation1 or snapshot isolation, which is often confusingly called "serializable" too. Which one is it? If MySQL InnoDB doesn't, are there any…
Roman Starkov
  • 59,298
  • 38
  • 251
  • 324
20
votes
4 answers

NOLOCK vs. Transaction Isolation Level

What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other?
ozzijb
  • 674
  • 5
  • 10
  • 17
19
votes
2 answers

Atomic UPDATE to increment integer in Postgresql

I'm trying to figure out if the query below is safe to use for the following scenario: I need to generate sequential numbers, without gaps. As I need to track many of them, I have a table holding sequence records, with a sequence integer column. To…
Paul
  • 335
  • 1
  • 3
  • 16
19
votes
2 answers

PostgreSQL Transaction Isolation READ UNCOMMITTED

I want to try transaction isolation using PostgreSQL with pgadmin. First I inserted a new record inside BEGIN but without COMMIT. BEGIN; INSERT INTO my_table(id,value) VALUES (1,'something'); //UNCOMMITTED Then, I tried to read the uncommitted…
ZZZ
  • 1,415
  • 5
  • 16
  • 29
14
votes
2 answers

Snapshot isolation transaction aborted due to update conflict

Following statement: INSERT INTO dbo.Changes([Content], [Date], [UserId], [CompanyId]) VALUES (@1, @2, @3, @4); SELECT @@identity; gives me this SQL error 3960: Snapshot isolation transaction aborted due to update conflict. You cannot use…
TN.
  • 18,874
  • 30
  • 99
  • 157
13
votes
3 answers

Database race conditions

I've heard about many application developers having a bit of trouble in regards to race conditions in database processing. A typical example goes something like this: User 1 selects a field, say, numStock, which is 3 User 2 also selects numStock,…
Matt Larsen
  • 471
  • 4
  • 6
  • 12
12
votes
3 answers

How to Select UNCOMMITTED rows only in SQL Server?

I am working on DW project where I need to query live CRM system. The standard isolation level negatively influences performance. I am tempted to use no lock/transaction isolation level read uncommitted. I want to know how many of selected rows are…
BI Dude
  • 1,842
  • 5
  • 37
  • 67
11
votes
1 answer

Can a locked row [in Postgres] still be read from?

If I SELECT... FOR UPDATE a row in a transaction, it will obviously block the row from being written to, but will it disallow reads as well? I'd prefer to still be able to read from the row, so if the answer is yes, can you provide a solution to…
orokusaki
  • 55,146
  • 59
  • 179
  • 257
9
votes
2 answers

Can concurrent value modification impact single select in PostgreSQL 9.1?

Consider the following query executed in PostgreSQL 9.1 (or 9.2): SELECT * FROM foo WHERE bar = true Suppose it's a fairly long running query (e.g. taking a minute). If at the start of the query there are 5 million records for which bar = true…
dexter meyers
  • 2,798
  • 2
  • 18
  • 22
8
votes
4 answers

How to safely and atomically decrement a counter with Django and PostgreSQL?

I've been reading up on PostgreSQL transaction isolation and how that relates to Django's transaction.atomic() (e.g. this article, PostgreSQL docs), but I'm far from fluent in this topic and I'm not sure I understand what I've read. We've got a…
JK Laiho
  • 3,538
  • 6
  • 35
  • 42
1
2 3
…
17 18