If your applications are written in a way that is dependent on being able to call ROLLBACK rather than COMMIT in critical situations, transactions are more convenient. Transactions also ensure that unfinished updates or corrupting activities are not committed to the database; the server is given the opportunity to do an automatic rollback and your database is saved.

If you use nontransactional tables, you must resolve potential problems at the application level by including checks before updates and by running scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. You can normally fix tables with no data integrity loss by using the MySQL log or even adding one extra log.

Sometimes, critical transactional updates can be rewritten to be atomic. Multiple DML operations can be done with LOCK TABLES or atomic updates, ensuring that there are no deadlocks by limiting concurrent write access. If you obtain a READ LOCAL lock (as opposed to a write lock) for a table that enables concurrent inserts at the end of the table, reads are permitted, as are inserts by other clients. The newly inserted records are not be seen by the client that has the read lock until it releases the lock. With INSERT DELAYED, you can write inserts that go into a local queue until the locks are released, without having the client wait for the insert to complete. See Concurrent Inserts, and INSERT DELAYED Syntax.

To be safe with MySQL Server, regardless of what kinds of tables you use, make regular backups and have binary logging turned on. It is always good to have backups, regardless of which database system you use.

Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don't need cursors to update records on the fly.

To avoid using ROLLBACK, you can employ the following strategy:

You can also use functions to update records in a single operation, using the following techniques:

When managing unique identifiers, you can avoid statements such as LOCK TABLES or ROLLBACK by using an AUTO_INCREMENT column and either the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Information Functions, and mysql_insert_id().

For situations that require row-level locking, use InnoDB tables. Otherwise, with MyISAM tables, you can use a flag column in the table and do something like the following:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn't 1 in the original row. You can think of this as though MySQL Server changed the preceding statement to:

UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;