什么是“合理”？ SQL 游标保持打开状态的时间长度？
在您的应用程序中，在提交或回滚之前保持事务打开的“长时间”是多少？ 分钟？ 秒？ 小时？
在哪个数据库上？
In your applications, what's a "long time" to keep a transaction open before committing or rolling back? Minutes? Seconds? Hours?
and on which database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
@lomaxx、@ChanChan：据我所知，游标仅是 SQL Server 和 Sybase（T-SQL 变体）上的问题。 如果您选择的数据库是 Oracle，那么游标就是您的朋友。 我见过许多使用游标实际上提高了性能的案例。 光标是一种非常有用的机制，说实话，说“如果你使用光标我们就解雇你”之类的话有点荒谬。
话虽如此，您只想在所需的绝对最小值内保持游标打开。 如果不了解问题领域，指定最大时间是任意且毫无意义的。
@lomaxx, @ChanChan: to the best of my knowledge cursors are only a problem on SQL Server and Sybase (T-SQL variants). If your database of choice is Oracle, then cursors are your friend. I've seen a number of cases where the use of cursors has actually improved performance. Cursors are an incredibly useful mechanism and tbh, saying things like "if you use a cursor we fire you" is a little ridiculous.
Having said that, you only want to keep a cursor open for the absolute minimum that is required. Specifying a maximum time would be arbitrary and pointless without understanding the problem domain.
一般来说，我同意其他答案：尽可能避免游标（在大多数情况下）并尽快关闭它们。
但是：这完全取决于您所在的环境。
Generally I agree with the other answers: Avoid cursors when possible (in most cases) and close them as fast as possible.
However: It all depends on the environment you're working in.
@nineside：除了性能问题之外，它还涉及使用正确的工具来完成工作。 如果选择将光标从查询移到代码中，我认为 100 次中有 99 次最好将循环逻辑放入某种托管代码中。 这样做可以让您获得使用调试器、编译时错误检查、类型安全等的优势。
我对问题的回答仍然是相同的，如果您使用游标，请尽快关闭它，在 Oracle 中我也会尝试使用显式游标。
@ninesided: performance issues aside, it's also about using the right tool for the job. Given the choice to move the cursor out of your query into code, I would think 99 times out of 100 it would be better to put that looping logic into some sort of managed code. Doing so allows you to get the advantages of using a debugger, compile time error checking, type saftey etc.
My answer to the question is still the same, if you're using a cursor, close it ASAP, in oracle I'd also be trying to use explicit cursors.
交易：分钟。
光标：最多 0 秒，如果您使用光标，我们会解雇您。
当你考虑到我们处于一个高可用性的 Web 环境中，必须运行 sql server，并且我们甚至不允许存储过程，因为无法准确地对它们进行版本控制和维护时，这并不荒谬。 如果我们使用 Oracle 也许会。
transactions: minutes.
Cursors: 0seconds maximum, if you use a cursor we fire you.
This is not ridiculous when you consider we are in a high availability web environment, that has to run sql server, and we don't even allow stored procs because of inability to accurately version and maintain them. If we were using oracle maybe.
我可能会因此而受到批评，但您确实应该尝试避免使用游标，因为它们会严重影响性能。 如果必须使用它，则应使其保持打开状态的绝对最短时间，以便尽快释放被游标阻塞的资源。
I'm probably going to get flamed for this, but you really should try and avoid using cursors as they incur a serious performance hit. If you must use it, you should keep it open the absolute minimum amount of time possible so that you free up the resources being blocked by the cursor ASAP.