How to ensure thread-safety between inter-dependent database persistence managers.

[news.microsoft.com]

Hi all,
Assume we have two entity class.

Class1:
Name: House
Property:
ID:int
Name:String
Desktopsesktop[]

Class2:
Name: Desktop

And we have two corresponding Database Managers: HouseDBManager and
DesktopManager

If we want to insert a House instance into database, we need to call
HouseDBManager#Insert(house).
And the general step is inserting ID and Name into database, then insert
Desktops though DesktopManager.

HouseDBManager#Insert(House house){
Execute "Insert into House values(ID,NAME)"

foreach ( Desktop desktop in house.Desktops ){
desktopManager.Insert(desktop);
}
}

My question is how to ensure thread-safety? If the house holds five
desktops, inserting the first 4 runs successfully, but inserting the last
one fails. Since inserting desktop is achieved by DesktopManager,
transaction will already be committed after inserting each desktop, how can
we rollback the inserting on the first four desktops.

Maybe sharing an IDbTransaction and IDbCommand between different managers is
a feasible solution. But it looks strange and I want to know whether there
is better solution to achieve this goal.

Thank you.

 

[Marc Gravell]

A better approach might be TransactionScope in 2.0; this will give you
serializable isolation (by default) [providing thread safety at the database
layer], and no need to pass the transaction / connection objects around;

just use:

using(TransactionScope ts = new TransactionScope()) {
// your code snippet
ts.Complete();
}

or whatever the actual method is. You will need to manage your own rollback
to any IDs; I do this by bundling all this up into a helpper class that
snapshots (think: memento) the IDs / timstamps of each entity (via a simple
interface) so that it can reapply them if it fails.

Note that on SqlServer 2005 this uses a promotable transaction, so it is
very efficient for a single database; for 2000 it uses the DTC immediately
(even for single database) - but hey! it works!

Marc

 

[Dave Sexton]

Hi,

Your question isn't about thread-safety, it's about transactions.

You must either pass a transaction object to each method or create a global
transaction context.

If you're using the 2.0 framework, check out the System.Transactions
namespace. In particular, the TransactionScope class already works with
ADO.NET to provide global transaction support. Commands auto-enlist in the
global transaction (this can be prevented in the connection string but
auto-enlist is true by default).

using (TransactionScope scope = new TransactionScope())
{
HouseDBManager.Insert(house);
scope.Complete();
}

In earlier framework versions you can use COM+ transactions but they might be
more costly depending on your RDBMS. (If your're using Sql Server 2005 then
TransactionScope will be light-weight, unless it needs to be promoted to a
distributed transaction, which is done automatically).

"Features Provided by System.Transactions"
http://msdn2.microsoft.com/en-us/library/0abf6ykb.aspx

 

[Marc Gravell]

own rollback to IDs
Meaning: to your entity objects; the database will roll itself back happily

Marc

 

[zlf]

Got it. Thank you