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

Data Processing And Security Agreement

Glossary

Pessimistic Transactions

PDF

포커스 모드

폰트 크기

마지막 업데이트 시간: 2026-03-06 18:48:24

Basic Concept of Pessimistic Locking
Pessimistic locking is a database concurrency control mechanism based on the core principle of acquire the lock first before accessing. It assumes a high probability of data conflicts in multi-user concurrent environments. Therefore, it locks data before access to prevent other transactions from modifying or reading the data, thereby ensuring data consistency.
Pessimistic locking is characterized by strong exclusivity and exclusiveness. When a transaction needs to operate on data, it first obtains the lock, ensuring that during its execution, other transactions cannot modify the data. This mechanism is suitable for high-contention scenarios, effectively avoiding concurrency issues such as dirty reads, phantom reads, and non-repeatable reads.
Compared to optimistic locking, pessimistic locking adopts a more conservative policy. Optimistic locking assumes that conflicts rarely occur and checks for conflicts only when operations are committed; while pessimistic locking assumes that conflicts often occur and prevents conflicts in advance.
Implementation Methods of Pessimistic Locking
Pessimistic locking in TDSQL Boundless is primarily implemented through the following two SQL statements:
Exclusive Lock (SELECT ... FOR UPDATE)
Exclusive Lock, also known as write lock, is the most commonly used implementation of pessimistic locking. Using the SELECT ... FOR UPDATE statement can place an exclusive lock on the queried data rows, preventing other transactions from modifying or locking these data rows.
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- Operate on the queried data.
UPDATE orders SET status = 'shipped' WHERE id = 1;
COMMIT;
In this example, after transaction A executes SELECT ... FOR UPDATE, if other transactions attempt to execute SELECT ... FOR UPDATE or modify operations on the same data row, they will be blocked until transaction A commits or rolls back.
Shared Lock (SELECT ... LOCK IN SHARE MODE)
Shared Lock, also known as read lock, uses the SELECT ... LOCK IN SHARE MODE statement to place a shared lock on the queried data rows. Shared locks allow multiple transactions to read the same data row concurrently but will prevent any transaction from modifying the data row.
START TRANSACTION;
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
-- Other transactions can read but cannot modify this data.
COMMIT;
Shared locks are suitable for scenarios where it is necessary to ensure that data remains unmodified during read operations while allowing multiple transactions to read concurrently.
Pessimistic Lock Granularity
TDSQL Boundless's storage engine supports row-level locks, but the actual lock granularity depends on whether the query condition uses an index. If the condition field in the WHERE clause has an index, TDSQL Boundless will use row-level locks; if there is no index, it may escalate to range locks.
-- Create table
CREATE TABLE users (
   user_id INT PRIMARY KEY,
   username VARCHAR(20)
);
﻿
SELECT * FROM users WHERE user_id = 101 FOR UPDATE; -- Row-level lock
SELECT * FROM users WHERE username = 'john' FOR UPDATE; -- Range lock (username column has no index, locks the entire table's data)
SELECT * FROM users WHERE user_id >= 100 AND user_id < 200 FOR UPDATE; -- Range lock (locks data only within the user_id range [100, 200))
Comparison with MySQL (InnoDB) Pessimistic Locking Behavioral Differences
The core differences in pessimistic locking between TDSQL Boundless and MySQL (InnoDB) stem from their architectural designs:
MySQL (InnoDB) locks are oriented towards physical storage, with lock information recording the "physical address" of data rows on disk (which tablespace, which page, which row). This approach is highly efficient within its single-machine sequential storage context.
TDSQL Boundless locks are oriented towards logical keys, with lock information recording the "unique identifier" of data rows (primary key value). This design naturally aligns with its distributed, hash-based storage architecture, enabling lock management to be decoupled from data storage nodes and achieving superior scalability.
Comparison Dimension
TDSQL Boundless​
MySQL (Based on InnoDB)
Core Differences and Impacts
Lock target (what to lock)
Logical key (such as Primary Key = 15)
Physical storage location of data (Space ID + Page Number + bitmap)
The fundamental difference: TDSQL Boundless does not concern itself with the physical location of data, locking directly on the unique logical identifier. MySQL (InnoDB) locks are intricately tied to the physical page layout of the data.
Locking a single record
(such as: WHERE number=15)
Behavior is consistent: whether the record exists or not, a row-level lock is applied to the logical key primary key = 15.
Behavior diverges:
1. Record exists: apply a row-level lock to the record where number=15.
2. Record does not exist: to prevent phantom reads, a gap lock is applied, locking the interval between two adjacent records (such as number=7 and number=20).
Lock granularity and semantics: TDSQL Boundless exhibits more consistent and predictable locking behavior. MySQL (InnoDB) escalates to a broader gap lock when records are absent, potentially impacting concurrent inserts.
Range query locking
(for example: WHERE row_id BETWEEN 1 AND 20)
Holistic or adaptive decomposition:
1. No conflict: directly lock the entire logical range [1, 20].
2. Conflict detected: if a point within the range (such as row_id=15) is already locked by another transaction, the locking range will be decomposed into non-contiguous intervals, [1, 15) + [15, 20], and wait for the lock to be released.
Segmented locking: according to the physical storage order of pages, split the query range into multiple small intervals (such as (1,3], (3,5], (5,10], and so on) and apply locks.
Locking policy: TDSQL Boundless tends to logically maintain a large lock range, decomposing only upon encountering lock conflicts. MySQL (InnoDB)'s locking granularity is influenced by physical data page divisions, resulting in finer fragmentation.
Characteristics Summary
Direct locking: the locking process is independent of physical locations, logically clear, and particularly suitable for distributed architectures.
Locking requires prior positioning: before locks are applied, it is necessary to locate the physical page where the data resides. Lock management is tightly coupled with the storage structure.
Architectural adaptability: TDSQL Boundless implementation offers better scalability in distributed environments. MySQL (InnoDB) implementation is highly mature and efficient in single-node centralized deployments.
﻿
﻿

도움말 및 지원

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

더 자세한 내용은 문의하기 또는 티겟 제출 을 통해 문의할 수 있습니다.

피드백

tencent cloud

TDSQL Boundless

Pessimistic Transactions

Basic Concept of Pessimistic Locking

Implementation Methods of Pessimistic Locking

Exclusive Lock (`SELECT ... FOR UPDATE`)

Shared Lock (`SELECT ... LOCK IN SHARE MODE`)

Pessimistic Lock Granularity

Comparison with MySQL (InnoDB) Pessimistic Locking Behavioral Differences

도움말 및 지원

Comparison Dimension	TDSQL Boundless	MySQL (Based on InnoDB)	Core Differences and Impacts
Lock target (what to lock)	Logical key (such as Primary Key = 15)	Physical storage location of data (Space ID + Page Number + bitmap)	The fundamental difference: TDSQL Boundless does not concern itself with the physical location of data, locking directly on the unique logical identifier. MySQL (InnoDB) locks are intricately tied to the physical page layout of the data.
Locking a single record (such as: `WHERE number=15`)	Behavior is consistent: whether the record exists or not, a row-level lock is applied to the logical key primary key = 15.	Behavior diverges: 1. Record exists: apply a row-level lock to the record where number=15. 2. Record does not exist: to prevent phantom reads, a gap lock is applied, locking the interval between two adjacent records (such as number=7 and number=20).	Lock granularity and semantics: TDSQL Boundless exhibits more consistent and predictable locking behavior. MySQL (InnoDB) escalates to a broader gap lock when records are absent, potentially impacting concurrent inserts.
Range query locking (for example: `WHERE row_id BETWEEN 1 AND 20`)	Holistic or adaptive decomposition: 1. No conflict: directly lock the entire logical range [1, 20]. 2. Conflict detected: if a point within the range (such as row_id=15) is already locked by another transaction, the locking range will be decomposed into non-contiguous intervals, [1, 15) + [15, 20], and wait for the lock to be released.	Segmented locking: according to the physical storage order of pages, split the query range into multiple small intervals (such as (1,3], (3,5], (5,10], and so on) and apply locks.	Locking policy: TDSQL Boundless tends to logically maintain a large lock range, decomposing only upon encountering lock conflicts. MySQL (InnoDB)'s locking granularity is influenced by physical data page divisions, resulting in finer fragmentation.
Characteristics Summary	Direct locking: the locking process is independent of physical locations, logically clear, and particularly suitable for distributed architectures.	Locking requires prior positioning: before locks are applied, it is necessary to locate the physical page where the data resides. Lock management is tightly coupled with the storage structure.	Architectural adaptability: TDSQL Boundless implementation offers better scalability in distributed environments. MySQL (InnoDB) implementation is highly mature and efficient in single-node centralized deployments.