Small instances of MySQL update data between multiple sessions

Source: Internet
Author: User
Tags mysql update

1: Create an experiment table

mysql> use test;mysql> CREATE TABLE T    -(ID int (one) not NULL default 0,    num int (one) default null,< C2/>-> PRIMARY KEY (ID)),    Engine=innodb DEFAULT CHARSET=GBK; Query OK, 0 rows affected (0.02 sec)

  

Mysql> INSERT into T values (1,100);mysql> insert into T values (2,200);
Session A Session B
Mysql> BEGIN;
Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  |+----+------+2 rows in Set (0.00 sec)

  

mysql> use testreading table information for completion of table and column namesyou can turn off this feature to get a Quicker startup With-adatabase changedmysql> INSERT into T VALUES (3,300); Query OK, 1 row affected (0.01 sec)

  

Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  |+----+------+2 rows in Set (0.00 sec)

mysql> UPDATE t SET num=1000 WHERE id=3; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0

The above view does not have the id=3 column, here incredibly succeeds!

Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  | |  3 | |+----+------+3 rows in Set (0.00 sec)

  

From session A, it appears that it is trying to update a nonexistent record (id=3), the result is updated successfully, and the record can be accessed later. Why is Sessiona's second search still 2 records?    InnoDB Each transaction starts with a transaction ID and a Read_view variable in the transaction object that controls the range of records (MVCC) that the transaction is visible to.    for each record row that is accessed, the record is logically visible based on the Read_view trx_id (transaction ID) compared to the trx_id of the row record.    the record inserted in session B is not visible because session a precedes session B, so the newly inserted data is judged and is not within the visible range. The corresponding source code is in ROW/ROW0SEL.C [4040-4055].

  

Session A Session B
Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  | |  3 | |+----+------+3 rows in Set (0.00 sec)

  

Mysql> INSERT into T VALUES (4,400); Query OK, 1 row affected (0.01 sec)

  

Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  | |  3 | |+----+------+3 rows in Set (0.00 sec)

  

Mysql> COMMIT; Query OK, 0 rows Affected (0.00 sec)

  

Mysql> SELECT * FROM t;+----+------+| ID | Num  |+----+------+|  1 |  | |  2 |  | |  3 | | |  4 |  |+----+------+4 rows in Set (0.00 sec)

Normally visible after commit

Small instances of MySQL update data between multiple sessions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.