Mysql数据库InnoDB表和MyISAM表混用后,事务回滚测试。
测试环境CENTOS7.3+MYSQL5.7
一张表使用InnoDB引擎,表名称 innodb_test
另外一张表使用MyISAM引擎,表名称 innodb_test
在事务操作的时候,看看到底会发生什么情况。
表结构相同只有3个字段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 | |
+-------+---------------+------+-----+---------+----------------+
两张表都只有一条数据
+----+----------+---------+
| id | name | money |
+----+----------+---------+
| 1 | colinshi | 1000.00 |
+----+----------+---------+
先关闭了自动事务提交
mysql> SET AUTOCOMMIT =0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
mysql> begin;
开始事务操作,这次测试成功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)
在成功commit的情况下数据库没有问题。都成了500,那么如果提交错误需要回滚会是什么情况呢。
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)
发现MyISAM表并没有进行回滚操作,而InnoDB表进行了回滚操作。
也就是说一个事务如果同时处理了MyISAM引擎表,InnoDB引擎表,那么回滚会出现数据不一致的情况。
我还直接使用了python的mysql对数据库使用了事务操作结果和mysql客户端是相同情况
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', '值2')")
conn.commit()
print('提交commit')
except:
conn.rollback()
print('回滚rollback')
finally:
cur.close()
conn.close()
if __name__ == '__main__':
dzp_id = input('请输入金额:',)
delSql(dzp_id)
print('5秒后程序终止')
time.sleep(5)
数据库中尽量不要同时用InnoDB表和MyISAM表混用。如果真的迫不得已需要混用,一定不能同一个事务同时对两种表引擎操作。