由 John Doe 六月 25, 2024
摘要：在本文中，我们将学习 PostgreSQL 中的事务性 DDL，以及与其他流行数据库的区别。
目录
事务性 DDL
与一些商业数据库一样，PostgreSQL 中有一个比较高级的功能，它能够通过其预写式日志的设计执行事务性 DDL。该设计支持回退 DDL 产生的重大更改，例如表创建。您无法恢复添加和删除数据库或表空间的操作，但所有其他的对象操作都是可逆的。
PostgreSQL
下面是一个示例，显示了 PostgreSQL 的设计在这方面的处理能力：
DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist
BEGIN;
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
ROLLBACK;
SELECT * FROM foo;
ERROR: relation "foo" does not exist
有经验的 PostgreSQL DBA 知道，在执行表结构升级等复杂工作时，可利用此功能来保护自己。如果你把所有这些变更都放到一个事务块中，你可以确保它们都以原子方式进行应用，或者完全不应用。这大大降低了数据库因架构变更中的拼写错误或其他此类错误而损坏的可能性，这在修改多个相关表时尤为重要，因为错误可能会破坏关系键。
MySQL
如果您在使用 MySQL，则无法以这种方式撤消 DDL 和一些类似的更改。如果您使用的是 MyISAM 存储引擎，那它根本不支持事务。对于 InnoDB 存储引擎，服务端有一种隐式提交，即使关闭了正常的自动提交行为，DDL 命令也会导致当前事务发生隐式提交。
set autocommit = 0;
drop table foo;
create table foo (bar int) engine=InnoDB;
insert into foo values (1);
rollback;
select * from foo;
Empty set (0.00 sec)
Oracle
一个事务从第一个可执行的 SQL 语句开始。事务在提交或回滚时结束，无论是显式使用COMMIT或ROLLBACK语句，还是在发出 DDL 语句时隐式提交。
Oracle 数据库在以下情况下会发生隐式COMMIT：
- 在任何语法上有效的数据定义语言（DDL）的语句之前，即使该语句发生了错误。
- 在任何没有发生错误执行完成的数据定义语言（DDL）语句之后。
让我们在 Oracle 中创建一个表，并插入一行：
-- Create a table and insert a row
CREATE TABLE states
(
abbr CHAR(2),
name VARCHAR2(90)
);
-- Transaction will be in progress after this insert
INSERT INTO states VALUES ('CA', 'California');
现在，让我们创建另一个表，并执行 ROLLBACK 操作：
-- Create another table table and insert a row
CREATE TABLE cities
(
name VARCHAR2(90),
state CHAR(2)
);
INSERT INTO cities VALUES ('San Francisco', 'CA');
ROLLBACK;
您可以看到，即使在 ROLLBACK 之后，表 states 和表中的行仍然存在，因为CREATE TABLE cities语句提交了事务。
表 cities 也存在，但插入的行已回滚：
-- Table states exists and contains 1 row
SELECT COUNT(*) FROM states;
-- Result: 1
-- Table cities also exists, but the inserted row was rolled back
SELECT COUNT(*) FROM cities;
-- Result: 0
SQL Server
在某些情况下，SQL Server 支持事务性 DDL。如果在读已提交的隔离模式下（悲观模式）运行 SQL Server，则可以使用事务性 DDL。如果运行的是 SQL Server 2005 或更高版本，并且在快照隔离模式下（乐观模式）运行，则在显式事务中支持的 DDL 会存在一些限制：SQL Server 快照隔离下的事务性 DDL 约束。
让我们启动一个事务，在 SQL Server 中创建一个表，并插入一行：
-- Start a transaction explicitly
BEGIN TRANSACTION;
-- Create a table and insert a row
CREATE TABLE states
(
abbr CHAR(2),
name VARCHAR(90)
);
INSERT INTO states VALUES ('CA', 'California');
-- 1 row(s) affected
现在让我们创建另一个表，插入一行，并执行 ROLLBACK 操作：
-- Create another table table and insert a row
CREATE TABLE cities
(
name VARCHAR(90),
state CHAR(2)
);
INSERT INTO cities VALUES ('San Francisco', 'CA');
-- 1 row(s) affected
ROLLBACK;
您可以看到，在 ROLLBACK 之后，表都已不存在：
SELECT COUNT(*) FROM states;
-- Msg 208, Level 16, State 1, Line 1
-- Invalid object name 'states'.
SELECT COUNT(*) FROM cities;
-- Msg 208, Level 16, State 1, Line 1
-- Invalid object name 'cities'.