Transaction isolation relates to the visibility and impact of changes made in one transaction on concurrent transactions.
Questions tagged [transaction-isolation]
256 questions
1
vote
1 answer
Isolation -Phantom read in REPEATABLE READ
Does this PARTICULAR case fall into the non-repeatable read category or as a phantom read?
I don't think this QUESTION is DUPLICATED because I have not seen this particular case anywhere.
begin;
…
user19551894
1
vote
0 answers
Does Using a Linked Server Affect Isolation Levels
I have a query running on one server (let's call it Server B). It looks something like the below - using a linked server to query data from another server ('Server A')
-- in SERVER B
CREATE PROC [foo]
AS
INSERT [Table]
SELECT this, that
FROM…
High Plains Grifter
- 1,357
- 1
- 12
- 36
1
vote
1 answer
Is transaction isolation unnecessary in MYSQL when configuring one buffer pool?
According to https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html, buffer pool was protected by its own buffer pool mutex. So there is only one data request can visit buffer pool at the same time. If we have only one buffer…
rayen
- 89
- 7
1
vote
0 answers
Using serializable transactions in Celery and Sqlalchemy and serialization errors
I have a flask project that relies on flask-sqlalchemy and celery to do a lot of things. Many of the celery tasks reach out to external API's, fetch some data, and read/update data on disk. When expanding my number of tasks that are being run, I see…
enrm
- 645
- 1
- 8
- 22
1
vote
1 answer
Does the delete operation block any insert into the same table?
I have table A and a stored procedure that deletes all data from that table periodically. All queries in the stored procedure are packed into 1 transaction. But sometimes the stored procedure execution takes up to 5 minutes. Could it be that…
AlexMal
- 21
- 4
1
vote
2 answers
SQL Server filter rows which are being selected in other transactions
i have a couple of jobs Update from select queries e.g
UPDATE TABLE_X
SET "stopFlag" = 1
OUTPUT
INSERTED."RowID" AS "rowID"
WHERE "RowID" IN (
SELECT TOP 50
"RowID"
FROM
TABLE_X
WHERE
stopFlag=0
)
Currently…
Ivelin
- 25
- 5
1
vote
2 answers
Will psycopg2 cursor.fetchmany() see concurrent committed transactions?
Consider the following code:
import psycopg2
conn = psycopg2.connect(**credentials)
cur = conn.cursor()
cur.execute('select * from some_table') # Imagine some_table to be a very big table
while True:
rows = cur.fetchmany(1000)
if not…
Denis Arharov
- 91
- 1
- 10
1
vote
0 answers
Why would one want a lower isolation level than serializable?
I'm learning about transaction isolation and I'm wondering why one would want a lower isolation level than serializable. What challenges would occur if two different people with isolation level serializable tried to read the sum of e.g. the account…
Kristina
- 97
- 1
- 8
1
vote
0 answers
MySQL Repeatable Read and Phantoms, unique username example
Martin Kleppmann in his book "Designing Data-Intensive Applications" is showcasing the following problem:
Claiming a username
On a website where each user has a unique username, two users may try to create
accounts with the same username at the same…
Ihor M.
- 2,728
- 3
- 44
- 70
1
vote
0 answers
What does "Canceled on identification as a pivot" mean?
I use a postgres database and I see the following error message:
could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking.
HINT: The…
Martin Thoma
- 124,992
- 159
- 614
- 958
1
vote
0 answers
ReadUnCommitted isolationlevel not working with EF Core
For isolation level "ReadUncommitted", we should create a transaction:
using var transaction = await db.Database.BeginTransactionAsync(isolationLevel: System.Data.IsolationLevel.ReadUncommitted);
try
{
// Run the query
await…
Hamed Hajiloo
- 964
- 1
- 10
- 31
1
vote
2 answers
How to create Flyway schema history with snapshot isolation level?
I'm investigating whether it would be possible to use Flyway for our database schema migration, on the project I've been working on... Database migrations are done manually and I would really like to start using Flyway.
It is a Spring Boot (v2.2.0)…
Milan
- 11
- 5
1
vote
1 answer
Why no lock in MySQL for READ COMMITTED
I am using MySQL 8 in my windows machine. trying to see READ COMMITTED isolation level .
innodb_lock_wait_timeout = 5;
innodb_rollback_on_timeout =1;
T1: start transaction;
update todo set title='RC' where id=1;
T2;
start…
Gnana
- 2,130
- 5
- 26
- 57
1
vote
1 answer
Yii2 database transaction behaviour to support repeatable read
I have the following question. I have a web application (written in php Yii2), where multiple post requests are expected to hit the application server within a very short time. The business logic should be very strict, meaning that only the very…
varpet
- 37
- 5
1
vote
2 answers
MySQL query design for booking database with limited amount of goods and heavy traffic
We're running a site for booking salmon fishing licenses. The site has no problem handling the traffic 364 days a year. The 365th day is when the license sale opens, and that's where the problem occurs. The servers are struggling more and more each…
aanders77
- 620
- 8
- 22