CMA Exam -- Transactions

The Certified MySQL Associate Exam is a general overview of the MySQL database. It is designed for those new to databases and/or MySQL.

The subject of transactions has caused a few folks to stumble on their exams. For someone with a background in programming where you change data and it stays that way, transactions take a little effort to understand.

The usual example is a payment at a bank. A pays B 100 units. If something happens between the time the system takes the 100 out of A's account and puts it into B's account, there is a large chance that both A and B will not be happy. So the subtract 100 and add 100 are treated as one unit and either both have to be completed as a unit or not at all.


So all or nothing.

You type START TRANSACTION, input the needed changes, and COMMIT. And your changes are done as one to your database. Or half way through you realize that something is wrong and use ROLLBACK to get back to where you were when you typed START TRANSACTION.

Almost.

First, you have to use a storage engine that supports transactions. Try using START TRANSACTION and COMMIT with MyISAM and see how it reacts.

Second, some commands like CREATE TABLE, DROP TABLE, or UNLOCK TABLES (see MySQL Reference Manual section 12.4.3) can cause MySQL to implicitly issue a COMMIT on your behalf. You have better know these to help you pass the exam and to keep your sanity on the job.