Transactions in mysql


#1

Has anyone had any luck using transactions in the mysql database. If so I’d like to hear how you did it. Thanks.


#2

Hi mfcurry -

Unfortunately, we currently don’t support the InnoDB table type, which is required for transaction support (actually, I believe a form of transactions can be done using BDB, but nobody seems to ever use it).

There are various reasons for this from an administrative point of view - most of which I don’t quite follow - but apparently hosting InnoDB-based databases would be somewhat taxing on our servers (for one thing, I believe there’s a significant overhead in caching transactional queries).

The MySQL people have stated many times in the past that their so-called “atomic” operations can duplicate much of the functionality of transactions without as much of a performance hit. However, there are those out there who are skeptical of this claim, chalking it up mostly to MySQL not having supported transactions for a long time. I’ll let you decide if it’s a valid claim. :>

Here’s some information on transactions vs. atomic operations:

http://www.mysql.com/doc/en/ANSI_diff_Transactions.html

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#3

Yea I was trying to use the BDB tables. Sometimes the transaction would work in myphpadmin, but I can’t get it to work with PHP. Will Dreamhost be updating to mysql 4 when it is out of the testing stages?


#4

I don’t see why not. If we don’t, I’ll probably be one of the loudest ones asking for it (I too use our servers to do my own ‘stuff’).

The operative phrase, though, is “out of the testing stages”. We really do like to see something like MySQL be not only out of beta, but running well on a variety of systems for some time before we support it.

That said, 4 has been in development and Q/A for quite some time, so we’ll probably jump onto it as soon as we can give it a decent round of testing. I’m not in the Admin team, though, so it really is their call.

I’d be very interested in seeing how well MySQL atomic operations work for you, though, if you decide to go that route. Most of the stuff I’m working on now doesn’t really need full transaction support, and if I can find a speedier way to ensure data integrity without them I’d be pretty happy.

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#5

This was a large concern for me too, before I decided to use DreamHost. Although there is STILL a potential for inconsistencies if the server goes down or gets really busy, in general the atomic operations are so quick that the chance of this happening are pretty slim.

Of course, having said that, as soon as transaction capability is supported, I’ll be using it!

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com