There are lots and lots of questions on HOW to use Transactions. What I want to know is WHEN? Under what circumstances? What types of queries? Can Try-Catch blocks suffice instead? Etc...
I've designed a database with ~20 tables and ~20 stored procedures. Currently none of my SPs use a transaction, but there are numerous Try-Catch blocks throughout. The reason is because every time I tried to wrap them in a transaction the SP would cease to function and I would end up with missing data and worse off than had I used Trans.
So again...
Here's a little sample SP I wrote for renaming a product:
CREATE PROCEDURE spRenameProduct @pKey int = NULL, @pName varchar(50) AS BEGIN BEGIN TRY IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey) IF @pKeyExisting is null BEGIN update rProduct set IsValid = 1, Product = @pName where ID = @pKey END ELSE BEGIN update Request set ProductID = @pKeyExisting where ProductID = @pKey update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey delete from rProduct where ID = @pKey END END END TRY BEGIN CATCH END CATCH END
Now what if two people were using this at the exact same time? I really don't want to, nor do I have time (unfortunately), to get to fancy. K.I.S.S. is best in this case. :)
You use transactions when you have a group of actions that must be atomic (either all succeed or none succeed) Wrapping these actions in a transaction allows you to rollback actions that have already succeeded when you encounter an error.
The main idea of transactions is that when each of the statements returns an error, the entire modifications rollback to provide data integrity. On the other hand, if all statements are completed successfully the data modifications will become permanent on the database.
The primary benefit of using transactions is data integrity. Many database uses require storing data to multiple tables, or multiple rows to the same table in order to maintain a consistent data set. Using transactions ensures that other connections to the same database see either all the updates or none of them.
You use transactions when the set of database operations you are making needs to be atomic.
That is - they all need to succeed or fail. Nothing in between.
Transactions are to be used to ensure that the database is always in a consistent state.
In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.
Try/Catch
blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.
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