Testing Queries Inside of a Transaction


If you are working on a table managed by a transactional storage engine, such as InnoDB or BDB, you can safely test many types of queries within the context of a transaction. If the queries operate as you expect, the transaction can be committed and made permanent. If the queries do not work as anticipated, you can roll them back and return the database to the state that it was in before the transaction started.

Caution

Not all types of query can be rolled back, including all of the CREATE and DROP queries.


Tip

Ensure that you are working with tables that are managed by a transactional storage engine by using the SHOW CREATE TABLE query. If a table is managed by a transaction storage engine, the ENGINE option of the CREATE TABLE statement generated by SHOW CREATE TABLE should be set to InnoDB or BDB.


Here is an example of testing commands within the context of a transaction:

# Ensure you are dealing with a transactional table # Look for the line ENGINE=InnoDB SHOW CREATE TABLE book; BEGIN; # start your transaction UPDATE book SET author = "Dr. Seuss"; SELECT * FROM book; # examine results and # ... notice that data has been overwritten ROLLBACK; # cancel the transaction SELECT * FROM book; # See? All the data is safe. BEGIN; # start a new transaction UPDATE book SET AUTHOR = "Dr. Seuss"   WHERE author = "Theodor Geisel"; SELECT * FROM book; # examine results COMMIT; # make changes permanent 




MySQL Phrasebook. Essential Code and Commands
MySQL Phrasebook
ISBN: 0672328399
EAN: 2147483647
Year: 2003
Pages: 130

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net