在所有查询中都有 TRANSACTION [英] Having TRANSACTION In All Queries

问题描述

您认为始终围绕存储过程中的 SQL 语句进行事务是一种好习惯吗?我正准备优化我公司中的这个遗留应用程序，我发现的一件事是每个存储过程都有 BEGIN TRANSACTION.即使只有一个选择或更新语句的过程也有一个.我认为如果执行多个操作，而不是一个操作，使用 BEGIN TRANSACTION 会很好.我可能是错的，这就是为什么我需要别人给我建议.谢谢你们的时间，伙计们.

Do you think always having a transaction around the SQL statements in a stored procedure is a good practice? I'm just about to optimize this legacy application in my company, and one thing I found is that every stored procedure has BEGIN TRANSACTION. Even a procedure with a single select or update statement has one. I thought it would be nice to have BEGIN TRANSACTION if performing multiple actions, but not just one action. I may be wrong, which is why I need someone else to advise me. Thanks for your time, guys.

推荐答案

这是完全没有必要的，因为每个 SQL 语句都是以原子方式执行的，即.就好像它已经在自己的事务中运行一样.事实上，打开不必要的事务会导致锁增加，甚至死锁.忘记将 COMMIT 与 BEGIN 匹配会使事务保持打开状态，只要与数据库的连接处于打开状态并干扰同一连接中的其他事务.

It is entirely unnecessary as each SQL statement executes atomically, ie. as if it were already running in its own transaction. In fact, opening unnecessary transactions can lead to increased locking, even deadlocks. Forgetting to match COMMITs with BEGINs can leave a transaction open for as long as the connection to the database is open and interfere with other transactions in the same connection.

这样的编码几乎可以肯定意味着编写代码的人在数据库编程方面不是很有经验，而且肯定还有其他问题.

Such coding almost certainly means that whoever wrote the code was not very experienced in database programming and is a sure smell that there may be other problems as well.

这篇关于在所有查询中都有 TRANSACTION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！