Author: tom000
Date: 2007-03-06 17:32:03
Transaction is some kind of operations which we execute on data. We can add data, modify, delete when transaction is go on, but all changes will be apply when transaction will be successful finished.
Effects of transaction we can watching all the time by SELECT instructions. I case when we see some anomaly, we can go back to place where transaction was saved or totally rollback this transaction.
MySQL database supports transactions by short time. Used by defaults MyISAM as table type, haven't any possibilities to run transactions. This functionality was appear after launch InnoDB tables type.
Preparing tables
Before starts transaction, we need to prepare table in database in adequate way. Table which we will be use to make transactions should has InnoDB type. Actually this is only one (against not fully integrated BerkleyDB) type of table in MySQL database, which has support for transactions. Here is sample structure of table, which we will be using in other samples.
CREATE TABLE `trans` ( `id` INT NOT NULL AUTO_INCREMENT , `re` INT NOT NULL , `txt` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = innodb;
Starting transactions
To start transaction in MySQL database we should use command START TRANSACTION; Before begin of transaction we can also state level of isolation by command SET TRANSACTION ISOLATION LEVEL. For this command are accessible four parameters: { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }. If we didn't choose any isolation level, by default will be REPEATABLE READ option. Here some sample for beginning of transaction:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION;
Saving state of transaction
Saving state of transaction isn't a necessary step. It is used in transactions when can appear some mistakes or problems. Then is a possibility to go back to earlier saved state. To save state of transaction we use command SAVEPOINT point; where point is a name of position to save.
Finishing transactions
To finish transaction we use command COMMIT; After finish of transaction all executed operations will be saved in database, without any possibility to call them off.
Breaking transaction
Sometimes happens situations when we must with some reason break transaction. To made this exists command ROLLBACK; When it will be used all realized commands by transaction time will be call off and contents of database will be not change.
This command makes possibilities to go back to earlier saved state of transaction. In this case command will be looks like this: ROLLBACK TO point; where point is name of last saved position.
Add comment