mysql-innodb下两种事务类型
有一段时间没写博客,刚看到mysql的提交类型的知识点,写个小demon记录下。
mysql innodb存储引擎提交分隐式提交及自动提交。
(1) 显式事务
用COMMIT命令直接完成的提交为显式提交。
例如:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from paint_user; +----+-----+--------+------+-------+---------+------+------------+------------+ | id | uid | weight | age | name | open_id | star | created_at | updated_at | +----+-----+--------+------+-------+---------+------+------------+------------+ | 1 | 1 | 19 | NULL | NULL | | 0 | 0 | 0 | | 2 | 2 | 99 | NULL | test1 | | 0 | 0 | 0 | | 3 | 3 | 99 | NULL | NULL | | 0 | 0 | 0 | | 6 | 6 | 0 | NULL | NULL | | 0 | 0 | 0 | | 7 | 7 | 0 | NULL | NULL | | 0 | 0 | 0 | | 8 | 4 | 99 | NULL | NULL | | 0 | 0 | 0 | | 9 | 11 | NULL | NULL | test2 | | 9 | 0 | 0 | +----+-----+--------+------+-------+---------+------+------------+------------+ 7 rows in set (0.01 sec) mysql> update paint_user set weight = 200 where uid = 1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> commit; Query OK, 0 rows affected (0.00 sec)
(2) 隐式事务
即无需显示执行commit语句,session中的操作被自动提交到数据库。
查看对话中是否开启自动提交:
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) #或者 mysql> show variables like '%autocommit%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)
举例如下,打开两个命令行窗口:
说明: 默认autocommit=1表示隐式事务在数据更改时候自动commit,截图中更改为手动提交。
如果是autocommit=1,截图第5步uid=4应该展示weight=99!!!
再看一个特殊例子,两个命令行窗口:
mysql中DDL默认不走事务,所以涉及到表结构变动的 ,无法用事务回滚!!!