0

I am learning InnoDB mvcc now and i have try a test show as follows:

Mysql version:

[root@mysql-test ~]# mysql --version
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

table schema:

MariaDB [liruifeng]> show create table test_a;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                          |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_a | CREATE TABLE `test_a` (
  `id` int(11) NOT NULL DEFAULT '0',
  `a` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

then init with data like this:

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

and at first i have open two session in different terminals, the test step show as bellows:

t1:

MariaDB [liruifeng]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

t2:

MariaDB [liruifeng]> insert into test_a values (4,4);
Query OK, 1 row affected (0.01 sec)

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

t1:

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

MariaDB [liruifeng]> update test_a set a = 444 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 444  |
+----+------+
4 rows in set (0.00 sec)

it makes me puzzled that why a t1 can update the row insert by t2 before t1 has committed? my tx_isolation level is repeatable read and why will this update sql works?

my isolation show as bellows:

MariaDB [liruifeng]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

thanks in advances :)

Pazu Lee
  • 3
  • 4

2 Answers2

0

REPEATABLE-READ says that the select * from test_a; will say the same thing until t1 COMMITs. The fact that the UPDATE can see row 4 but the identical SELECT cannot is weird, but valid.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • so if program try to update data with a where clause like "a < 5" maybe dangerous when other transaction do the insert or update operation? why the mvcc in mysql designed like this? :( – Pazu Lee Aug 31 '17 at 08:02
  • `UPDATE ... WHERE a<5` would lock (to some degree) all those rows. `WHERE a=5` would lock only the one row. Generally, one makes transactions so fast that subtle issue like this are not an issue. Production systems take milliseconds, not seconds between statements like your t1 and t2. – Rick James Aug 31 '17 at 14:17
0

this is strange, in my experiment with auto-commit = false the update will block because of a record X lock on the inserted entry.

maki
  • 477
  • 2
  • 12