Analysis of strange phenomena in MySQL mvcc
This article mainly explains "MySQL mvcc strange phenomenon analysis", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "MySQL mvcc strange phenomenon analysis"!
Strange phenomenon 1:
Session1:
Root@localhost: test 08:43:09 > select * from test1; (1)
+-+ +
| | orderid | ID |
+-+ +
| | 2 | 123 |
+-+ +
1 row in set (0.00 sec)
Root@localhost: test 08:43:14 > start transaction; (2)
Query OK, 0 rows affected (0.00 sec)
Root@localhost: test 08:43:23 > select * from test1; (3)
+-+ +
| | orderid | ID |
+-+ +
| | 2 | 123 |
+-+ +
1 row in set (0.00 sec)
Root@localhost: test 08:43:27 > select * from test1; (5)
+-+ +
| | orderid | ID |
+-+ +
| | 2 | 123 |
+-+ +
1 row in set (0.00 sec)
Root@localhost: test 08:44:03 > update test1 set id=234 where orderid=1; (6)
Query OK, 1 row affected (0.01sec)
Rows matched: 1 Changed: 1 Warnings: 0
Root@localhost: test 08:44:27 > select * from test1; (7)
+-+ +
| | orderid | ID |
+-+ +
| | 1 | 234 |
| | 2 | 123 |
+-+ +
2 rows in set (0.00 sec)
Session2:
Root@localhost: test 08:43:48 > INSERT into test1 values (1123);
Query OK, 1 row affected (0.01sec)
Strange phenomenon 2:
Session1:
Root@localhost: test 08:46:57 > select * from test1; (1)
+-+ +
| | orderid | ID |
+-+ +
| | 1 | 123 |
| | 2 | 123 |
+-+ +
2 rows in set (0.00 sec)
Root@localhost: test 08:47:01 > start transaction; (2)
Query OK, 0 rows affected (0.00 sec)
Root@localhost: test 08:47:15 > select * from test1; (4)
+-+ +
| | orderid | ID |
+-+ +
| | 1 | 123 |
| | 2 | 123 |
| | 3 | 123 | |
+-+ +
3 rows in set (0.00 sec)
Session2:
Insert into test1 values (3); (3)
Phenomenon 3:
Root@localhost: test 08:49:26 > start transaction; (1)
Query OK, 0 rows affected (0.00 sec)
Root@localhost: test 08:49:28 > select * from test1; (2)
+-+ +
| | orderid | ID |
+-+ +
| | 1 | 123 |
| | 2 | 123 |
| | 3 | 123 | |
+-+ +
3 rows in set (0.00 sec)
Root@localhost: test 08:49:30 > select * from test1; (4)
+-+ +
| | orderid | ID |
+-+ +
| | 1 | 123 |
| | 2 | 123 |
| | 3 | 123 | |
+-+ +
3 rows in set (0.00 sec)
Session2:
Root@localhost: test 08:47:43 > insert into test1 values (4dint 123'); (3)
Query OK, 1 row affected (0.01sec)
Phenomena 2 and 3 can be perfectly explained by read-view in mvcc:
1. You cannot see the transactions started after the creation time of read view.
2. You cannot see the active transactions when read view was created.
Perhaps we are familiar with that under the isolation level of repeatable-read, when a session initiates a dml statement, the result of the operation of the dml statement cannot be seen if the current session is not submitted in the current session! Read-view apps may rarely notice, like me.
But how to explain phenomenon 1?
After insert trx_id=aa in session 1, after update in trx_id=ac,session2 in session 2, you can see more clearly below
That is, an invisible update on session 2 in session 1 will change the trx_id in session 2 into the same trx_id in session 1, so session 1 can see the updated state of invisible data in session 2 at this time.
Thank you for your reading, the above is the content of "Analysis of strange phenomena in MySQL mvcc". After the study of this article, I believe you have a deeper understanding of the analysis of strange phenomena in MySQL mvcc, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!
