SQL 围绕单个语句的事务有什么作用？

What does a transaction around a single statement do?

提问by MatthewMartin

I understand how a transaction might be useful for co-ordinating a pair of updates. What I don't understand is wrapping single statements in transactions, which is 90% of what I've ever seen. In fact, in real life code it is more common in my experience to find a series of logically related transactions each wrapped in their own transaction, but the whole is not wrapped in a transaction.

我了解事务对协调一对更新有何用处。我不明白的是在事务中包装单个语句，这是我见过的 90%。事实上，在现实生活中的代码中，根据我的经验，更常见的是发现一系列逻辑相关的事务，每个事务都包裹在自己的事务中，但整体并没有包裹在一个事务中。

In MS-SQL, is there any benefit from wrapping single selects, single updates, single inserts or single deletes in a transaction?

在 MS-SQL 中，在事务中包装单个选择、单个更新、单个插入或单个删除有什么好处吗？

I suspect this is superstitious programming.

我怀疑这是迷信编程。

采纳答案by Charles Bretana

It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly "In a Transaction" whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows).

它什么都不做。所有单独的 SQL 语句（除了极少数例外，如无日志的批量插入或截断表）自动地“在事务中”，无论您是否明确表示。（即使它们插入、更新或删除数百万行） .

EDIT: based on @Phillip's comment below... In current versions of SQL Server, Even Bulk Inserts and Truncate Table do write somedata to the transaction log, although not as much as other operations do. The critical distinction from a transactional perspective, is that in these other types of operations, the data in your database tables being modified is not in the log in a state that allows it to be rolled back.

编辑：基于@Phillip 在下面的评论......在当前版本的 SQL Server 中，即使是批量插入和截断表也会将一些数据写入事务日志，尽管不像其他操作那样多。从事务的角度来看，关键的区别在于，在这些其他类型的操作中，被修改的数据库表中的数据不在日志中，处于允许回滚的状态。

All this means is that the changes the statement makes to data in the database are logged to the transaction log so that they can be undone if the operation fails.

所有这一切都意味着语句对数据库中的数据所做的更改会记录到事务日志中，以便在操作失败时可以撤​​消这些更改。

The only function that the "Begin Transaction", "Commit Transaction" and "RollBack Transaction" commands provide is to allow you to put two or more individual SQL statements into the same transaction.

“开始事务”、“提交事务”和“回滚事务”命令提供的唯一功能是允许您将两个或多个单独的 SQL 语句放入同一个事务中。

EDIT: (to reinforce marks comment...) YES, this could be attributed to "superstitious" programming, or it could be an indication of a fundamental misunderstanding of the nature of database transactions. A more charitable interpretation is that it is simply the result of an over-application of consistency which is inappropriate and yet another example of Emersons euphemism that:

编辑：（加强标记评论...）是的，这可能归因于“迷信”编程，或者它可能表明对数据库事务的性质存在根本性误解。一种更善意的解释是，它只是过度应用一致性的结果，这是不恰当的，也是爱默生委婉语的另一个例子：

A foolish consistency is the hobgoblin of little minds,
adored by little statesmen and philosophers and divines

愚蠢的一致性是小头脑的妖精，
受到小家、哲学家和神学家的崇拜

回答by Philip Kelley

As Charles Bretana said, "it does nothing" -- nothing in addition to what is already done.

正如查尔斯·布雷塔纳 (Charles Bretana) 所说，“它什么都不做”——除了已经完成的事情之外什么也不做。

Ever hear of the "ACID" requirements of a relational database? That "A" stands for Atomic, meaning that either the statement works in its entirety, or it doesn't--and while the statement is being performed, noother queries can be done on the data affected by that query.BEGIN TRANSACTION / COMMIT "extends" this locking functionality to the work done by multiple statements, but it adds nothing to single statements.

听说过关系数据库的“ACID”要求吗？“A”代表 Atomic，这意味着该语句要么完全起作用，要么不起作用——并且在执行语句时，不能对受该查询影响的数据执行其他查询。BEGIN TRANSACTION / COMMIT 将此锁定功能“扩展”到由多条语句完成的工作，但它不会向单个语句添加任何内容。

However,the database transaction log is alwayswritten to when a database is modified (insert, update, delete). This is not an option, a fact that tends to irritate people. Yes, there's wierdness with bulk inserts and recovery modes, but it still gets written to.

但是，数据库事务日志总是在修改（插入、更新、删除）数据库时写入。这不是一个选项，一个容易激怒人们的事实。是的，批量插入和恢复模式很奇怪，但它仍然被写入。

I'll name-drop isolation levels here too. Fussing with this will impact individual commands, but doing so will still not make a declared-transaction-wrapped query perform any differently than a "stand-alone" query. (Note that they can be very powerful and very dangeroug with multi-statement declared transactions.) Note also that "nolock" does notapply to inserts/updates/deletes -- those actions always required locks.

