logo标签列表

主要的SQL数据库是否支持撤销CREATE TABLE和ALTER TABLE语句？

sqltransactionsddlcreate-table
128
我正在开发一个能够发出DDL（数据定义语言）的程序。我想了解在以下数据库中，CREATE TABLE和其他相似的DDL操作是否可以回滚。
  • Postgres
  • MySQL
  • SQLite
  • 等等
请描述每个数据库如何处理带有DDL的事务。
仅为补充此线程，H2 大多数 SQL 命令也不支持事务性 DDL 语句，根据 此链接。 - Gabriel Paim
5个回答
173

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis提供了PostgreSQL的观点关于此问题的概述。

根据该文档，DDL是否是事务性的？

  • PostgreSQL - 是
  • MySQL - 否；DDL会导致隐式提交
  • Oracle Database 11g Release 2及以上版本- 默认情况下不是，但存在一种称为基于版本的重定义的替代方法
  • 早期版本的Oracle - 否；DDL会导致隐式提交
  • SQL Server - 是
  • Sybase Adaptive Server - 是
  • DB2 - 是
  • Informix - 是
  • Firebird（Interbase）- 是

SQLite似乎也具有事务性的DDL。 我能够在SQLite中使用ROLLBACK回滚CREATE TABLE语句。其CREATE TABLE文档未提及任何特殊的事务性问题。

9
然而，默认的Python sqlite驱动器会阻止事务型SQL操作。http://bugs.python.org/issue10740 - joeforker
2
所以答案是“是的，它们可以被回滚，除非你正在使用MySQL或更旧版本的Oracle。” - rjmunro
不，除了列出的那些SQL数据库之外还有其他的。 - joeforker
3
MariaDB存在一个开放问题，需要增加事务性DDL支持：https://jira.mariadb.org/browse/MDEV-4259。请投票支持。 - Gili
1
SQLite的ALTER TABLE语句功能有限，但也可以回滚。这在文档中没有明确说明。文档中提到的是如何在事务中执行“高级”更改。 - Thomas
显示剩余3条评论
34

PostgreSQL为大多数数据库对象提供了事务性DDL（如表、索引等），但不支持对数据库和用户进行DDL操作。然而，实际上任何DDL都会在目标对象上获取ACCESS EXCLUSIVE锁，使其在DDL事务完成之前完全无法访问。此外，并非所有情况都能得到很好处理——例如，如果您尝试从表foo中选择数据，同时另一个事务正在删除它并创建一个替换表foo，则被阻塞的事务最终将收到错误消息而不是找到新的foo表。（编辑：这个问题在 PostgreSQL 9.3或之前版本中已经得到解决）

CREATE INDEX ... CONCURRENTLY 是一个例外，它使用三个事务向表添加索引，同时允许并发更新，因此不能在一个事务中执行。

此外，数据库维护命令 VACUUM 不能在事务中使用。

我认为，如果我在另一个事务正在删除和重新创建表foo时尝试从中选择，那么我可以接受旧版本或错误。我不接受新版本，因为它还没有提交，所以我不能看到它。我可以接受错误，因为在并发事务访问中，必须准备好重新启动事务。如果错误比必要的更频繁地发生，可能会降低性能，但仍然是正确的。 - Jan Hudec
1
@JanHudec：你不会看到新表的未提交版本，只能看到删除/重新创建整个事务的结果。也就是说，删除、重新创建和重新填充表的事务在其他选择该表的进程方面是有效的原子操作。（但是，一旦它们尝试读取表的模式，所有内容都将被阻止） - araqnid
8
似乎无法使用MySQL实现，非常愚蠢但却是事实...（根据被接受的答案）
“InnoDB中的CREATE TABLE语句被处理为一个事务。这意味着用户的ROLLBACK不会撤销用户在该事务期间执行的CREATE TABLE语句。”

https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

尝试了几种不同的方法，但它仍然无法回滚.. 解决方法是简单地设置一个失败标志并执行"drop table tblname"，如果其中一个查询失败..
1
很遗憾，截至v10.5版本，MariaDB仍不支持事务性DDL，请参见此链接了解详情。 - Ham
7

看起来其他答案都比较过时。

截至2019年：

  • Postgres支持事务性DDL已经有很多版本了。
  • SQLite支持事务性DDL已经有很多版本了。
  • MySQL从2018年推出的8.0版开始支持原子DDL。
3
应注意到，MySQL 8 中的 Atomic DDL 仅指原子 DDL 语句，而不是事务性语句。DDL 语句（无论是否原子）主要仍会导致隐式提交，因此不能在另一个事务中执行（例如，START TRANSACTION...COMMIT;）。因此，如果相同事务中后面的语句失败，则仍无法回滚事务中的 DDL 语句。（请参见 https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html#atomic-ddl-characteristics 下的注释） - Lacek
4

虽然严格来说它不是“回滚”，但在Oracle中，如果数据库已配置支持，则可以使用FLASHBACK命令撤消这些类型的更改。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接