Initiating a database transaction from within a controller method.

[eluser]TheFuzzy0ne[/eluser]
Is it OK to start a database transaction from within the controller, or should it be in a model method? I want to run two database queries within a transaction. Each one is in it's own model method (one for adding a topic, and one for adding a post).

[eluser]Dam1an[/eluser]
In a way, it seems most logical to start/stop transactions in the controller, as most people have a model per table, and with most transactions, you're dealing with multiple tables. So you'd have to call 2 (or more) separate models.

Btw, remember you need to use InnoDB or BDB with MySQL to get transaction support (instead of MyISAM)

[eluser]TheFuzzy0ne[/eluser]
Hmm. Thanks for pointing that out. I think I'll just stick to MyISAM. It's what I know, and I heard that innoDB is slower(?)

[eluser]Dam1an[/eluser]
Do CI transactions fail gracefully (aka not do anything) if you use a non transactionable database engine?
Cause then you could have the start and stop calls for the transactions, but they would only execute if its supported

Tbh, I don't think you really need transactions for this, as its not mission critical
If a thread gets created without the opening post, its not the end of the world, no one loses out financially etc

[eluser]sl3dg3hamm3r[/eluser]
MyISAM is faster, especially when it comes to updating or deleting. On the other hand, I would miss the capabilities like referential integrity. Nowadays, I use it all along the way. And all kind of codes I put into lookup-tables (again referential integrity), it makes the model much more fail-safe and data consistent.

[eluser]TheFuzzy0ne[/eluser]
I'm going to be implementing Zend_Search_Lucene for searching the forum. It's a database in it's own right, but built quite specifically for indexing data. I'm wondering if I should stored the post counts in there. It's feels a very hackish way to go about maintaining data normalisation. I have two data sources to maintain anyway, and try to keep in sync with one another. Perhaps I need to implement my own type of database-independent transaction specifically for my forum search model (not even sure if this is possible)?

A lot of potential errors can be caught by checking that the corresponding data exists in the database (for example, checking a topic ID exists when a post is inserted), but that's more work for the database. Also, it's still not 100% foolproof, but I'm sure you will agree it can prevent database anomalies. I'm not entirely sure how I should proceed with this. I don't want too much code and unnecessary database calls, but I do need to keep an eye on what's going on in my database - or do I need to just chillax a bit and design my forum so this doesn't matter about these anomalies?

[eluser]Cro_Crx[/eluser]
Dam1an: i almost tried transactions with MyISAM but then I saw one of the big red warnings on the CI documentation somewhere so didn't get to try it and see what happens. I was reading up a lot about this because I needed transactions for one of my applications (so MyISAM wasn't any good) but I also needed to be able to count(*) very quickly, (so InnoDB wasn't any good) either.

I decided in the end to use InnoDB and denormalize the totals so count was almost instant, but reading all those comparisons it seems as though the performance of InnoDB isn't that much slower than MyISAM and on some benchmarks it's actually faster. For example if your performing insert/updates a lot it's almost always quicker as it has row level locking compared to MyISAM table locking.

If you just google 'MyISAM vs InnoDB' i'm sure you'll find a website with some benchmarks, you'll probably be surprised that InnoDB isn't slower than MyISAM at all.

Here's one for example --> http://www.mysqlperformanceblog.com/2007...ks-part-1/

[eluser]sl3dg3hamm3r[/eluser]
[quote author="Cro_Crx" date="1242162744"]If you just google 'MyISAM vs InnoDB' i'm sure you'll find a website with some benchmarks, you'll probably be surprised that InnoDB isn't slower than MyISAM at all.[/quote]

If there is any use of referential integrity, I would be suprised if MyISAM is still slower... But maybe it is also a difference if you measure middle sized tables vs. big tables.

[eluser]Cro_Crx[/eluser]
TheFuzzy0ne: If your using MyISAM in the database, using count(*) is almost instant as it keeps an index of the rows anyways. For InnoDB it's quite slow as there isn't an index so it has to count the number of rows which takes longer when the table grows.

So yeah either is fine, if you don't need transactions just count(*), it's easier and as quick as storing the total.

If your using InnoDB then yeah you'd want to keep a total of the rows and then use transactions to keep the totals accurate etc...

[eluser]Cro_Crx[/eluser]
[quote author="sl3dg3hamm3r" date="1242163284"][quote author="Cro_Crx" date="1242162744"]If you just google 'MyISAM vs InnoDB' i'm sure you'll find a website with some benchmarks, you'll probably be surprised that InnoDB isn't slower than MyISAM at all.[/quote]

If there is any use of referential integrity, I would be suprised if MyISAM is still slower... But maybe it is also a difference if you measure middle sized tables vs. big tables.[/quote]

I don't think there is, they only tested selects as well if you look there's no update/insert testing as well. Obviously the tests won't reflect real life conditions. I'm still under the impression that MyISAM is quicker but obviously InnoDB has more features. I can't see how InnoDB performed more requests on most of those tests, seems odd!