Using C# and SQL Server, would there be any reason to put a single INSERT statement into a transaction?
I am reviewing code written by someone else. I can't understand why a transaction would be needed since there is only one SQL statement.
+8
A:
It will already be in an implicit transaction. No need to wrap it in another redundant transaction.
Oded
2010-08-10 12:48:49
An `INSERT` statement is atomic, but it doesn't use an implicit transaction (at least not the type described in that link). If that were the case then an explicit `COMMIT` would be required.
LukeH
2010-08-10 13:02:47
@Luke - It does use implicit transactions. There is a `set` option as well though also called implicit transactions which is what I think the link is referring to.
Martin Smith
2010-08-10 13:03:54
@Luke Actually it looks like the preferred term may be auto commit transactions http://msdn.microsoft.com/en-us/library/ms187878.aspx I've always just thought explicit vs implicit as well.
Martin Smith
2010-08-10 13:08:49
A:
Programmatically, there would be no reason. The reason might be in readability though; wrapping an arbitrary code snippet in a transaction is a pretty good way of drawing the attention of future developers to it - or away from something else. It might also just be that the Best Practice at the time of writing was to wrap any dealings with the database in a transaction, just as wrapping stuff in try/catch-blocks.
My suggestion: If the code is clearer and more readable because of this, or if all other database calls are in transactions, leave it in. Otherwise, remove it.
Streamcap
2010-08-10 13:03:06