473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Repeateable read isolation level question

I think I understand the concept of repeatable read, but this scenario
doesn't make sense to me, if someone can explain.

Say you have an innodb table test with a string column s = 'blank' and
are using the default isolation level of repeatable read.

Session 1:
begin;

Session 2:
begin;

S1:
update test set s = 'session one' where id = 1;
commit;

S2:
mysql> select * from test;
+-------+------+
| s | id |
+-------+------+
| blank | 1 |
+-------+------+
1 row in set (0.00 sec)

(so we cannot see the changes from S1's transaction yet, only when S2
commits or rolls back will it see the changes)

mysql> update test set s = 'session two' where s = 'blank' and id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update test set s = 'session two' where s = 'session one' and
id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

So this is very weird to me, that the first update fails. You cannot
see the updates from session 1 in a select due to the isolation level,
yet your where clause sees the updates...???

Mind you, this is good since it avoids lost updates, but I just don't
understand it--it seems inconsistent.

Thanks,
Chris
Jul 19 '05 #1
0 1820

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: francis70 | last post by:
Hi, I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the...
11
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to...
0
by: Chris | last post by:
I think I understand the concept of repeatable read, but this scenario doesn't make sense to me, if someone can explain. Say you have an innodb table test with a string column s = 'blank' and...
29
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using...
2
by: kanda | last post by:
Hello. I am developing the application (VBA&ODBC, to be exact) which periodically calls the stored procedures in the IBM DB2. A few of the procedures require executing with isolation level RR (...
8
by: Gregor Kovač | last post by:
Hi! If I create a procedure with BEGIN ATOMIC block I know these means that when an error occurs all the SQL statements will be rolled back. The question I have is: If I select all records form...
5
by: m0002a | last post by:
Is there some way to track the isolation level of an indivual SQL statement submitted via JDBC in a snaphot or some other similar means? I have JDBC programs that are changing the isolation level...
3
by: D. | last post by:
I have a question about the "readCommitted" transaction isolation level. I have a client that is updating a record on a table. I suspend the execution after the UPDATE but before the commit...
5
by: william.david.anderson | last post by:
Hi there, I have a newbie question regarding stored procedures and locking. I'm trying to use a stored procedure to perform a 'select for update' and return a cursor. Below is a stripped down...
3
by: RG | last post by:
How can I lookup the current isolation level? Thanks in advance
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.