OOPhoto - Handling Database Transactions With Ease
The latest OOPhoto application can be experienced here.
The latest OOPhoto code can be seen here.
A while back, you might remember me freaking out a bit about handling database transactions in OOPhoto, my latest attempt at learning object oriented programming in ColdFusion. One of the things that is nice about procedural code is that you know where all the code is being called from (that page) and wrapping a whole bunch of code in CFTransaction tags is never a problem. When you are using object oriented programming, on the other hand, it's a totally different story; you never know where the objects are being called from. That's part of the beauty and the initial frustration of object oriented programming - the objects don't have and shouldn't have to know about anything else outside of themselves (well, almost nothing else).
Because of the environmental-agnostic properties of method execution in object oriented programming, what I ended up doing was creating two functions for methods that might require a database transaction. One uses transactions, the other one does not. Take, for example, the Save() method on my service objects; for saving a transient object (a non-cached object), I created two save methods:
- Save()
- SaveWithTransaction()
I got the basis of this idea (using two methods) from a brief conversation that I had with Peter Bell a couple of weeks ago. At first, I thought I would end up having to duplicate the logic in both methods, which was disappointing to say the least. And, in fact, this is how I went about implementing the methods initially. But then, half way through my first SaveWithTransaction() method, it dawned on me - why not just create the CFTransaction tags and then turn around and call the existing Save() method. After all, the only added functionality I wanted in SaveWithTransaction() was the CFTransaction tag itself; every other piece of logic was already built into the existing Save() method.
And so it was that I created a SaveWithTransaction() method that looked like this:
<cffunction
name="SaveWithTransaction"
access="public"
returntype="any"
output="false"
hint="I take a photo object and persist it (using a database transaction).">
<!--- Define arguments. --->
<cfargument
name="Photo"
type="any"
required="true"
hint="I am the photo object to be persisted."
/>
<!--- Wrap the whole interaction in a transaction. --->
<cftransaction action="begin">
<cftry>
<!---
Because the functionality for saving already
exists, let's just turn around and call our
existing Save() method (that works in a non-
transaction way).
Return the object that is passed back from
our Save() method.
--->
<cfreturn THIS.Save( ARGUMENTS.Photo ) />
<!--- Catch any errors. --->
<cfcatch>
<!--- Roll back transaction. --->
<cftransaction action="rollback" />
<!--- Rethrow error. --->
<cfrethrow />
</cfcatch>
</cftry>
</cftransaction>
</cffunction>
As you can see, the SaveWithTransaction() method just adds the CFTransaction functionality including the transaction rollback if the save action errors out. Other than that, the method simply turns around and calls THIS.Save() which handles the object persistence implementation.
Because the method calls of an object are supposed to be environment-agnostic, I had to make it a rule that no "Save" method would turn around and invoke the transactional version of another object's save method. By enforcing this, I am ensuring that a transaction is only created at the primary object persistence level and not for any of the composed objects. What this ultimately means is that a transaction-involving method will only ever be invoked by the Controller, never by a member of the domain model itself.
I am very satisfied with this solution. It allows me to cleanly organize my transactions. But almost more importantly, it allows me to add parallel versions of a feature (ie. Save, Delete) without duplicating any of my logic. That just feels clean to me.
Want to use code from this post? Check out the license.
Reader Comments
@Ben,
That seems like a great idea. I too am trying to get a handle on the OOP approach, and a few weeks ago I encountered the Transaction issue.
I had a transaction in my method which worked fine when the method was the only action I was calling from my code. When I later called that method as part of a larger series of steps, it ended up nested within another transaction, which generated an error.
Having the ability to call two methods seems like a nice approach, and the ease of creating the SaveWithTransaction() method keeps it pretty painless!
@Ben,
What I like about this approach, Ben, first is that it's a simple start (doesn't require you to have some sort of additional DataSet object or something like an ORM) and then second that doing it the way you have done here means that you don't have to have the transaction code directly in your Controller. Yes, the Controller has to 'know' about transactions, but that's OK, it's making all your decisions in any case. What the Controller doesn't have to do is decide what 'DB transaction' means ... so, going back to some of the comments very early in the OOPhoto project: this way someone could theoretically change how the data layer deals with transactions without having to alter the Controller at all.
Nice solution that should be simple enough to not obstruct the ongoing project.
-jfish
Great stuff as usual, but this almost seems to cry out as another reason to have method overloading in CF.
Love the series Ben. I was just thinking, this method of having dual methods where one is transactional and one isn't might be a great candidate for a "missing method" handler.
Your onMissingMethod function could just check for the suffix "withTransaction" (or any pattern you decide) on a method call and if there's a pattern match, it could wrap a dynamic call to the non-transactional version of the function passing along all parameters using nearly all of the same code in your current "saveWithTransaction" function.
You could place the onMissingMethod handler in a base class that you extend so that you don't have to actually have 2 versions of each function in each of your CFCs. It would just become an understood convention that if you need to use a transaction, you call the function you want with the suffix "withTransaction"
Keep up the brain dumping, love it.
- Kurt
Just as a side note, this could be written just as:
<cftransaction>
<cfreturn THIS.Save( ARGUMENTS.Photo ) />
</cftransaction>
And you would be done.
If an error happens inside the <transaction> tags, then the db is automatically rolled back.
I've seen a few people use try/catch blocks, just like this, and it's not necessary.
Now, what you need is some clever state storage, so that you can next saveinTransaction() calls ;o)
Keep up the good work!
@Jason,
Ahhh, so that's what the "J" stands for :)
I think one thing we could do to overcome having the Controller to know about the transactions is to make the generic "Save" method the one with Transactions. Then, once inside the model, we could have the Service objects call SaveWithoutTransaction().
It doesn't seem to roll off the tongue like the other one, but at least this way, to the outside world, you are always calling "Save" or "Delete" and it is actually the model that is then 100% worrying about how to handle transactions internally.
@Kurt,
It's funny you mention that because on walk home last night, after posting this, I thought the same thing. Why not just let the OnMissingMethod() handle it. After all, if all the method is going to do is use the CFTransaction tag and then call around and call another method and pass along ALL of the same arguments, its can be easily factored out. And, especially with the ArgumentCollection feature, it's almost a no-brainer.
@Mark,
I actually used to do transactions that way. It seems really easy and straight forward. But then, one day, someone told me that that was not how it was meant to be used; that to rely on the error to automatically roll-back was not actually the intent.
Of course, I never bothered to check. I did, however, just now, look up the documentation, and you are 100% correct:
If a cfquery tag generates an error within a cftransaction block, all cfquery operations in the transaction roll back.
That's what happens when you blindly follow people's advice :( Shame on me.
Although looking at that documentation again, it does mention specifically CFQuery tag errors. I can't imagine that the CFTransaction tag would selectively roll back for cfquery errors but not other errors thrown in the code.
Hey, what is the SaveInTransaction() idea? I am not sure what the intent would be?
@Ben,
Love the idea of pushing the call type out of the Controller and letting the Model decide what Save and Delete have to mean in any given context. Guess that also makes sense when thinking about a few fat Services that are in turn implementing combinations of many, more table-based DAO objects or whatever: the top tier of the model is the only one that really knows how it's assembling DAO calls at any rate, so the Transaction decision almost has to be there.
As for the rollback use of cftransaction, yes, you can manually flag it, but you don't need to. In good coding on the DB side, such as using T-SQL to build stored procedures in SQL Server, you want to manually trap for errors and flag the rollbacks when appropriate, but CF has (once again) encapsulated that for us, which is awesome.
Also, you are right that any which occurs before the closing /cftransaction tag will cause the rollback, even if the error is unrelated to the database interactions.
Sorry, that last sentence was supposed to be:
"... you are right that any error which occurs ..."
-jfish
@Jason, Mark,
Thanks for the CFTransaction insights. I will put my transaction code on a diet immediately (except where, of course, I do need to catch the error and actually do something with it, such as with an API call).
I have updated my service objects, moving the transaction control into the BaseService.cfc and out of the individual services:
www.bennadel.com/index.cfm?dax=blog:1311.view
This uses OnMissingMethod() for generic transaction control.
Its true about the transactions in OO. Ive had serious problems when calling DAOs with coldfusion with session and database swapping. In the end i had to use CFLOCK inside the DAO but it slows the whole show down alot.
@Ben, FYI - I was testing cftransaction with and without the cftransaction action="rollback" inside of the cfcatch block and it did not work without it. In other words it did not rollback the records when the insert query bombed out. Maybe just wrapping the query blocks with cftransaction works in some cases, but in my case it did not. In case your curious, the sequence of tags in my test was cftransaction,cftry,cfloop,cfif,cfquery(insert),cfcatch. Hope that makes sense and hope this helps.
@Joey,
I think the rollback happens automatically if you do not include a CFTry / CFCatch. But, sine the CFTry / CFCatch block traps the error without letting it bubble up, the parent CFTransaction block has no idea that an error occurred.
@Ben,
Good point. I will have to agree with you on that one.
My host has encourage me to use <cftransaction> around my queries. They say it allows them to physically close database connections ?
Is that true ?
CFTRANSACTION does not impact the connection at all, it only impacts the way the RDBMS itself stages and posts data. Within a transaction, all changes are staged and not committed to the tables until all changes are successfully completed, and then everything is posted at the end. In MSSQL, it's the equivalent of using BEGIN TRANSACTION at the start of database code and COMMIT at the end, and has no impact on the connection between CF and the database.
@ thanks Jason <br/>
-Ok this might be of the page topic then. My provider has said that i am over utilizing their mysql sockets, I only have a couple hundred people a day visit. What would keep these connections open. They have cut me back to 6 sockets till i fix my problem. arrr - is their a post anywhere that shows a good tutorial on this issue optimizing mysql database connections for cfml.Would bad querys not wraped in cftransaction leave the connection open.
@James,
In CF Administrator, where your datasource is configured, they should have the "Maintain Connections" option checked (under Advanced Settings). And, frankly, they should also have "Limit Connections" set to something reasonable. The recommendation in CF since the move to Java (CFMX+) has been 5-7 connections per CPU, and that has always worked well for me, even in large clustered environments. (Don't know if that tuning advice is still valid, but it seems to do well in my environments.)
Could this "over utilization" be caused be google flying around in my site. I have made some technical improvements to a couple sites and goggles been pretty aggressive reading my alterations. -
Last question promise !_!
I have a possible 150 connections supposedly. Would a connection be used up by requesting an external image from another site via http. So if i had ten pics on a page it would essential used 10 connections for that page. For one user ! I read about it just would like to confirm.
From the original question about CFTRANSACTION, I would assume that your hosting co is referring to DB connections, not HTTP connections, so there should be no correlation between number of images (or JS libraries or whatever) on the page and number of database calls.
I think i figured out the issue.
Its my google ads - some of those visual ads are eating up l2 page requests. evil little Google, bad Google. so if i have a potential 150 request at any given time. that could be used easily by 8 or less people. - ("aaaa i felt my brain get bigger") . please correct me if im wrong. I just deleted my visual ads and the problems went away.
Hmmm ... I'm confused. Are the ads on a different server with the same Google Ads code attached or something? Why would each ad create a separate Google Ads call? That may be important info for some of our ad-heavy sites!
I dont get it also, because its a java script call to google, from the users machine i thought and that shouldn't use up your connections to the server. But I deleted the Google visual ads and everything is fine. Maby a java script conflict ? Probably a fluke. reduced my page connections by 3/4 from 35 to 10. *
New thought - i belive that the large google files and scripts (sometimes) are just running on and on not completing causing my page to not load completly. You can see this in fire bug. So - this is why i beleive my site was getting the 503 error. It coincides with me going crazy putting google ads in my site a couple of days ago. Bad google. ok im done. I may just be blowin smoke ( im self taught ).
@James,
I have often seen the Google libraries spin on load, too. Not sure that I've seen it throw 503's, but then I have definitely never looked at the logs when I've noticed a library spinning ... interesting possibility.
I figured out what was wrong google has just assigned me a custom crawl rate that i cant change without emailing them. I checked my access logs and whooooooly gooogle bot ------- en! I had to do some sitehouse keeping and upgrade my account. Fun Weekend
@James, @Jason,
I doubt that Google has anything to do with your database connections. The ads happen on a completely different machine (calls to Google's servers), and any crawl rate issue would simply be like having a lot of people hit the site at the same time.
I have never heard of database connection issues in so much as that they are remaining open. I am not a server-side expert by any stretch, but I believe that ColdFusion handles all the database connections well; as @Jason said, you can maintain the connections, which is recommended for efficiency, but the server should limit the amount that any connection pool can have.
This is all a bit out of my area of understanding, but it sounds very suspicious :)
This is the exact quote from a site on a gdiddy server. - They have since fixed my connections, but I didnt know this happening becouse I thought those connections would close to - but this is a Quote-
It has come to our attention that your hosting account itemtrader.ca is causing an over-utilization of the shared resources on the server, more specifically MySQL and ColdFusion. There are multiple ColdFusion scripts that are attempting to access a MySQL databases but are not coded properly and the connections are continuing to timeout. This is allowing 30 connections to queue up on the database server and is then causing issues with other customers on the same MySQL server and also with other ColdFusion customers. --
so i added timeouts and - they also asked that I add cftransaction to my querys, - so far so good. - any other suggestions would be great.
And the google ad thing - It was just hanging my site. Half the page would load, and then one of their fancy picture banners would pop in. An the page would stall out. ? Probibly conflicting javascript. You can see it in firebug.
@James,
The MySQL stuff is definitely interesting. I wish I knew more about how CF actually connects to the database. Glad you seem to have it in a stable situation now.
@James, CFTRANSACTION (or using transaction wrapping directly in your SQL) is going to decrease data connection performance, but whatever ... if they like you to do it, then I guess that's fine. Think about it like this: inside a transaction, the data changes are all staged, but not actually saved to the database until everything is successfully pre-processed. Once the pre-flight appears OK, then the database engine allows all the data to flow into the tables. Without the transaction lock, data changes are simply made directly by the database engine.
As for the issue that they seem to actually be reporting -- database connection timeouts -- that seems to me to be an issue of optimization. Make sure that your major filters are indexed properly at the table level and that your SQL is as tight as possible, and you should be able to bring database returns down to very small numbers (like milliseconds, in many cases).