如果我们不指定事务关键字，SQL Server是否会记录更改？

If we don't specify Begin Trans, Commit Trans, etc... and just use a simple SQL statement like this:

如果我们不指定Begin Trans,Commit Trans等...并且只使用这样的简单SQL语句:

delete from myTable where id = 1

inside the stored procedure,

在存储过程中,

will SQL Server write the changes to the transaction log? If yes then how can we use them while there is no Begin Trans keyword specified (we can't use RollBack).

SQL Server会将更改写入事务日志吗?如果是,那么在没有指定Begin Trans关键字时我们如何使用它们(我们不能使用RollBack)。
When we write stored procedures is it a good practice to always use the transaction keywords or is it better to use them when they seem to be appropriate (for example when we need to rollback a change)?

当我们编写存储过程时,总是使用事务关键字是一个好习惯,还是在它们看起来合适时更好(例如当我们需要回滚更改时)?

edit: thank u guys. but how about the second part of my question 1?? how do i recover my data by using the transaction log??

编辑:谢谢你们。但问题的第二部分怎么样?如何使用事务日志恢复数据?

2 个解决方案

#1

By default SQL Server operates in auto commit mode meaning each statement is automatically in its own transaction and gets committed automatically and written to the transaction log (or rolled back automatically in case of error). Once a transaction is committed it cannot be rolled back and you would need to roll forward from the last backup to rectify any mistakes (subject to a last backup being available and recovery model).

默认情况下,SQL Server以自动提交模式运行,这意味着每个语句都自动在其自己的事务中,并自动提交并写入事务日志(或在出现错误时自动回滚)。提交事务后,无法回滚,您需要从上次备份前滚以纠正任何错误(取决于上次备份可用和恢复模型)。

You can alter this behaviour by using set implicit_transactions on after which nothing is committed until you issue a commit command.

您可以使用set implicit_transactions on更改此行为,之后在发出commit命令之前不会提交任何内容。

With regard to your question about stored procedures if you have multiple statements that you need treated atomically you would need explicit transaction control statements otherwise it is optional.

关于存储过程的问题如果你有多个需要原子处理的语句,你需要显式的事务控制语句,否则它是可选的。

#2

SQL Server always and only works with the transaction log available and enabled, there is by design no way to tell SQL Server to do not use it.

SQL Server始终只能与可用和启用的事务日志一起使用,因此设计无法告诉SQL Server不要使用它。

this does not exclude that in some cases you have certain commands or options to commands which allow you to tell SQL Server to minimize the log writing, for example a TRUNCATE differs from a DELETE because data is removed and won't be logged in the log. In other cases you can specify the log level for some bulk commands to minimize the logging.

这并不排除在某些情况下你有一些命令的命令或选项,允许你告诉SQL Server最小化日志写入,例如TRUNCATE与DELETE不同,因为数据被删除,不会记录在日志中。在其他情况下,您可以为某些批量命令指定日志级别以最小化日志记录。

Said so, SQL Server does commit immediately if you execute a command in the Management Studio or from code and you do not specify any transaction (Begin, Commit or rollback).

这样说,如果您在Management Studio中执行命令或从代码执行命令,并且您没有指定任何事务(Begin,Commit或rollback),SQL Server会立即提交。

I am not much a SQL guy but I know there is the concept of AutoCommit which could be also changed or set to a different level than the default one.

我不是一个SQL人,但我知道AutoCommit的概念也可以更改或设置为与默认值不同的级别。

your delete above will apply the changes immediately and info will be written in the log.

您上面的删除将立即应用更改,信息将写入日志。

In general in stored procedures we do not inject BEGIN and COMMIT or ROLLBACK within the stored procedure body itself but we use BeginTrans/Commit or Rollback from the C# DAL.

通常在存储过程中,我们不会在存储过程体本身中注入BEGIN和COMMIT或ROLLBACK,但我们使用来自C#DAL的BeginTrans / Commit或Rollback。

there are also other ways to do, if inside the same stored proc you touch multiple tables it could make sense to have a local transaction in there, but would also work to do it from code.

还有其他方法可做,如果在同一个存储过程中触摸多个表,那么在那里有一个本地事务是有意义的,但也可以从代码中完成它。

#1