jJ8tC71j

1DROP DATABASE IF EXISTS savepoint_test;
2
3CREATE DATABASE savepoint_test;
4
5USE savepoint_test;
6
7CREATE TABLE IF NOT EXISTS test_savepoints_tbl (
8  id INT AUTO_INCREMENT PRIMARY KEY,
9  trx INT,
10  savepoint INT
11);
12
13CREATE FUNCTION trx_savepoints ()
14RETURNS INT READS SQL DATA
15RETURN (SELECT NUMBER_OF_SAVEPOINTS 
16         FROM performance_schema.events_transactions_current
17        WHERE THREAD_ID = sys.ps_thread_id(NULL));
18
19DELIMITER $$
20
21CREATE PROCEDURE test_savepoints ()
22BEGIN
23
24  START TRANSACTION;
25
26  INSERT INTO test_savepoints_tbl (trx, savepoint) VALUES (1, 1);
27
28  SELECT trx_savepoints() as zero_savepoints;
29
30  SAVEPOINT one;
31
32  INSERT INTO test_savepoints_tbl (trx, savepoint) VALUES (1, 2);
33
34  SELECT trx_savepoints() as one_savepoint;
35
36  SAVEPOINT two;
37
38  INSERT INTO test_savepoints_tbl (trx, savepoint) VALUES (1, 3);
39
40  SELECT trx_savepoints() as two_savepoints;
41
42  ROLLBACK TO SAVEPOINT one;
43
44  SELECT trx_savepoints() as rolled_back_to_savepoint_one;
45
46  ROLLBACK;
47
48  SELECT trx_savepoints() as rolled_back;
49
50  SELECT NUMBER_OF_SAVEPOINTS,
51         NUMBER_OF_ROLLBACK_TO_SAVEPOINT,
52         NUMBER_OF_RELEASE_SAVEPOINT
53    FROM performance_schema.events_transactions_current
54   WHERE THREAD_ID = sys.ps_thread_id(NULL);
55
56END $$
57
58DELIMITER ;
59
60CALL test_savepoints();
61
62mysql> CALL test_savepoints();
63+-----------------+
64| zero_savepoints |
65+-----------------+
66|               0 |
67+-----------------+
681 row in set (0.01 sec)
69
70+---------------+
71| one_savepoint |
72+---------------+
73|             1 |
74+---------------+
751 row in set (0.01 sec)
76
77+----------------+
78| two_savepoints |
79+----------------+
80|              2 |
81+----------------+
821 row in set (0.01 sec)
83
84+------------------------------+
85| rolled_back_to_savepoint_one |
86+------------------------------+
87|                            2 |
88+------------------------------+
891 row in set (0.01 sec)
90
91+-------------+
92| rolled_back |
93+-------------+
94|           2 |
95+-------------+
961 row in set (0.01 sec)
97
98+----------------------+---------------------------------+-----------------------------+
99| NUMBER_OF_SAVEPOINTS | NUMBER_OF_ROLLBACK_TO_SAVEPOINT | NUMBER_OF_RELEASE_SAVEPOINT |
100+----------------------+---------------------------------+-----------------------------+
101|                    2 |                               1 |                           0 |
102+----------------------+---------------------------------+-----------------------------+
1031 row in set (0.01 sec)
104
105Query OK, 0 rows affected (0.01 sec)