tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessDeveloper GuideDeveloper Guide (MySQL Compatibility Mode)Transaction ManagementPessimistic Transactions

Pessimistic Transactions

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 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.


Anterior: Isolation LevelPróximo: Transaction Limit

Ajuda e Suporte

Esta página foi útil?

comentários