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
문서TDSQL BoundlessDeveloper GuideDeveloper Guide (MySQL Compatibility Mode)Transaction ManagementTransaction Operations

Transaction Operations

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-03-06 18:48:24

Starting Transactions

In TDSQL Boundless, users can enable a transaction in the following three ways:
1. Use the BEGIN statement
BEGIN;
2. Use the START TRANSACTION statement
START TRANSACTION;
3. Disable auto-commit mode
SET autocommit=0;
Note:
After SET autocommit=0 is used, all subsequent SQL statements will be executed within a single transaction until explicitly executing COMMIT or ROLLBACK.
After the user executes the statement to enable a transaction, the database does not immediately assign a snapshot to the transaction. The snapshot is assigned to the transaction when the user executes the first read/write SQL statement.

Execute SQL Operations in the Transaction

After a transaction is enabled, you can execute various SQL statements. These operations are temporarily saved in the transaction context and cannot be perceived by other transactions until commit or rollback.
START TRANSACTION;

-- Update the user table
UPDATE users SET balance = balance - 100 WHERE user_id = 1;

-- Insert order records
INSERT INTO orders (user_id, amount, order_date) 
VALUES (1, 100, NOW());

Committing Transactions

After all operations are successfully executed, you can use the COMMIT statement to commit the transaction:
COMMIT;
After committing, all operations in the transaction will be permanently saved to the database.

Rolling Back Transactions

If errors occur during transaction execution or operations need to be canceled, you can use the ROLLBACK statement to roll back the transaction:
ROLLBACK;
After a rollback, all operations in the transaction will be undone, and the database will be restored to the state before the transaction started.

SAVEPOINT Operation

SAVEPOINT is an important feature in database transaction management, which allows creating marked points during transaction execution so that you can roll back to a specific point when needed without having to roll back the entire transaction.

Create a Savepoint

SAVEPOINT savepoint_name;

Roll Back to a Savepoint

ROLLBACK TO SAVEPOINT savepoint_name;

Releasing a Savepoint

RELEASE SAVEPOINT savepoint_name;

Examples

This section will demonstrate various transaction operations in detail based on a bank account transfer scenario.
1. Create an example table and initialize the data.
-- Create a test table.
CREATE TABLE bank_account (
    account_id VARCHAR(20) PRIMARY KEY,
    account_name VARCHAR(50),
    balance DECIMAL(15, 2)
);

-- Insert initial data.
INSERT INTO bank_account VALUES 
('1001', 'Zhang San', 5000.00),
('1002', 'Li Si', 3000.00);
2. Transaction Operations Demonstration.
-- 1. BEGIN TRANSACTION
START TRANSACTION;

-- Check initial balance
SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
| 1001       | Zhang San    | 5000.00 |
| 1002       | Li Si        | 3000.00 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

-- 2. Create the first savepoint (before deduction)
SAVEPOINT before_debit;

-- 3. Deduct 1000 CNY from Zhang San's account.
UPDATE bank_account SET balance = balance - 1000 WHERE account_id = '1001';

-- 4. Create the second savepoint (after deduction, before deposit)
SAVEPOINT after_debit;

-- 5. Deposit 1000 CNY to Li Si's account.
UPDATE bank_account SET balance = balance + 1000 WHERE account_id = '1002';

-- Check account balance within the current transaction
SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
| 1001       | Zhang San    | 4000.00 |
| 1002       | Li Si        | 4000.00 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

-- Assuming that Li Si's account status is found to be abnormal at this time, it is necessary to roll back to before the deposit operation.
-- 6. Roll back to the second savepoint (This will undo the deposit operation while retaining the deduction operation.)
ROLLBACK TO SAVEPOINT after_debit;

-- Check the balance after rollback (Li Si's account balance remains unchanged)
SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
| 1001       | Zhang San    | 4000.00 |
| 1002       | Li Si        | 3000.00 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

-- 7. Since business rules require that transfers must be complete, we decide to roll back the entire transaction.
ROLLBACK;  -- Roll back to the transaction start state with all operations undone

-- Finally confirm whether the data has been restored to its original state.
SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
| 1001       | Zhang San    | 5000.00 |
| 1002       | Li Si        | 3000.00 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

-- 8. Restart the transaction and perform a complete transfer
START TRANSACTION;

UPDATE bank_account SET balance = balance - 1000 WHERE account_id = '1001';
UPDATE bank_account SET balance = balance + 1000 WHERE account_id = '1002';

-- 9. Commit the transaction to make the changes permanent after confirming everything is correct
COMMIT;

-- Finally confirm whether the data modification has taken effect.
SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
| 1001       | Zhang San    | 4000.00 |
| 1002       | Li Si        | 4000.00 |
+------------+--------------+---------+
2 rows in set (0.00 sec)
이전 주제: Overview다음 주제: Isolation Level

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백