After the MySQL database InnoDB table and the MyISAM table are mixed, the transaction rolls back the test.

Test environment centos7.3+mysql5.7

A table using the InnoDB engine, the table name Innodb_test

The other table uses the MyISAM engine, and the table name Innodb_test

In the event of a transactional operation, see what happens.

Table structure same as only 3 fields Id,name, Money
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| ID | Int (10) | NO | PRI | NULL | auto_increment |
| name | char (30) | YES | | NULL | |
| Money | Decimal (11,2) | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+

Both tables have only one piece of data

+----+----------+---------+
| ID | name | Money |
+----+----------+---------+
| 1 | Colinshi | 1000.00 |
+----+----------+---------+

Automatic transaction commit is turned off first

mysql> SET autocommit = 0;

Query OK, 0 rows Affected (0.00 sec)

mysql> Show variables like ' autocommit '  ;

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
Mysql> begin;

Start transaction operation, this test successfully commit

mysql> Update innodb_test set money=500 WHERE id = 1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

mysql> Update myisam_test set money=500 WHERE id = 1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

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

Mysql> select * from Innodb_test;
+----+----------+--------+
| ID | name | Money |
+----+----------+--------+
| 1 | Colinshi | 500.00 |
+----+----------+--------+
1 row in Set (0.00 sec)

Mysql> select * from Myisam_test;
+----+----------+--------+
| ID | name | Money |
+----+----------+--------+
| 1 | Colinshi | 500.00 |
+----+----------+--------+
1 row in Set (0.00 sec)

In the case of a successful commit, there is no problem with the database. Is 500, what happens if a commit error needs to be rolled back.

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

mysql> Update myisam_test set money=300 WHERE id = 1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

mysql> Update innodb_test set money=300 WHERE id = 1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

mysql> rollback;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select * from Myisam_test;
+----+----------+--------+
| ID | name | Money |
+----+----------+--------+
| 1 | Colinshi | 300.00 |
+----+----------+--------+
1 row in Set (0.00 sec)

Mysql> select * from Innodb_test;
+----+----------+--------+
| ID | name | Money |
+----+----------+--------+
| 1 | Colinshi | 500.00 |
+----+----------+--------+
1 row in Set (0.00 sec)

The MyISAM table was found to have not been rolled back, and the InnoDB table was rolled back.

That is, if a transaction processes the MyISAM engine table, the InnoDB engine table, then the rollback will show inconsistent data.

I also directly used the Python MySQL to the database using the transaction operation result and the MySQL client is the same situation

Import Pymysql
Import time

def delsql (dzp_id):
    conn = Pymysql.connect (host= ' 192.168.1.71 ', user= ' root ', passwd= '! Qaz2wsx ', db= ' colinshitop ')
    cur = conn.cursor ()
    Cur.execute (' Use Colinshitop ')
    Try
        Cur.execute ("Update innodb_test set money= ' {} '  WHERE id = 1". Format (dzp_id))
        Cur.execute ("Update myisam_test set money= ' {} '  WHERE id = 1". Format (dzp_id))
        Cur.execute ("INSERT into name (' name ', ' Fdjaskfjsa ') VALUES (' Fdkjakf ', ' Value 2 ')")
        Conn.commit ()
        Print (' Commit commit ')
    Except
        Conn.rollback ()
        Print (' rollback rollback ')
    Finally
        Cur.close ()
        Conn.close ()

if __name__ = = ' __main__ ':
    dzp_id = input (' Please enter Amount: ',)
    Delsql (dzp_id)
    Print (' program terminates after 5 Seconds ')
    Time.sleep (5)

Try not to mix InnoDB and MyISAM tables at the same time in the database. If you really need to mix it up, you can't operate on both kinds of table engines with one transaction at the same time.

After the MySQL database InnoDB table and the MyISAM table are mixed, the transaction rolls back the test.