Querying data by primary key prompts TTL timeout lock expired

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

Original topic: 根据主键查询数据提示TTL超时锁过期

| username: Hacker_0LElpqkb

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

Querying a single piece of data by primary key, during the query, this piece of data did not execute update or delete statements, and there were no running transactions.

When the first query fails, querying again can succeed, and this happens frequently.

SQL state [HY000]; error code [8229]; TTL manager has timed out, pessimistic locks may expire, please commit or rollback this transaction; nested exception is java.sql.SQLException: TTL manager has timed out, pessimistic locks may expire, please commit or rollback this transaction

| username: 小于同学 | Original post link

Could you share the table structure?

| username: Hacker_0LElpqkb | Original post link

CREATE TABLE sjjh_sjly (
data_code varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘Data Code’,
ly_name varchar(125) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Source Name’,
bs varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Identifier’,
xzqh varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Administrative Division Code’,
xzqh_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Administrative Division Name’,
status varchar(11) COLLATE utf8_general_ci DEFAULT ‘1’ COMMENT ‘Call Status 1 Valid 0 Invalid’,
sp_time varchar(255) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Approval Time’,
sq_name varchar(255) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Applicant Organization Name’,
remark varchar(255) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘Remarks’,
create_time datetime DEFAULT NULL COMMENT ‘Creation Time’,
tx_type int(1) DEFAULT NULL,
PRIMARY KEY (data_code) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY sjly (bs)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘Data Source Information Table’;

| username: 哈喽沃德 | Original post link

Try running the SQL statement and see.

| username: TiDBer_aaO4sU46 | Original post link

Is it the issue with large transactions? Try to minimize transaction processing time to avoid occupying or locking resources for an extended period.

| username: zhaokede | Original post link

Are there long transactions involving this table?