LINQ2SQL是否自动将ExecuteCommand放入事务中 [英] Does LINQ2SQL automatically put ExecuteCommand in a transaction

问题描述

是否从以下答案中引用文档: https://stackoverflow.com/a/542691/1011724

Does the documentation quotation from this answer: https://stackoverflow.com/a/542691/1011724

当您调用SubmitChanges时，LINQ to SQL会检查该调用是否在事务范围内，或者是否将Transaction属性(IDbTransaction)设置为用户启动的本地事务. 如果未找到任何事务，则LINQ to SQL会启动本地事务(IDbTransaction)并使用它执行生成的SQL命令.当所有SQL命令成功完成后，LINQ to SQL会提交本地事务并执行.返回.

When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

是否适用于.ExecuteCommand()方法?换句话说，我是否可以相信以下删除操作是在事务中处理的，如果删除失败，它将自动回滚，或者我需要手动告诉它使用事务，如果是，该如何操作?我应该使用TransactionScope吗?

apply to the .ExecuteCommand() method? In otherwords, can I trust that the following delete is handled in a transaction and will automatically rollback if it fails or do I need to manually tell it to use a transaction and if so how? Should I use TransactionScope?

using(var context = Domain.Instance.GetContext())
{
    context.ExecuteCommand("DELETE FROM MyTable WHERE MyDateField = {0}", myDate)
}

推荐答案

每个SQL语句(无论是否包装在显式事务中)都以事务方式发生.因此，无论是否进行显式事务处理，单个语句始终都是 atomic -它们要么完全发生，要么根本不发生.在上面的示例中，符合条件的所有行都将被删除，或者都不删除任何行，这与客户端代码的作用无关.实际上，没有办法让SQL Server删除部分行.即使拔出电源线也仅意味着在服务器重新启动并读取事务日志时，撤消已完成的所有操作.

Every SQL statement, whether or not wrapped in an explicit transaction, occurs transactionally. So, explicit transaction or not, individual statements are always atomic -- they either happen entirely or not at all. In the example above, either all rows that match the criterion are deleted or none of them are -- this is irrespective of what client code does. There is literally no way to get SQL Server to delete the rows partially; even yanking out the power cord will simply mean whatever was already done for the delete will be undone when the server restarts and reads the transaction log.

美中不足的是， match 哪些行可以根据语句锁定的方式而变化.该语句在逻辑上分为两个阶段，第一个阶段确定要删除的行，第二个阶段实际删除它们(处于更新锁定状态).例如，如果您发出了此语句，并且在其运行时发出了INSERT并插入了符合DELETE条件的行，则DELETE完成后该行是否在数据库中取决于哪个事务这些语句的隔离级别有效.因此，如果您希望删除所有行的实用保证，那么客户端代码的作用就在范围之内.但是，这超出了原始问题的范围.

The only fly in the ointment is that which rows match can vary depending on how the statement locks. The statement logically happens in two phases, the first to determine which rows will be deleted and the second to actually delete them (while under an update lock). If you, say, issued this statement, and while it was running issued an INSERT that inserted a row matching the DELETE criterion, whether the row is in the database or not after the DELETE has finished depends on which transaction isolation level was in effect for the statements. So if you want practical guarantees about "all rows" being deleted, what client code does comes into scope. This goes a little beyond the scope of the original question, though.

这篇关于LINQ2SQL是否自动将ExecuteCommand放入事务中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！