Transactions - In the SP or BO?

djjlewis posted on Tuesday, June 20, 2006

Hi all,

I know CSLA.NET 2.0 adds some good functionality for transaction handling (manual, TransactionScope, ES etc), but what are people thoughts on making transactions the responsibility of the business object and not at the database level in a stored procedure?

My own thought is how you think of each logical layer of the app as being indepenant from each other i.e. if you know the database will only be accessed by the business objects, then it is fine for the transactions to be managed at the BO level, but if later down the line another system reuses the same SPs, then the transaction code may never run.
Should we place transactions in the 'lowest common denominator' layer in the same way business logic 'could' be in the UI layer but also needs to be in the BO layer?

Any thoughts on this?

Regards,

Dan

Dawn replied on Tuesday, June 20, 2006

I think some times you can't just put transaction handling in a stored procedure, like a root object with a collection of child, how could you pass this to one stored procedure, BO is defined by behavior, and it's data may come or save to different table, different database or other data storge. So using TranscatoinScope, ES etc, we can focus on business logical, let them care the low level data acess.

ajj3085 replied on Tuesday, June 20, 2006

I think your BO should handle the transactions; that way you can have different BOs calling some of the exact same procedures. 

The grouping of data which must commit as a single unit is a business rule, and can change.

HTH
Andy

chriss replied on Tuesday, June 20, 2006

In "principle" the SP's shouldn't be used by any other application as you would be bypassing the BO rules/validation.

However I can think when transactions should be used in SP's; when your BO sits accross more than one DB table (i.e. updates more than one DB table). Then that would make sense. 

ajj3085 replied on Tuesday, June 20, 2006

chriss:

However I can think when transactions should be used in SP's; when your BO sits accross more than one DB table (i.e. updates more than one DB table). Then that would make sense. 

Actually I would argue this is where you'd have the BO control the transaction.  The reason all those tables must commit changes as a unit is because they are all touched by your BO.

chriss replied on Tuesday, June 20, 2006

I may be missing the somthing still, but I mean when you want to delegate responsibility to update more than one DB table via a single stored proc called by  the BO's DataPortals Update/Delete methods. When/why one might need to do this is another question.

 

djjlewis replied on Tuesday, June 20, 2006

I think I just needed a reality check... you're right that the BOs should considered the only safe access to an application and therefore would contain all data access/transactions/rule checking logic etc. It is possible that someone could bypass the transactions and use the SPs directly but I guess that should be treated as violation of the application design and would be no different than updating data directly in the table...something we hope won't happen very often!

The only exception to this would be things like reporting tools accessing the SPs directly, but of course this would predominately be 'select' queries and therefore transactions would not be an issue.

Thanks for the input,

Dan.