Return to Revisions

For many people, the MySQL Achilles' heel is implicit commit.

According to Page 418 Paragraph 3 of the Book

the following commands can and will break a transaction

• ALTER TABLE
• BEGIN
• CREATE INDEX
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• LOCK TABLES
• UNLOCK TABLES
• SET AUTOCOMMIT = 1
• START TRANSACTION

#SUGGESTION

When it comes to MySQL, any ContinuousIntegration/SelfService jobs you construct should always make Transactional jobs and DDL scripts mutually exclusive.

This gives you the opportunity to create paradigms that would

• support transactions that are properly isolated with START TRANSACTION/COMMIT blocks
• control of DDL by scripting the DDL yourself, running such DDL as either constructor or destructor
• never combine this operations under one job

WARNING : If you are using MyISAM for any this, you can (un)kindly add MyISAM to the list of things that can break a transaction, maybe not in terms of implicit commit, but definitely in terms of data consistency should a rollback every be needed.