DBI and transactions on MySQL

d_i_r_t_y has asked for the wisdom of the Perl Monks concerning the following question:

Has anyone had much experience with using the new transactions features of MySQL with DBI and DBD::mysql?

Our initial experiments with them show that the latest DBD::mysql driver (version 2.0415) doesn't permit the use of transaction by virtue of the fact that the Autocommit flag insists on remaining on, which of course, obviates the concept of transaction.

what can be done to get around this though, is to use the (rather crude) do method:

 $dbh->do('begin');  
 $dbh->do('commit');
 $dbh->do('rollback');
[download]

This works, but probably isn't portable and probably isn't the 'right' way to do things. does anyone else has advice/experiences to offer?

d_i_r_t_y

Comment on DBI and transactions on MySQL Download Code

Replies are listed 'Best First'.
Re: DBI and transactions on MySQL by BMaximus (Chaplain) on Apr 20, 2001 at 10:15 UTC
I haven't had any problems using the new transaction abilities of MySQL with DBI. When using it with Apache::DBI and setting Autocommit => 0. Doing an insert and then calling rollback on it promptly removes it as if it never happened. Are you making sure that when you create a table that you make it of TYPE=BDB or TYPE=InnoDB?(make sure you read up on InnoDB databases lest more pain be brought upon you) The default type is MYISAM which will ignore the transaction code and autocommit, causing the much pain and frustration which has been brought upon you. BMaximus	[reply]
Re: DBI and transactions on MySQL by Mungbeans (Pilgrim) on Apr 20, 2001 at 16:19 UTC
I would say 'begin', 'commit' and 'rollback' were the correct and portable way to do things. I like being able to specify where the trasaction boundaries go on inserts and deletes, that lets you use locks more efficiently and avoid running out of log space for rollbacks. E.g. if you begin and commit after every 5000 transactions then you will only use 1 lock for that band of inserts which is much faster than 5000 separate begin and commits. My 2p.	[reply]