Prepare table structure: CREATE table for InnoDB engine t_i CREATE table ' t_i ' (' a ' int (one) default NULL) Engine=innodb default Charset=utf8
CREATE table for MyISAM engine t_m CREATE table ' t_m ' (' a ' int (one) default NULL) Engine=myisam default Charset=utf8
The opening of a transaction is divided into the following two ways:
See if the default auto-commit is on: ([email protected]) [wzy]> show variables like ' autocommit '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | autocommit | On | +---------------+-------+ 1 row in Set (0.00 sec)
When the default is auto-commit, you need to use the following command to open InnoDB's transaction ([email protected]) [wzy]> begin; Query OK, 0 rows Affected (0.00 sec)
([email protected]) [wzy]> INSERT INTO t_i values (1); Query OK, 1 row Affected (0.00 sec)
([email protected]) [wzy]> select * from T_i; +------+ | A | +------+ | 1 | +------+ 1 row in Set (0.00 sec)
([email protected]) [wzy]> commit; Query OK, 0 rows affected (0.01 sec) Last commit transaction using commit
Turn off the default auto-Commit transaction ([email protected]) [wzy]> set autocommit=0; Query OK, 0 rows Affected (0.00 sec)
([email protected]) [wzy]> Show variables like ' autocommit '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in Set (0.00 sec)
In this case, you do not have to begin the transaction, directly execute the DML statement
([email protected]) [wzy]> INSERT INTO t_i values (2); Query OK, 1 row Affected (0.00 sec)
([email protected]) [wzy]> commit; Query OK, 0 rows Affected (0.00 sec)
Note: If the autocommit transaction has been turned off, do not use begin to open the transaction, otherwise the data will not be visible in the other session even if the transaction is committed.
Question: In the same transaction, the data is inserted into the InnoDB table and MyISAM tables, but no transaction is committed, can I see the data in the table MyISAM in another transaction?
Using the two table tests created above, execute the following statement in Session1: ([email protected]) [wzy]> show variables like ' autocommit '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in Set (0.00 sec)
([email protected]) [wzy]> INSERT into t_i values (3); Query OK, 1 row Affected (0.00 sec)
([email protected]) [wzy]> INSERT INTO t_m values (1); Query OK, 1 row Affected (0.00 sec)
([email protected]) [wzy]> select * from T_i; +------+ | A | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in Set (0.00 sec)
([email protected]) [wzy]> select * from T_m; +------+ | A | +------+ | 1 | +------+ 1 row in Set (0.00 sec)
Execute the following statement in Session2 to see the results ([email protected]) [wzy]> select * from T_i; +------+ | A | +------+ | 1 | | 2 | +------+ 2 rows in Set (0.00 sec)
([email protected]) [wzy]> select * from T_m; +------+ | A | +------+ | 1 | +------+ 1 row in Set (0.00 sec)
It can be seen that, in the case of opening a transaction, the data is inserted into the MyISAM engine table, even if no transaction is committed, and the data is visible in another transaction.
InnoDB the opening of a transaction
