MySQL MVCC 2 transictions update one row, select the snapshot question

Question

MySQL version:5.7.29 Homebrew
ENGINE=InnoDB
REPEATABLE READ

MVCC snapshot question:
the table:

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `v` int not null
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  insert into r values (1,1);

the steps:

-----transaction A -------------| transaction B
--------------------------------|---------------------------
---------begin;-----------------| --
1, select * from r;-------------| --
--------------------------------| begin;
--------------------------------| update r set v=v+1 where id = 1;
--------------------------------| commit;
2, select * from r;-------------| --
update r set v=v+1 where id = 1;| --
3, select * from r;-------------| --
----------commit;---------------| --

step 1 and 2 v = 1, but why step 3 v = 3, cuz MVCC I think that v should be 2. please help me out.

I guess because insert and update operation is Current read?Current red mean always get newest value.But i doubt this insert operation whether have influence on other operation in transaction? — spike 王建, Aug 07 '20 at 02:59

score 0 · Answer 1 · answered Aug 07 '20 at 03:07

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

MySQL MVCC 2 transictions update one row, select the snapshot question

1 Answers1