It is….however, I am just trying to figure out which way to go. MYISAM
vs. INNODB. I want to make an informed decision and move forward.
--------------------------------------------------------
Kevin Fricke
Lone Star Media
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Office: (512) 371-1822
Mobile: (512) 626-0528
Fax: (512) 597-0909
Toll Free: (877) 791-7083
http://www.lonestarmedia.com
*From:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of *Christopher Jordan
*Sent:* Thursday, July 26, 2007 11:03 PM
*To:* Dallas/Fort Worth ColdFusion User Group Mailing List
*Subject:* Re: [DFW CFUG] mySQL
I realize that this thread has gotten a bit off the original poster's
subject, but I just had a look at the docs and all it says is:
<cftransaction>
<cfquery name='makeNewCourse' datasource='Snippets'>
INSERT INTO Courses
(Number, Descript)
VALUES
('#myNumber#', '#myDescription#')
</cfquery>
<cfquery name='insertNewCourseToList' datasource='Snippets'>
INSERT INTO CourseList
(CorNumber, CorDesc, Dept_ID,
CorName, CorLevel, LastUpdate)
VALUES
('#myNumber#', '#myDescription#', '#myDepartment#',
'#myDescription#', '#myCorLevel#', #Now()#)
</cfquery>
</cftransaction>
It further says that:
Changes to data that is requested by the queries
are not committed to the datasource until all actions within the transaction
block have executed successfully.
So while they provide the commit and rollback options, they're listed
in the docs as optional because the cftransaction tag doesn't really
commit the changes until the block has completed successfully. That
would make it seem, to me, that the try/catch blocks and the rollback
and commit statements aren't necessary since the commit hasn't been
made in the first place. There's nothing to rollback.
I seem to remember bringing this subject a while back with the group,
and I had my transaction laid out much the same way you're talking
about, and I was told that none of it was necessary, because the
cftransaction tag took care of doing all that for me. I'd like to know
why those options were made available in the first place, but I still
don't think they're necessary.
Hey Kevin! Any of this playful banter helpin' ya? ;o)
Chris
On 7/26/07, *Chris Gomez* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
if you're doing a multi-query transaction then you need something like
this to rollback any inserts, etc if one of the queries fails.
cftransaction will automatically insert the data up to the point
there's an error. without that error checking, it becomes a huge
headache to maintain, for example, customer order transactions. I
would much rather have a db full of successful transactions than one
with incomplete transactions to sort through. But then, I'm a little
fanatical these days about this kind of stuff. :)
On 7/26/07, *Christopher Jordan* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Not that this post was originally about using cftransaction, but isn't
the try/catch stuff unnecessary because of the nature of the
cftransaction tag itself? That's what I thought. I could be dead
wrong. ;o)
Chris
On 7/26/07, *Chris Gomez* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> > wrote:
One of the better ways I've found to handle transactions using CF is
like this:
<cflock name="AddOrder" timeout="10">
<cfset commit = true>
<cftransaction action="begin">
<cftry>
<cfquery>
blah blah
</cfquery>
<cfquery>
blah blah
</cfquery>
<cfcatch type="any">
<cfset commit = false>
<cftransaction action="rollback" />
<cflocation addtoken="false" url="errorpage.cfm">
</cfcatch>
</cftry>
<cfif commit>
<cftransaction action="commit" />
<cflocation addtoken="false" url="success.cfm">
</cfif>
</cftransaction>
</cflock>
The transaction is only added to the db if every query or function is
successful. This prevents multiple transaction sessions from
interfering with each other and disallows partial inserts/updates/etc
(like in a shopping cart solution) from being entered in to the db.
Hope this helps.
Chris
On 7/26/07, *Christopher Jordan* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Kevin,
I'm not an authority, but here's what I think I know:
INNODB tables are slower *because* they allow for transactions.
Transactional databases (tables?) allow you to roll back changes that
were made to it. So it keeps track of all changes that have been made
and store them so that you can roll back a particular insert or update
or what have you as if that statement had not taken place. I have no
idea how far back it keeps information of this sort, I just know that
on other DBs I've worked with (older versions of DB2) this means that
extra disk space is required to store all the changes.
The only senario that *I'm* aware of (I'm sure there are loads more)
would be when doing something like this:
<cftransaction>
<cfquery name="update" datasource="blahblah">
UPDATE InventoryTable
SET ItemQuantity = ItemQuantity - 1 -- I think you can do this in
sql... if not just consider it psuedo code ;o)
WHERE ItemID = #MyItemID#
</cfquery>
<!--- customers automattically get entered for a prize drawing or
something --->
<cfquery name="insert" datasource="blahblah">
INSERT INTO PrizeDrawingTable (CustomerID, ItemID)
VALUES (#MyCustomerID#, #MyItemID#)
</cfquery>
</cftransaction>
In this case if the first query fails for some reason, then the second
query wouldn't execute. If the first query was successful but the
second query failed, then the first query would be 'Undone' or rolled
back.
This may be an overly simplistic view, and since I don't do this sort
of thing often, I may have even gotten something wrong. Someone please
correct me if I did get something wrong.
Does that help?
Cheers,
Chris
On 7/26/07, *Kevin* < [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Anyone have experience with MySQL? I have been using it for years
now but I wonder if I might have outgrown it.
The way that I understand it is that my MyISAM tables are faster
but INNODB allow for transaction support (which I have never fully
understood).
We have been using MyISAM for years and have been satisfied.
However, I am told that in order to make a backup of the database
you have to shut it down, back it up, then restart it. This is
absurd. Therefore, we were considering moving to INNODB but
everything I read says that these are _VERY_ slow tables.
Please let me know if you have any advise….should we move away
from MySQL? Is there a solution to back up MyISAM? Is the
performance of INNODB really not as bad as I have read.
I am so confused and frustrated……I understand that MySQL is used
by some pretty big hitters (http://www.mysql.com/customers/
<http://www.mysql.com/customers/>). What are these guys using?
Any help, advise, comments, etc. will be tremendously appreciated.
--------------------------------------------------------
Kevin Fricke
Lone Star Media
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Office: (512) 371-1822
Mobile: (512) 626-0528
Fax: (512) 597-0909
Toll Free: (877) 791-7083
http://www.lonestarmedia.com <http://www.lonestarmedia.com/>
_______________________________________________
Reply to DFWCFUG:
[email protected] <mailto:[email protected]>
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
<http://lists1.safesecureweb.com/mailman/listinfo/list>
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/ <http://www.instantspot.com/>
www.teksystems.com/ <http://www.teksystems.com/>
--
http://cjordan.us <http://cjordan.us/>
_______________________________________________
Reply to DFWCFUG:
[email protected] <mailto:[email protected]>
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
<http://lists1.safesecureweb.com/mailman/listinfo/list>
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/ <http://www.instantspot.com/>
www.teksystems.com/ <http://www.teksystems.com/>
--
"Fortune favors the prepared mind."
- Louis Pasteur
_______________________________________________
Reply to DFWCFUG:
[email protected] <mailto:[email protected]>
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
<http://lists1.safesecureweb.com/mailman/listinfo/list>
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/ <http://www.instantspot.com/>
www.teksystems.com/ <http://www.teksystems.com/>
--
http://cjordan.us <http://cjordan.us/>
_______________________________________________
Reply to DFWCFUG:
[email protected] <mailto:[email protected]>
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
<http://lists1.safesecureweb.com/mailman/listinfo/list>
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/ <http://www.instantspot.com/>
www.teksystems.com/ <http://www.teksystems.com/>
--
"Fortune favors the prepared mind."
- Louis Pasteur
_______________________________________________
Reply to DFWCFUG:
[email protected] <mailto:[email protected]>
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/ <http://www.instantspot.com/>
www.teksystems.com/ <http://www.teksystems.com/>
--
http://cjordan.us
------------------------------------------------------------------------
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
