我正在帮助我的一些SQL问题的同事。他们主要是想将所有行从表A移到表B(两个表都具有相同的列(名称和类型))。尽管这是在Oracle 11g中完成的，但我认为这并不重要。

他们最初的天真实现是

BEGIN
  INSERT INTO B SELECT * FROM A
  DELETE FROM A
  COMMIT;
END

他们担心的是，在从A复制到B的过程中是否对表A进行了INSERT操作，而" DELETE FROM A"(或者TRUNCATE表示值得)会导致数据丢失(删除了A中新插入的行)。

当然，我很快建议将复制的行的ID存储在临时表中，然后仅删除A中与临时表中的IDS相匹配的行。

但是出于好奇的缘故，我们通过在INSERT和DELETE之间添加一个等待命令(不记得PL / SQL语法)进行了一些测试。从另一个连接中，我们将在等待期间插入行。

我们发现这样做会造成数据丢失。我在SQL Server中重现了整个上下文，并将其全部包装在一个事务中，但是仍然新鲜的新数据在SQL Server中也丢失了。这使我认为初始方法中存在系统性的错误/缺陷。

但是，我不能说这是因为TRANSACTION没有(以某种方式)与新的INSERT隔离，或者是INSERT在WAIT命令期间出现的事实。

最后，它是使用我建议的临时表实现的，但我们无法获得"为什么数据丢失"的答案。你知道为什么吗？

解决方案

我不知道这是否相关，但是在SQL Server中，语法为

begin tran
....
commit

不只是"开始"

我们需要设置事务隔离级别，以使来自另一个事务的插入不会影响事务。我不知道该如何在Oracle中做到这一点。

我不能说事务的稳定性，但是另一种方法是从存在的源表中删除第二步(从目标表中选择ID)。

原谅语法，我尚未测试此代码，但我们应该能够理解：

INSERT INTO B SELECT * FROM A;

DELETE FROM A WHERE EXISTS (SELECT B.<primarykey> FROM B WHERE B.<primarykey> = A.<primarykey>);

这样，我们将使用关系引擎来强制执行任何新数据都不会被删除的操作，并且我们无需在事务中执行两个步骤。

更新：更正了子查询中的语法

可以使用以下方法在Oracle中实现：

Alter session set isolation_level=serializable;

可以在PL / SQL中使用EXECUTE IMMEDIATE进行设置：

BEGIN
    EXECUTE IMMEDIATE 'Alter session set isolation_level=serializable';
    ...
END;

请参阅询问Tom：关于事务隔离级别

这就是交易的方式。我们必须为手头的任务选择正确的隔离级别。

我们正在同一事务中执行INSERT和DELETE。我们没有提到隔离模式事务正在使用，但是它可能是"读已提交"。这意味着DELETE命令将看到在此期间提交的记录。对于这种工作，最好使用"快照"类型的事务，因为这时INSERT和DELETE只会知道同一组记录，而没有其他信息。

是的，米兰，我还没有指定事务隔离级别。我想这是我不知道的默认隔离级别。在Oracle 11g和SQL Server 2005中都没有。

此外，在WAIT命令(在第二个连接上)期间进行的INSERT操作不在事务内。应该防止这种数据丢失吗？

根据隔离级别，从表中选择所有行不会阻止新插入，它只会锁定我们读取的行。在SQL Server中，如果使用Serializable隔离级别，则它将阻止新行(如果它们已包含在选择查询中)。

http://msdn.microsoft.com/zh-CN/library/ms173763.aspx-

可序列化
指定以下内容：

• 语句无法读取已被其他事务修改但尚未提交的数据。
• 在当前事务完成之前，没有其他事务可以修改当前事务已读取的数据。
• 在当前事务完成之前，其他事务不能插入键值将落入当前事务中任何语句读取的键范围内的新行。

在Oracle中，默认事务隔离级别为已提交读。基本上，这意味着当查询开始时，Oracle将返回结果在SCN(系统更改号)处的结果。将事务隔离级别设置为可序列化意味着在事务开始时捕获了SCN，因此事务中的所有查询都返回该SCN的数据。无论其他会话和事务正在做什么，这都可以确保一致的结果。另一方面，Oracle可能会因为其他事务正在执行的活动而确定它无法序列化事务，因此可能会产生代价，因此我们必须处理此类错误。

托尼(Tony)与AskTom讨论的链接详细介绍了所有这些内容-我极力推荐。

如上所述，这是默认读提交模式的标准行为。 WAIT命令只会导致处理延迟，没有任何数据库事务处理的链接。

要解决此问题，我们可以：

• 将隔离级别设置为可序列化，但随后会出现ORA-错误，我们需要通过重试来处理该错误！另外，我们可能会受到严重的性能影响。
• 使用临时表先存储值
• 如果数据不是太大而无法容纳到内存中，则可以使用RETURNING子句将数据批量收集到嵌套表中，并且仅当嵌套表中存在该行时才删除它。

或者，我们可以使用快照隔离来检测丢失的更新：

快照隔离何时起作用以及何时受到损害