SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT savepoint_name;

RELEASE SAVEPOINT savepoint_name;

-- Create a test table.
CREATE TABLE account (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

-- Insert test data.
INSERT INTO account VALUES (1, 'Alice', 1000.00);
INSERT INTO account VALUES (2, 'Bob', 500.00);

-- BEGIN TRANSACTION
BEGIN;

-- Check initial state
SELECT * FROM account WHERE id IN (1,2);

-- Alice transfers 100 CNY to Bob
UPDATE account SET balance = balance - 100 WHERE id = 1;
SELECT * FROM account WHERE id IN (1,2);

-- Set savepoint sp1
SAVEPOINT sp1;

-- Bob received 100 CNY.
UPDATE account SET balance = balance + 100 WHERE id = 2;
SELECT * FROM account WHERE id IN (1,2);

-- Set savepoint sp2
SAVEPOINT sp2;

-- Transfer 50 CNY again
UPDATE account SET balance = balance - 50 WHERE id = 1;
UPDATE account SET balance = balance + 50 WHERE id = 2;
SELECT * FROM account WHERE id IN (1,2);

-- Set savepoint sp3
SAVEPOINT sp3;

-- Rollback to savepoint sp2 (undo the second transfer)
ROLLBACK TO SAVEPOINT sp2;
SELECT * FROM account WHERE id IN (1,2);

-- RELEASE SAVEPOINT sp1 (deleting all savepoints from sp1 onwards)
RELEASE SAVEPOINT sp1;

-- Attempting to roll back to sp3 will fail (because sp3 has been deleted)
-- ROLLBACK TO SAVEPOINT sp3; -- This line will throw an error

-- COMMIT TRANSACTION
COMMIT;

-- Query the final balance
SELECT * FROM account;

+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
| 1  | Alice | 900.00  |
| 2  | Bob   | 600.00  |
+----+-------+---------+