我可以用保存点来替代 Oracle 中的新事务吗？
现在，我们用于插入记录集的过程是这样的：（
请注意，“记录集”是指一个人的记录及其地址、电话号码或任何其他连接表）。
- 开始交易。
- 插入一组相关的记录。
- 如果一切成功则提交，否则回滚。
- 返回步骤 1 获取下一组记录。
我们应该做更多类似的事情吗？
- 在脚本开头启动事务
- 为每组记录启动一个保存点。
- 插入一组相关记录。
- 如果出现错误则回滚到保存点，如果一切成功则继续。
- 在脚本开头提交事务。
在遇到 ORA-01555 的一些问题并阅读了一些 Ask Tom 文章（例如 这个），我正在考虑尝试第二个过程。当然，正如 Tom 指出的那样，启动新事务应该由业务需求来定义。第二个过程值得尝试还是一个坏主意？
Right now the process that we're using for inserting sets of records is something like this:
(and note that "set of records" means something like a person's record along with their addresses, phone numbers, or any other joined tables).
- Start a transaction.
- Insert a set of records that are related.
- Commit if everything was successful, roll back otherwise.
- Go back to step 1 for the next set of records.
Should we be doing something more like this?
- Start a transaction at the beginning of the script
- Start a save point for each set of records.
- Insert a set of related records.
- Roll back to the savepoint if there is an error, go on if everything is successful.
- Commit the transaction at the beginning of the script.
After having some issues with ORA-01555 and reading a few Ask Tom articles (like this one), I'm thinking about trying out the second process. Of course, as Tom points out, starting a new transaction is something that should be defined by business needs. Is the second process worth trying out, or is it a bad idea?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
事务应该是一个有意义的工作单元。但工作单元的构成取决于上下文。在 OLTP 系统中，工作单元将是单个人员及其地址信息等。但这听起来好像您正在实现某种形式的批处理，这会加载大量人员。
如果您遇到 ORA-1555 问题，几乎可以肯定是因为您有一个长时间运行的查询，提供的数据正在由其他事务更新。在循环内提交有助于 UNDO 段的循环使用，因此往往会增加您所依赖的提供读取一致性的段被重用的可能性。所以，不这样做可能是个好主意。
使用 SAVEPOINT 是否是解决方案是另一回事。我不确定在您的情况下这会给您带来什么优势。当您使用 Oracle10g 时，也许您应该考虑使用批量 DML 错误日志记录 相反。
或者，您可能希望重写驱动查询，以便它可以处理较小的数据块。如果不了解您的流程细节，我无法给出具体建议。但一般来说，与其为 10000 条记录打开一个游标，不如为每次弹出 500 行打开它 20 次。另一件需要考虑的事情是插入过程是否可以变得更加高效，例如使用批量收集和 FORALL。
A transaction should be a meaningful Unit Of Work. But what constitutes a Unit Of Work depends upon context. In an OLTP system a Unit Of Work would be a single Person, along with their address information, etc. But it sounds as if you are implementing some form of batch processing, which is loading lots of Persons.
If you are having problems with ORA-1555 it is almost certainly because you are have a long running query supplying data which is being updated by other transactions. Committing inside your loop contributes to the cyclical use of UNDO segments, and so will tend to increase the likelihood that the segments you are relying on to provide read consistency will have been reused. So, not doing that is probably a good idea.
Whether using SAVEPOINTs is the solution is a different matter. I'm not sure what advantage that would give you in your situation. As you are working with Oracle10g perhaps you should consider using bulk DML error logging instead.
Alternatively you might wish to rewrite the driving query so that it works with smaller chunks of data. Without knowing more about the specifics of your process I can't give specific advice. But in general, instead of opening one cursor for 10000 records it might be better to open it twenty times for 500 rows a pop. The other thing to consider is whether the insertion process can be made more efficient, say by using bulk collection and FORALL.
一些想法...
Some thoughts...