事务隔离问题还是错误的做法？

我正在帮助我的一些同事解决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隔离或者在WAIT命令期间INSERT出现这一事实.

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

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

http://msdn.microsoft.com/en-us/library/ms173763.aspx -

SERIALIZABLE指定以下内容:

语句无法读取已修改但尚未由其他事务提交的数据.

在当前事务完成之前,没有其他事务可以修改当前事务已读取的数据.

其他事务无法插入新行,其键值将落在当前事务中任何语句读取的键范围内,直到当前事务完成为止.

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

原谅语法,我没有测试过这段代码,但你应该能够理解:

INSERT INTO B SELECT * FROM A;

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

这样您就可以使用关系引擎强制执行不会删除任何新数据,并且您不需要在事务中执行这两个步骤.

更新:更正子查询中的语法

这可以在Oracle中实现:

Alter session set isolation_level=serializable;

这可以使用EXECUTE IMMEDIATE在PL/SQL中设置:

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

请参阅问问汤姆:交易隔离级别