Transactions in Biz Layer or DAL? (.Net)

[dee-u]

Upon reading some discussion a few moments ago I came across some statements that there is a rule of not putting one's transactions in the DAL but on the Business Layer instead, may I know your opinions on this?

[szlamany]

Well I'm no expert in n-tier design...

But it makes sense to me that the DAL is a more granular "pipe to the data" set of routines...

Only the business-layer knows the bigger picture of when a transaction begins and ends.

Were you speaking about transactions in regard to begin and end?

[oldbear]

When considering this, please bear in mind the problems of the database administrator. Your database, as it scales, will need troubleshooting on all sorts of issues, such as locking and performance.

As more database specific code that goes into your app, rather than stored procs, the less a dba is able to do when troubleshooting.

For instance, if a transaction is enacted in .Net assembly, and that transaction causes a locking problem, it is hard for the dba to find. It is also next to impossible to rectify without recompiling the assemblies.

In disconnected applications, which are currently in vogue due to scalability and durability of operations, transactions across components are not recommended. This is due to the use of queues to move data around within the architecture.

Also, web service technology does not provide a useful mechanism for distributed transactions.

The whole essence of a distributed transaction is that multiple systems are locked during the process. This reduces scalability and performance.