Encountering Primary Lock Issues During Table Write Operations

Translated
1

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

Original topic: 表写入遇到 primary lock 问题

| username: Leonadle

[TiDB Version]

  • v7.1.3

[Encountered Issues]

  1. There is a business table with a relatively simple logic and a low probability of concurrent reads. The process is to first lock on Redis, then check if there is data. If there is data, update it; if not, insert it. However, the logs often report lock conflict errors Key is locked (will clean up) primary_lock.

image
image1380×588 223 KB

  1. The structure of this table is as follows, with the primary key composed of three fields:
CREATE TABLE `test` (
  `type` tinyint(3) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,
  `device_type` tinyint(3) unsigned NOT NULL,
  `pv` int(10) unsigned NOT NULL,
  PRIMARY KEY (`type`,`post_id`,`device_type`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin'
  1. Checking the TIKV_REGION_STATUS table shows that there are a total of 55 regions, but only 1 region of the PRIMARY type. It is suspected that the inaccurate region statistics might be causing the frequent primary_lock issues.
2
| username: Leonadle | Original post link

Number of primary regions:

image
image1380×298 24.7 KB

3
| username: WalterWj | Original post link

Check this out: TiDB 锁冲突问题处理 | PingCAP 文档中心 :thinking:

4
| username: tidb菜鸟一只 | Original post link
  • A small amount of txnLock appearing in the monitoring does not require much attention. The background will automatically perform backoff retries, with an initial single retry of 100 ms and a maximum of 3000 ms per retry.
  • If a large number of txnLocks appear, it is necessary to evaluate the cause of the conflict from a business perspective.
  • Use pessimistic locking mode.
5
| username: Leonadle | Original post link

The concurrency of this table is not high.

6
| username: Jasper | Original post link

Does it have a real impact on the business? If the backoff time is not very long, this can be ignored.

7
| username: zhanggame1 | Original post link

If there is data, update it; if there is no data, insert it. How is this specifically implemented? Is it using select for update?