在没有开启事务的情况下, Mysql 插入数据，如果中途出错会怎样？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2555 天前的主题，其中的信息可能已经有所发展或是发生改变。

譬如保存一大堆数据

插入数据库中

INSERT IGNORE INTO StudentTable (id, name) VALUES (1,"hong"),(2, "ping"),(3,"kong")....

中途出错

在没有开启事务的情况下

已被插入的数据会被删除还是留下？

插入

出错

kong

MySQL

19 条回复 • 2017-11-28 15:09:56 +08:00

gawainid

2017-11-27 19:15:36 +08:00

我觉得应该是出错前的留下

jalja27

2017-11-27 19:22:15 +08:00 via Android

测试一下应该就能看到结论，猜测出错前的会留下，虽然是一个语句，解析后应该是多条

yimity

2017-11-27 23:18:55 +08:00 via Android

这个是原子操作了吧，要么成功都有，要么都没有。除非是多条语句的插入吧。

cnnblike

2017-11-28 03:55:53 +08:00 via iPhone

acid 吧

cnnblike

2017-11-28 03:56:29 +08:00 via iPhone

mysql 这么经典的关系型数据库 acid 肯定保证的

Perry

2017-11-28 06:12:34 +08:00 via iPhone

只有一个 SQL statement 不需要事务

Perry

2017-11-28 06:13:28 +08:00 via iPhone

我收回刚刚那句话

naiba

2017-11-28 07:22:18 +08:00 via Android

@Perry 所以只有一个需不需要事务

des

2017-11-28 08:06:06 +08:00 via Android

@Perry 即使是单条插入，默认也是有事务包自动裹的。好像……记不太清了

1010011010

2017-11-28 09:07:12 +08:00

在 InnoDB，所有的用户活动发生在一个事务。如果 autocommit 启用模式，则每个 SQL 语句将自行形成一个事务。默认情况下，MySQL 会为每个 autocommit 启用的新连接启动会话，所以如果该语句没有返回错误，MySQL 将在每个 SQL 语句之后进行提交。如果语句返回错误，则提交或回滚行为取决于错误。

justicelove

2017-11-28 09:12:55 +08:00

楼上说的很对，但是批量插入好像挺特殊，你可以测试下，比如中间一组数据人为干预使他唯一约束报错。应该报错前插入的数据不会回滚。这点和 mysql 批量插入的操作方式有关。

kenduffy

2017-11-28 10:48:46 +08:00

@cnnblike 没开启事务的情况下也有 ACID?

kenduffy

2017-11-28 10:50:59 +08:00

@justicelove @1010011010

所以你的意思是有批量插入的情况下，最好开启事务（ java 里用 @transactional 注解）？
没有批插入的情况下，如果方法里只有一个 sql 语句，不需要开启事务？

justicelove

2017-11-28 13:33:46 +08:00

一般开启事物，具体开不开启看你自己了。

paradoxs

2017-11-28 13:38:50 +08:00

单条 insert 是不需要单独开启事务的。

除非
insert xxxxxxx
int = i/0;
insert xxxxxxxx

这种场景才要事务，不然第一条数据插进去了，第二条就没插进去。

crazyneo

2017-11-28 14:50:33 +08:00

@1010011010 To be exactly，autocommit 是分级别的，全局 /session/语句，以及是否允许脏读 /可重复读 /幻读这些现象也是根据 innodb 所支持的隔离级别而有不同支持。
简单总结一下，就是 innodb 目前支持四种级别的隔离级别( https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html)，对应现象分别是：
隔离等级脏读不可重复读幻读
未提交读可能可能可能
已提交读不可能可能可能
可重复读不可能不可能可能
可串行化读不可能不可能不可能

回到楼主的问题，哪种级别的 autocommit 都不影响，只有事务隔离级别对语句有影响，而 innodb 默认的事务隔离级别是可重复读，可能会出现幻读现象，亦即 @justicelove 所说的情况，存在该 bulk-insert 语句执行完后因为其他事务干扰导致的结果与预期不符情形，但不会出现脏读亦即语句未成功执行而出现部分数据被写入并被其他事务读取的情况(换句话说，如果其中有任何一个值插入失败，整条语句都会被回滚，但取决于 innodb 的磁盘刷新策略和时间点可能会有部分数据仍在内存或者已经落盘亟待清理的情况，但不会允许其他事务读取这些脏数据)。

kenduffy

2017-11-28 14:57:40 +08:00

@paradoxs 你说的“除非”已经是两条 insert 而不是单条了

crazyneo

2017-11-28 15:02:58 +08:00

顺便再就楼上添一句，也是针对 @justicelove 所疑惑的，实际上 insert 出现 duplicate key 的情况下，一般 bulk-insert 推荐写 on duplicate key update，这种情况是有可能出现不可重复读的，取决于语句先后顺序和执行时间点。

还有一点就是关于 auto increment，在较早版本(5.5 或更早？存疑)的 mysql 所自带的 innodb 版本，bulk insert 可能会出现插入顺序 123 实际写入顺序 213 的情况。

crazyneo

2017-11-28 15:09:56 +08:00

@kenduffy 我猜你想说的是是不是要开启自动提交事务，亦即在语句中是否要加入 autocommit = 0。
mysql 默认是开启自动提交事务的，在非显式使用 start transaction 的情况下，每条语句都会作为一个事务自动进行提交。对于你所说的 bulk insert，是的，推荐你写 on duplicate key update，而不写 auto commit = 0，除非是你自己想要在后面还做其他事情，然后手动加入 commit 语句。