tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Configuration Change
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
DocumentationTDSQL BoundlessGeneral ReferenceSQL ReferenceSQL StatementsSAVEPOINT

SAVEPOINT

PDF
Focus Mode
Font Size
Last updated: 2026-03-06 18:48:09

Feature Description

Savepoint (Savepoint) is a marker within a transaction, allowing rollback points to be set during transaction execution to achieve partial rollback without affecting the entire transaction.

Syntax

Set Savepoint
Creates a savepoint with a specified name in the current transaction.
If a savepoint with the same name already exists, the original savepoint is deleted and a new savepoint is created.
SAVEPOINT savepoint_name;
Rollback to Savepoint
Rolls back the transaction to the specified savepoint without terminating the transaction.
After a rollback, all data modifications made after the savepoint will be undone.
Subsequent savepoints will be automatically deleted.
ROLLBACK TO SAVEPOINT savepoint_name;
Release Savepoint
Deletes the specified savepoint and all savepoints created after it. Does not commit or roll back the transaction.
RELEASE SAVEPOINT savepoint_name;

Parameter Description

Parameter
Required
Description
savepoint_name
Yes
The unique name identifier for a savepoint supports uppercase and lowercase English letters and numbers.

Limitations

When a non-existent savepoint is rolled back or released, an error will be returned: ERROR 1305 (42000): SAVEPOINT does not exist.
After a transaction is committed or rolled back, all savepoints are automatically cleared.
Savepoint names must be unique within the same transaction.

Examples

1. Set up the environment.
-- 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);
2. Transaction Operation Process.
-- 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;
3. Verify the results.
-- Query the final balance
SELECT * FROM account;
Expected result:
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
| 1  | Alice | 900.00  |
| 2  | Bob   | 600.00  |
+----+-------+---------+
Execution process state changes:
Steps
Alice Balance
Bob Balance
Description
Initial status
1000.00
500.00
Before the transaction starts
First transfer
900.00
500.00
Alice transfers out 100 CNY
After sp1 is set
900.00
600.00
Bob received 100 CNY.
After sp2 is set
850.00
650.00
Transfer 50 CNY again
Rollback to sp2
900.00
600.00
Undo the second transfer
Final commit
900.00
600.00
Transaction completed
Previous Topic: PURGE RECYCLEBINNext Topic: SHOW RECYCLEBIN

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback