Transactions in MySQL

In Oracle transactions are "turned on" by default, in other words DML statements (update, insert, delete) need to be commited or rolledback explicitly. It's possible to set your client, for example SQL*Plus, to auto commit for you but many Oracle developers (Myself included) are simply used to having to commit and rollback (and many times appreciate the fact when we forget to include a where clause on that delete statements).

One of the great features of MySQL is it's ability to use different storage engines. The defualt storage engine is InnoDB which supports transactions, however the command line client and Query Browser will commit and DML automatically by default.

If you need to use transactions you can do one of the following,

explictily start a transaction using the following command,

mysql > START TRANSACTION;

This will then continue the transaction until a commit or rollback takes place. To start a new transaction you need to issue the start transaction statment again.

If you would rather work in an environment like SQL*Plus where each and every statement needs to commited or rolled back you can use the SET command like so.

mysql > SET AUTOCOMMIT = 0;

A few points to note, when using transactions DDL does not perform a commit in the back ground, this is the case in Oracle. However issuing a lock tables command will issue a commit, even if the table with the changes is not part of the lock tables command.