Forums

mysql InnoDB and Transaction

Permalink 1 user found helpful
Hi there

I have a table, witch get an update every 30 minutes. This update will take something between 0.5 seconds and 2 seconds. depends on server load.

Update in this case means, truncate the table and re fill it. So, as you can see, in a worst case, there are up to 2 seconds without a complete table.

this is the reason, why i would like to update this table with transactions. But actually, it seams not to work. I allready changed the table from MyISAM to InnoDB.

So my main question is, does someone have a sample, on using adodb transactions with mysql in C5?

Thanks a lot and kind regards,
steff
Steff
 
Mainio replied on at Permalink Reply
Mainio
Here's some documentation I found:
http://phplens.com/lens/adodb/docs-adodb.htm#ex11...

So in C5 you would do something like this:
$db = Loader::db();
$db->StartTrans();
$db->Execute($sql);
$db->Execute($sql2);
$db->CompleteTrans();


Adodb is really basic database abstraction class, so I'm not sure of the extent it can handle transactions. From that documentation it seems to be possible quite easily.


Antti / Mainio
Steff replied on at Permalink Reply
Steff
This is what i was looking at too.

So if I run this:
$db = Loader::db();
$db->StartTrans();
$query = 'TRUNCATE TABLE btSteffTravelsTravels';
$db->Execute($query);
$db->FailTrans();
$db->CompleteTrans();


The Table is truncated. But with FailTrans(), it shoudn't truncate the table.

http://phplens.com/adodb/tutorial.smart.transactions.html...

Steff
Mainio replied on at Permalink Best Answer Reply
Mainio
Ok, this might be some adodb specific issue by not handling the transactions correctly, that's my best guess...

I actually opened up the MySQL driver code from adodb and it states this:
/* ...
MySQL code that does not support transactions. Use mysqlt if you need transactions.
... */


Try adding this to your config/site.php:
define('DB_TYPE', 'mysqlt');



Also, if you re-fill the table straight after the truncate have you completely thought-out your scenario and whether it is the best possible way to handle things?


Antti
Steff replied on at Permalink Reply
Steff
Antti

Thanks. I tried this. But no luck. The table is also truncated.

any other ideas?

Here is why i truncate the table and then refill it. The data for this table comes from an other service.

1. Check if domain is available
2. check if get.aspx returns an xml
3. if 1 and 2 are true, then insert complete data into the table.

And each time this process is called, the xml can have new entries and there can be entries in the database, which aren't in the xml anymore. this is why i truncate the table.
Mainio replied on at Permalink Reply
Mainio
Ok, seems that you just use the db table as XML cache. C5 also has internal cache functionality in Cache class, I think this might be easier solution.

Looking at adodb code you might also try this:
$db->CompleteTrans(false);



If that doesn't work I'd suggest handling the transactions with some better library. As said, adodb is really just basic library for this stuff.

Or alternatively, go with the Cache class.


Antti
Steff replied on at Permalink Reply
Steff
Got it. After RTFM I found that TRUNCATE is not transaction safe:
http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html...

this means, i changed from "truncate" to "DELETE FROM tblName".

@Antti
It is a kind of XML cache. But i need the SQL-Table for easier queries.

Thanks for your help.
Mainio replied on at Permalink Reply
Mainio
Ok, great to know this as well. Thanks for the info!