Monday, January 16, 2012

[Level 3] Transaction in MySQL.

#!/usr/bin/bash -vx
MYSQL_HOME=/opt/mysql/mysql
mysql -uroot -proot_pwd <<EOF
create database if not exists test;
use test;
create table if not exists test (id int) engine=InnoDB;
alter table test engine=Innodb;
truncate table test;
insert into test values (1),(2),(3);

system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.bak

start transaction;
insert into test values (4);
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran1;
rollback;
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.rollback;
system diff $MYSQL_HOME/data/test/test.ibd.tran1 $MYSQL_HOME/data/test/test.ibd.rollback > ./diff_tran1_rollback;

start transaction;
insert into test values (4);
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran2
commit;
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.commit
system diff $MYSQL_HOME/data/test/test.ibd.tran2 $MYSQL_HOME/data/test/test.ibd.commit > ./diff_tran2_commit

EOF
Wish this helps.
regards,
Stanley Huang