Issues with Range Locking in Pessimistic Transaction Mode

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 悲观事务模式范围查询加锁的问题

| username: TiDBer_BA6AjAYE

[TiDB Usage Environment] Testing
[TiDB Version] 6.6
[Reproduction Path] Usage consultation.
[Encountered Problem: Problem Phenomenon and Impact] When using “select * from t1 for update where…” without specifying the primary key under the pessimistic transaction RC level, will a row lock be added to each row of the queried data? Since the primary key is not specified, how is the row lock added? Thank you.
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

| username: xfworld | Original post link

Check this out: 事务限制 | PingCAP 文档中心


If the primary key is not specified, it will find the corresponding primary key based on the conditions and then lock it…
However, TiDB’s handling of locking for select for update is quite special and somewhat different from the known method in MySQL, so I recommend you check the documentation…

| username: Kongdom | Original post link

:+1: We also used this syntax, but we haven’t really studied it in depth. I’ll learn as well.

| username: redgame | Original post link

Executing row locks without specifying a primary key may lead to performance issues because TiDB requires additional overhead to determine the position of each row.

| username: TiDBer_BA6AjAYE | Original post link

Does this mean that TiDB first finds each row and then locks each row found? Querying without specifying the primary key is a consistent snapshot, so querying first and then locking seems to have consistency issues?

| username: TiDBer_BA6AjAYE | Original post link

Multi-statement transactions.
First, check the primary key and then lock it. If the key is modified in the gap between the query and the lock, it won’t be locked. This detail is not described in the documentation…

| username: xfworld | Original post link

It mainly involves locking through transactions, you can give it a try.

| username: TiDBer_BA6AjAYE | Original post link

Well, without specifying the primary key, you can’t lock first and then query. If you query first and then lock, the query results are not locked, making the results unreliable. So there are some questions about how to lock here. :joy:

| username: xfworld | Original post link

Then just get the primary key information first, and then lock it…

| username: TiDBer_BA6AjAYE | Original post link

The primary key information obtained without locking is unreliable and can be added or deleted by other transactions at any time. Generally, the primary key is locked first to determine the condition, but without the primary key, it is impossible to lock, so there is some confusion about the specific behavior here…

| username: Jellybean | Original post link

TiKV is implemented based on RocksDB, so “add and delete” are both implemented in a put-append manner.

  • Adding a row of data is directly put at the end of the file.
  • Deleting a row of data, with a delete marker, is also directly put at the end of the file. Later, the system will compact and merge based on the key, and GC will reclaim the space.

When TiDB reads data, it always reads the last committed data, so old data will not be read.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.