I have successfully implemented TransactionScope to control the updating of data across tables, however, I have run into a problem: before I can update one of the tables, I need to run a select statement against another table to find the current levels of inventory. My question is this, is there a danger of another user changing the inventory levels before the transaction commits or does TransactionScope guarantee this won't happen? Should I handle the logic for fetching inventory levels within a stored pro rather than within the BO?
Thanks
Bonio
|
Notes from the documentation: The TransactionScope class creates a transaction with a IsolationLevel of Serializable by default. Depending on your application, you might want to consider lowering the isolation level to avoid high contention in your application. |
|
We recommend that you perform only updates, inserts, and deletes within distributed transactions because they consume significant database resources. Select statements may lock database resources unnecessarily, and in some scenarios it may be necessary to use transactions for selects. |
So, my understanding would be that by default if you included the Select in the TransactionScope it would protect from the changes you are concerned with.
I don't know the specifics of what you're trying to accomplish, but one technique you can use to help avoid this is to have update queries do the math as part of the query rather than a three-step read, calculate, update. In other words, the update query is simply to "add 50" rather than to figure out explicitly what the new value should be. You could (should?) also get a value from another table via a subquery in the main update query rather than retrieve it all the way back to the business object. Remember the goal when using transactions is to keep them open for as short a time as possible.
This is a question I've had on Transaction Scope that I simply haven't had time to test for myself. I'm sure others need to know the answer too! (I'll answer my own question here eventually if someone doesn't beat me to it! :)
How does Transaction Scope affect a stored procedure that has multiple statements in it, where it fails partway thru. Let's say that the Delete Projects statement returns an error - does the Delete Assignemnts statement get rolled back as part of the built-in transaction scope logic?
Example:
CREATE PROCEDURE deleteProject (@id uniqueidentifier)
AS
DELETE Assignments WHERE ProjectId = @id
DELETE Projects WHERE Id=@id
RETURN
END
Yes, this would roll back, otherwise it wouldn't be transactional at all.
TransactionScope (for a single database connection) is really no different than if you manually created an ADO.NET SqlTransaction object and called Commit or Rollback on that object yourself using a try..catch block. It is just simpler to use, and of course it automatically escalates to use the DTC if your code uses multiple database connections.
Hoping somebody knows this...
the documentation says SQL 2005 is a requirement for TransactionScope, but as I recall it works with SQL 2000 as long as you have the DTC service running as well.
Verification?
From: DansDreams [mailto:cslanet@lhotka.net]
Sent: Wednesday, July 12, 2006 2:20 PM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] Question on TransactionScopeHoping somebody knows this...
the documentation says SQL 2005 is a requirement for TransactionScope, but as I recall it works with SQL 2000 as long as you have the DTC service running as well.
Verification?
Copyright (c) Marimer LLC