MySQL and transactions

At my new job we are using MySQL. Its an excellent product but it is far from competing with Microsoft SQL Server. I learned most of my database skills on SQL Server 7.0/2000 and are now using 2005. It is a really beautiful and mature product with excellent tools to support it.

I know from quite some time ago that MySQL was excellent for speed and the creators idea was not directly in line with how most view a RDBMS. This didn’t stop MySQL from becoming a very popular backend and is in very wide use today.

How I would like to work with a database is very different from how MySQL allow developers to work with it.

If you are doing some work on a database level you usually want to do it close the the data like in a stored procedure. When you update multiple entities in the database you usually want to do it in a transaction.  MySQL 5.0 supports procedures and transactions! I might just be missing the whole concept but so far I have not found a way to detect errors inside a stored procedure. What I want is to run a stored procedure that starts a transaction and it will fire several statements that might fail and if one statement fail I should detect it inside the procedure and roll it back.

At the moment it seems as I have to do it from the client; start a transaction, execute the procedure and commit if it succeeds otherwise roll back.

This is just something I either do not know enough about or is correct about and need to get used to.

Leave a Reply

Your email address will not be published. Required fields are marked *