1
DROP TABLE IF EXISTS t1;
2
DROP TABLE IF EXISTS t2;
3
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b VARCHAR(10), c VARCHAR(10), PRIMARY KEY(a));
4
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b VARCHAR(10), c VARCHAR(10), PRIMARY KEY(a));
5
CREATE TABLE t3(a INT NOT NULL AUTO_INCREMENT, b VARCHAR(10), c VARCHAR(10), PRIMARY KEY(a));
6
INSERT INTO t1 (b,c) VALUES ('1','ok'), ('3','ok');
7
INSERT INTO t2 (b,c) VALUES ('2','ok'), ('4','ok');
8
INSERT INTO t3 (b,c) VALUES ('1','ok'), ('2','ok'), ('3','ok'), ('4','ok'), ('5','ok'), ('6','ok'), ('7','ok'), ('8','ok'), ('9','ok'), ('10','ok');
9
SET GLOBAL transaction_log_truncate_debug= true;
15
UPDATE t1 SET c = 'trx1' WHERE a > 0;
18
Should have one savepoint: A
19
SELECT * FROM DATA_DICTIONARY.USER_DEFINED_SAVEPOINTS;
25
UPDATE t2 SET a = a*3;
26
UPDATE t1 SET b = 'trx2' WHERE a > 0;
27
DELETE FROM t2 WHERE a > 0;
28
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
30
Should have no savepoints
31
SELECT * FROM DATA_DICTIONARY.USER_DEFINED_SAVEPOINTS;
35
UPDATE t3 SET c = 'TRX1a' WHERE a < 10 ORDER BY b LIMIT 4;
36
UPDATE t3 SET b = 'TRX1b' WHERE a < 10 ORDER BY c LIMIT 4;
37
ROLLBACK TO SAVEPOINT A;
42
Definitely should have no savepoints
43
SELECT * FROM DATA_DICTIONARY.USER_DEFINED_SAVEPOINTS;
46
UPDATE t3 SET b = 'TRX1c' WHERE a > 7;
47
ROLLBACK TO SAVEPOINT A;
48
ERROR 42000: SAVEPOINT A does not exist
75
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log', ENTRY_OFFSET) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
76
PRINT_TRANSACTION_MESSAGE('transaction.log', ENTRY_OFFSET)
87
sql: "UPDATE t3 SET b = \'TRX1c\' WHERE a > 7"
125
transaction_context {
135
sql: "UPDATE t2 SET a = a*3"
169
sql: "UPDATE t1 SET b = \'trx2\' WHERE a > 0"
202
DROP TABLE t1, t2, t3;
203
SET GLOBAL transaction_log_truncate_debug= true;