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
8
votes
2 answers

Why should I use Repeatable Read(or higher) isolation level if I need to read version the second time?

I've read Martin Fowler's book chapter Optimistic Offline Lock Author describes following example(if I understood it correctly): There are 2 entites: Order and Client. There are 2 transactions(business) involved: First transaction calculates…
gstackoverflow
  • 36,709
  • 117
  • 359
  • 710
8
votes
2 answers

MySQL interprets SERIALIZABLE less strenuously than PostgreSQL. Is it correct?

When using SERIALIZABLE transactions to implement a pattern of inserting a value into a database only if it does not already exist, I have observed a significant difference between MySQL and PostgreSQL in their definition of the SERIALIZABLE…
Michael Ekstrand
  • 28,379
  • 9
  • 61
  • 93
8
votes
2 answers

In MySQL Why does setting a variable from a select acquire a lock when using read uncommitted?

We have a table in MySQL using InnoDB, and we are using a transaction isolation level of read uncommitted. Why does setting @x as shown acquire a lock? mysql> set @x = (select userID from users limit 1); Query OK, 0 rows affected (0.02…
Yoseph
  • 730
  • 1
  • 7
  • 8
8
votes
1 answer

using @Transactional for thread safety

I have a question about how @Transactional annotation alone manages code and transactions execution. Given a correctly setup Spring application and the following code: @Transactional public void withdraw(int amount) { if(isEnoughFunds(amount))…
user1700598
  • 81
  • 3
7
votes
3 answers

How can I fix "Snapshot isolation transaction aborted due to update conflict"?

I see an error message related to transaction isolation levels. There are two tables involved, first one is updated frequently with transaction isolation level set to SERIALIZABLE, the second one has a foreign key on first one. Problem occurs when…
aron
  • 1,874
  • 5
  • 23
  • 29
7
votes
3 answers

Is a transaction that only updates a single table always isolated?

According to the UPDATE documentation, an UPDATE always acquires an exclusive lock on the whole table. However, I am wondering if the exclusive lock is acquired before the rows to be updated are determined or only just before the actual update. My…
lex82
  • 11,173
  • 2
  • 44
  • 69
7
votes
2 answers

How a Repeatable Read transaction run on the standby could see a transient state?

In the PostgreSQL documentation about transaction isolation levels, we can read the following: While performing all permanent database writes within Serializable transactions on the master will ensure that all standbys will eventually reach a…
Flyer
  • 327
  • 2
  • 14
7
votes
2 answers

Isolation levels in oracle

I would like to know different isolation levels with respect to commit, and would also like to know about row-level and table-level lock.
Touchstone
  • 5,575
  • 7
  • 41
  • 48
6
votes
1 answer

How does PostgreSQL implement the REPEATABLE_READ isolation level?

The REPEATABLE_READ transaction isolation level of PostgreSQL 12 prevents dirty reads, non-repeatable reads, and phantom reads. In contrast to the READ_COMMITTED isolation level, the REPEATABLE_READ isolation level prevents non-repatable reads and…
Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
6
votes
2 answers

Practical uses of Seralization Isolation level?

Under what scenarios we use SERIALIZABLE Isolation level? I have seen some answers on website that says, when you want transaction to be completely isolated, we usually go for this. What i would like to know from your own experience is that, when…
Vicky
  • 5,380
  • 18
  • 60
  • 83
6
votes
2 answers

Isolation level in C# or SQL - which one will be used?

I have set isolation level in C# code as readcommitted, and I am calling a stored procedure which is timing out due to some reason. The stored procedure does not have any set isolation level statement. In SQL Server, database level isolation level…
Sagar
  • 645
  • 2
  • 9
  • 31
6
votes
2 answers

SQL Server query - why am I getting deadlock?

I have the following code: set transaction isolation level read committed; --this is for clarity only DECLARE @jobName nvarchar(128); BEGIN TRAN SELECT @jobName = JobName FROM dbo.JobDetails WHERE ExecutionState_Status = 1 WAITFOR…
kubal5003
  • 7,186
  • 8
  • 52
  • 90
6
votes
1 answer

MySQL Workbench session does not see updates to the database

I have MySQL Workbench (community-6.2.3) installed in a Ubuntu system using .deb. Workbench session does not seem to see updates (DML) to the database done by other sessions (applications/command line client). A new session is able to see correct…
Sithsu
  • 2,209
  • 2
  • 21
  • 28
6
votes
1 answer

What will happen in PostgreSQL if a cascading delete is attempted on the parent of a locked row?

I have a table foo_bar and another table spam_eggs with a fb foreign key pointing to foo_bar. spam_eggs rows are cascade deleted when their related spam_eggs.fb are deleted. I'm working with PostgreSQL. In a transaction I have used SELECT... FOR…
orokusaki
  • 55,146
  • 59
  • 179
  • 257
5
votes
7 answers

Commiting only Specific Changes made inside a TRANSACTION which may ROLLBACK

This is a significant edit from the original question, making it more concise and covering the points raised by existing answers... Is it possible to have mulitple changes made to multiple tables, inside a single transaction, and rollback only some…
MatBailie
  • 83,401
  • 18
  • 103
  • 137
1
2
3
…
17 18