CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
CREATE DATABASE rhinos;
USE rhinos;
CREATE TABLE serengeti (a BIGINT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1000) NOT NULL);
CREATE PROCEDURE rhino (IN loops BIGINT, IN undo_space char(20))
BEGIN
SET @x = 0;
WHILE (@x < loops) DO
SET @y = 0;
ALTER UNDO TABLESPACE innodb_undo_005 SET INACTIVE;
WHILE (@y < 5) DO
SET @c = concat("INSERT INTO serengeti (b) VALUES ('Insert #", @x * 10 + @y + 1, " while ", undo_space, " is inactive')");
PREPARE stmt FROM @c;
EXECUTE stmt;
SET @y = @y + 1;
END WHILE;
ALTER UNDO TABLESPACE innodb_undo_005 SET ACTIVE;
WHILE (@y < 10) DO
SET @c = concat("INSERT INTO serengeti (b) VALUES ('Insert #", @x * 10 + @y + 1, " while ", undo_space, " is active')");
PREPARE stmt FROM @c;
EXECUTE stmt;
SET @y = @y + 1;
END WHILE;
SET @x = @x + 1;
END WHILE;
END|
CALL rhinos.rhino(10, 'innodb_undo_005');;
CALL rhinos.rhino(10, 'undo_003');;
CALL rhinos.rhino(10, 'undo_005');;
SELECT count(*) from serengeti;
count(*)
300
USE test;
DROP DATABASE rhinos;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
DROP UNDO TABLESPACE undo_003;
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
DROP UNDO TABLESPACE undo_004;
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
DROP UNDO TABLESPACE undo_005;
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
DROP UNDO TABLESPACE undo_006;