Questions tagged [repeatable-read]
16 questions
10
votes
3 answers
PostgreSQL's Repeatable Read Allows Phantom Reads But its document says that it does not allow
I have a problem with Postgresql repeatable read isolation level.
I did make an experiment about repeatable read isolation level's behavior when phantom read occurred.
Postgresql's manual says "The table also shows that PostgreSQL's Repeatable Read…
fatih kosal
- 175
- 1
- 11
2
votes
1 answer
Understanding InnoDB Repeatable Read isolation level snapshots
I have the following table:
CREATE TABLE `accounts` (
`name` varchar(50) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
And it has two accounts in it. "Bob" has a…
Aurast
- 3,189
- 15
- 24
2
votes
2 answers
Is it possible to do a Phantom read to a row someone just updated?
From the MySQL glossary:
phantom: A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after…
Croco
- 326
- 1
- 12
2
votes
2 answers
MySQL Repeatable Read isolation level and Lost Update phenomena
In High Performance Java Persistence book's 6.3.3.3 section it's written that Lost Update phenomena is possible in MySQL Repeatable Read isolation level. This is the screenshot:
Assuming the following(isolation level is REPEATABLE READ):
…
Suren Aznauryan
- 984
- 10
- 24
1
vote
0 answers
Understanding InnoDB X-Lock with REPEATABLE_READ and READ_COMMITED isolation level
I have two mysql(AWS Aurora) db tables:
--------------------------------
| Table:deparment |
--------------------------------
--------------------------------
| id | dept_name |
--------------------------------
| d1 …
Rajdip
- 21
- 5
1
vote
1 answer
What is the theory behind when DELETE or UPDATE query comes in a Repeatable Read Isolation Level?
In Repeatable Read Isolation Level, it does not prevent other transactions from inserting new rows into the tables which have been selected in the current transaction.
I want to know the theory behind when DELETE or UPDATE query comes? Consider the…
Nawoda Dharmabandu
- 33
- 7
0
votes
1 answer
JPA with Isolation.READ_COMMITTED acts as REPEATABLE_READ
I'm seeing a behaviours that is a little strange in my test JPA app.
Simple table;
id
count
@Transactional(isolation = Isolation.READ_COMMITTED)
public void testCount() {
// I randomly fetch a row which has a count > 4
Counts counts =…
Alexis
- 1,825
- 4
- 23
- 28
0
votes
0 answers
PostgreSQL Repeatable Read Isolation Level "transaction start time"
https://www.postgresql.org/docs/15/transaction-iso.html#XACT-REPEATABLE-READ
The PostgreSQL documentation for the Repeatable Read Isolation Level states the following:
UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave…
Wizard Brony
- 111
- 5
0
votes
0 answers
Is it possible to analyze the types of transaction isolation issues?
I'm taking care of a web application which uses Postgres and the SERIALIZABLE transaction isolation level. I am thinking about reducing the transaction isolation level REPEATABLE_READ. Is there a way to analyze current logs to see if there were…
Martin Thoma
- 124,992
- 159
- 614
- 958
0
votes
1 answer
update then select value which updated by other transaction but get the value before update
mysql: 8.0.23
transaction isolation level: repeatable read
test data:
create table test.test (
id int primary key,
value int
);
insert into test.test(id,value) values(1,0);
In same transaction, we get output value is 1 as we have execute…
14sxlin
- 76
- 5
0
votes
2 answers
InnoDb - SELECT while a Repeatable Read Transaction is Executing
Consider a table with fields ID, X, Y, and Z.
CREATE TABLE TABLE_NAME (
ID int NOT NULL,
X varchar(255) NOT NULL,
Y varchar(255) NOT NULL,
Z varchar(255) NOT NULL,
PRIMARY KEY (ID)
);
create index idx on TABLE_NAME (X,…
user2684198
- 812
- 1
- 10
- 18
0
votes
0 answers
Replay java stream in memory effiicient way?
There are some ways to replay java 8 streams e.g. mentioned here https://dzone.com/articles/how-to-replay-java-streams. Now what if i want to use log huge java stream (200-300mb) and pass it forward for replay. That should work right? But what if i…
AlexL
- 33
- 6
0
votes
0 answers
Does REPEATABLE READ affect Hibernate Expectations?
We have been facing StaleStateException in our code mostly with following error Hibernate - Batch update returned unexpected row count from update: 0 actual row count: 0 expected: 1.
This error wasn't seen when we were using Oracle 12c database but…
JavaTechnical
- 8,846
- 8
- 61
- 97
0
votes
1 answer
Repeatable read regarding select * with order by and limit
At the default isolation level of mysql (Repeatable Read), if I issue a select like select * from table_a where column_a = 'a' order by id limit 100, and after a while, I issued another statement within the same transaction like select * from…
Danny
- 5
- 4
0
votes
1 answer
What concurrency issues can this PostgreSQL code create?
Ok so here's the schema, which is pretty self-explanatory:
STORE(storeID, name, city)
PRODUCT(productID, name, brand)
PRODUCT_FOR_SALE(productID, storeID, price)
I have 2 transactions: T1 and T2.
T1 raises by 5% the price for any product sold in…
Michael Vigato
- 326
- 2
- 11