Using transactions with PHP/MySQL?

[Tarzan]

Hi!
Has anyone managed to use transactions with MySQL and Php?
Since the x10 server Starka seems to be having massive problems with MySQL connections there's a high probability that connections are lost between queries that need to be executed together. (It has happened that new players have signed up, but not received any activation e-mail because the database connection was lost in between actions).

I've tried to use SET AUTOCOMMIT=0 and then use BEGIN, END and COMMIT statements.
If I put an "exit;" (in the php code) and halts it before the COMMIT, all changes should be rolled-back right? It seems like it doesn't.
Or is the mysqli class a better alternative?

 

[lemon-tree]

1. Make sure you are using a table type that supports transactions, InnoDB is the probably the best choice.
2. This code should work:

//$connect = PDO connection
//Start transaction
$connect->exec('START TRANSACTION;');

//Do queries
$query = $connect->prepare('INSERT ETC');
$sth->execute();

//Or

$connect->exec('INSERT ETC;');

//Commit
$connect->exec('COMMIT;');

Setting autocommit shouldn't need to be done if you start a transaction, as by definition it will not commit within the transaction.

 

[Tarzan]

Thanks! I'll try that.
Is it correct to assume that an "exit;" in the middle of the php code would roll-back the transaction?
Or is there another better way to simulate a lost connection? (thus rolling back the transaction.)