我也会在这里命名删除隔离级别。对此大惊小怪将影响单个命令，但这样做仍然不会使声明事务包装的查询执行与“独立”查询有任何不同。（请注意，他们是非常强大，并与多语句非常dangeroug宣布的交易。）另请注意，“NOLOCK”并不能适用于插入/更新/删除-那些总是需要的锁的行为。

回答by GWR

For me, wrapping a single statement in a transaction means that I have the ability to roll it back if I, say, forget a WHERE clause when executing a manual, one-time UPDATE statement. It has saved me a few times.

对我来说，在事务中包装单个语句意味着如果我在执行手动一次性 UPDATE 语句时忘记了 WHERE 子句，我有能力回滚它。它救了我几次。

e.g.

例如

--------------------------------------------------------------
CREATE TABLE T1(CPK INT IDENTITY(1,1) NOT NULL, Col1 int, Col2 char(3));
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');
INSERT INTO T1 VALUES (101, 'abc');

SELECT * FROM T1


--------------------------------------------------------------
/* MISTAKE SCENARIO     (run each row individually) */
--------------------------------------------------------------
BEGIN TRAN YOUR_TRANS_NAME_1;   /* open a trans named YOUR_TRANS_NAME_1 */
    UPDATE T1 SET COL2 = NULL;  /* run some update statement */
    SELECT * FROM T1;       /* OOPS ... forgot the where clause */
ROLLBACK TRAN YOUR_TRANS_NAME_1;    /* since it did bad things, roll it back */
    SELECT * FROM T1;       /* tans rolled back, data restored. */



--------------------------------------------------------------
/* NO MISTAKES SCENARIO (run each row individually) */
--------------------------------------------------------------

BEGIN TRAN YOUR_TRANS_NAME_2;
    UPDATE T1 SET COL2 = 'CBA' WHERE CPK = 4;   /* run some update statement */
    SELECT * FROM T1;               /* did it correctly this time */

COMMIT TRAN YOUR_TRANS_NAME_2           /* commit (close) the trans */

--------------------------------------------------------------

DROP TABLE T1

--------------------------------------------------------------

回答by GWR

One possible excuse is that that single statement could cause a bunch of other SQL to run via triggers, and that they're protecting against something going bad in there, although I'd expect any DBMS to have the common sense to use implicit transactions in the same way already.

一个可能的借口是，该单个语句可能会导致一堆其他 SQL 通过触发器运行，并且它们正在防止那里出现问题，尽管我希望任何 DBMS 都具有在其中使用隐式事务的常识已经一样了。

The other thing I can think of is that some APIs allow you to disable autocommit, and the code's written just in case someone does that.

我能想到的另一件事是某些 API 允许您禁用自动提交，并且编写代码以防万一有人这样做。

回答by Quassnoi

When you start an explicit transaction and issue a DML, the resources being locked by the statement remain locked, and the results of statement are not visible from outside the transaction until you manually commit or rollback it.

当您启动显式事务并发出 a 时DML，被语句锁定的资源保持锁定状态，并且在您手动提交或回滚它之前，语句的结果在事务外部是不可见的。

This is what you may or may not need.

这是您可能需要也可能不需要的。

For instance, you may want to show preliminary results to outer world while still keeping a lock on them.

例如，您可能希望向外部世界显示初步结果，同时仍保持对它们的锁定。

In this case, you start another transaction which places a lock request before the first one commits, thus avoiding race condition

在这种情况下，您启动另一个事务，在第一个提交之前放置锁定请求，从而避免竞争条件

Implicit transactions are commited or rolled back immediatley after the DMLstatement completes or fails.

隐式事务在DML语句完成或失败后立即提交或回滚。

回答by Martin Vajsar

SQL Server has a setting which allows turning autocommit off for a session. It's even the default for some clients (see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017)

SQL Server 有一个设置，允许关闭会话的自动提交。它甚至是某些客户端的默认设置（参见https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017）

Depending on a framework and/or a database client you use, not putting each individual command into its own transaction might cause them to be all lumped together into a default transaction. Explicitly wrapping each of them in a transaction clearly declares the intent and actually makes sure it happens the way the programmer intended, regardless of the current autocommit setting, especially if there isn't a company-wide policy on autocommit.

根据您使用的框架和/或数据库客户端，不将每个单独的命令放入其自己的事务中可能会导致它们全部集中到一个默认事务中。将它们中的每一个显式地包装在一个事务中清楚地声明了意图，并实际上确保它以程序员预期的方式发生，无论当前的自动提交设置如何，特别是如果没有关于自动提交的全公司政策。

If the begin tran / commit tran commands are being observed in the database (as per your comment here), it is also possible that a framework is generating them on behalf of an unsuspecting programmer. (How many developers closely inspect SQL code generated by their framework?)

如果在数据库中观察到 begin tran / commit tran 命令（根据您在此处的评论），则框架也可能代表毫无戒心的程序员生成它们。（有多少开发人员仔细检查由他们的框架生成的 SQL 代码？）

I hope this is still relevant, despite the question being somewhat ancient.

我希望这仍然是相关的，尽管这个问题有点古老。