Menu
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.
In MS-SQL, is there any benefit from wrapping single selects, single updates, single inserts or single deletes in a transaction?
I suspect this is superstitious programming.
743
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
Transaction Statement means a statement, in paper or electronic form, which the manufacturer transferring ownership of the product certifies that the non-manufacturer sealed electronic[-] cigarette substance or the manufacturer sealed electronic cigarette substance is in compliance with the standards in this rule.
Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.
A SQL statement always runs in a transaction. If you don't start one explicitly, every SQL statement will run in a transaction of itself. The only choice is whether you bundle multiple statements in one transaction.
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).
EDIT: based on @Phillip's comment below... In current versions of SQL Server, Even Bulk Inserts and Truncate Table do write some data 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.
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.
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
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
