| 1 |
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu'; |
| 2 |
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu'; |
| 3 |
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu'; |
| 4 |
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu'; |
| 5 |
CREATE DATABASE africa; |
| 6 |
USE africa; |
| 7 |
CREATE TABLE serengeti (a INT PRIMARY KEY, b TEXT); |
| 8 |
CREATE PROCEDURE crash (IN loops INT, IN undo_number INT) |
| 9 |
BEGIN |
| 10 |
SET @undo_num = undo_number; |
| 11 |
SET @before = 5 * (@undo_num - 2); |
| 12 |
SET @during = 25; |
| 13 |
SET @after = 25 - @before; |
| 14 |
SET @all = @before + @during + @after; |
| 15 |
PREPARE stmt_ins FROM "INSERT INTO serengeti VALUES (?, ?)"; |
| 16 |
SET @a = ((@undo_num - 2) * 10000) + 1; |
| 17 |
SET @n = 0; |
| 18 |
WHILE (@n < @all) DO |
| 19 |
SET @b = concat("Insert #", @a); |
| 20 |
EXECUTE stmt_ins USING @a, @b; |
| 21 |
SET @a = @a + 1; |
| 22 |
SET @n = @n + 1; |
| 23 |
END WHILE; |
| 24 |
PREPARE stmt FROM "UPDATE serengeti SET b = ? WHERE a = ?"; |
| 25 |
SET @loop = 0; |
| 26 |
WHILE (@loop < loops) DO |
| 27 |
SET @a = ((@undo_num - 2) * 10000) + 1; |
| 28 |
SET @n = 0; |
| 29 |
WHILE (@n < @before) DO |
| 30 |
SET @b = concat("Update #", @a, ", loop ", @loop, " BEFORE undo_00", @undo_num, " is SET INACTIVE"); |
| 31 |
EXECUTE stmt USING @b, @a; |
| 32 |
SET @a = @a + 1; |
| 33 |
SET @n = @n + 1; |
| 34 |
END WHILE; |
| 35 |
IF (@undo_num = 3) THEN |
| 36 |
ALTER UNDO TABLESPACE undo_003 SET INACTIVE; |
| 37 |
END IF; |
| 38 |
IF (@undo_num = 4) THEN |
| 39 |
ALTER UNDO TABLESPACE undo_004 SET INACTIVE; |
| 40 |
END IF; |
| 41 |
IF (@undo_num = 5) THEN |
| 42 |
ALTER UNDO TABLESPACE undo_005 SET INACTIVE; |
| 43 |
END IF; |
| 44 |
IF (@undo_num = 6) THEN |
| 45 |
ALTER UNDO TABLESPACE undo_006 SET INACTIVE; |
| 46 |
END IF; |
| 47 |
SET @n = 0; |
| 48 |
WHILE (@n < @during) DO |
| 49 |
SET @b = concat("Update #", @a, ", loop ", @loop, " WHILE undo_00", @undo_num, " is SET INACTIVE"); |
| 50 |
EXECUTE stmt USING @b, @a; |
| 51 |
SET @a = @a + 1; |
| 52 |
SET @n = @n + 1; |
| 53 |
END WHILE; |
| 54 |
IF (@undo_num = 3) THEN |
| 55 |
ALTER UNDO TABLESPACE undo_003 SET ACTIVE; |
| 56 |
END IF; |
| 57 |
IF (@undo_num = 4) THEN |
| 58 |
ALTER UNDO TABLESPACE undo_004 SET ACTIVE; |
| 59 |
END IF; |
| 60 |
IF (@undo_num = 5) THEN |
| 61 |
ALTER UNDO TABLESPACE undo_005 SET ACTIVE; |
| 62 |
END IF; |
| 63 |
IF (@undo_num = 6) THEN |
| 64 |
ALTER UNDO TABLESPACE undo_006 SET ACTIVE; |
| 65 |
END IF; |
| 66 |
SET @n = 0; |
| 67 |
WHILE (@n < @after) DO |
| 68 |
SET @b = concat("Update #", @a, ", loop ", @loop, " AFTER undo_00", @undo_num, " is SET ACTIVE"); |
| 69 |
EXECUTE stmt USING @b, @a; |
| 70 |
SET @a = @a + 1; |
| 71 |
SET @n = @n + 1; |
| 72 |
END WHILE; |
| 73 |
SET @loop = @loop + 1; |
| 74 |
END WHILE; |
| 75 |
END| |
| 76 |
CALL africa.crash(10, 3);; |
| 77 |
CALL africa.crash(10, 4);; |
| 78 |
CALL africa.crash(10, 5);; |
| 79 |
CALL africa.crash(10, 6);; |
| 80 |
SELECT count(*) from serengeti; |
| 81 |
count(*) |
| 82 |
200 |
| 83 |
USE test; |
| 84 |
DROP DATABASE africa; |
| 85 |
ALTER UNDO TABLESPACE undo_003 SET INACTIVE; |
| 86 |
DROP UNDO TABLESPACE undo_003; |
| 87 |
ALTER UNDO TABLESPACE undo_004 SET INACTIVE; |
| 88 |
DROP UNDO TABLESPACE undo_004; |
| 89 |
ALTER UNDO TABLESPACE undo_005 SET INACTIVE; |
| 90 |
DROP UNDO TABLESPACE undo_005; |
| 91 |
ALTER UNDO TABLESPACE undo_006 SET INACTIVE; |
| 92 |
DROP UNDO TABLESPACE undo_006; |