今天使用benchmarksql压测LightDB数据库等待事件排名第二的event type 是lock，wait event为transactionid那么transactionid具体是什么意思呢？今天我们看一下

Wait event statistics

Top wait event type by wait time

Wait Event Type	Wait Time (s)	%Total Wait Time
DBCpu	2702	58.8
Lock	1041	22.6
LWLock	710	15.4
IO	135	2.9
IPC	11	.2

Top wait event by wait time

Wait Event	Wait Event Type	Wait Time (s)	%Total Wait Time
DBCpu	DBCpu	2702	58.8
transactionid	Lock	1030	22.4
WALInsert	LWLock	269	5.8
WALWrite	LWLock	210	4.6
pg_stat_statements	LWLock	123	2.7
ProcArray	LWLock	71	1.5
WALSync	IO	51	1.1
DataFileRead	IO	42	.9
WALWrite	IO	28	.6
TwoPhaseState	LWLock	12	.3
tuple	Lock	11	.2
ProcArrayGroupUpdate	IPC	11	.2
WALRead	IO	9	.2
MultiXactGen	LWLock	8	.2
LockManager	LWLock	6	.1
XactSLRU	LWLock	5	.1
BufferMapping	LWLock	5	.1
DataFileExtend	IO	5	.1
BufferContent	LWLock	1	.0

Lock:transactionid

当事务试图获取已授予同时运行的事务的行级锁时，会发生 Lock:transactionid 事件。显示 Lock:transactionid 等待事件的会话因为这个锁而被阻塞。在阻塞事务以 COMMIT 或 ROLLBACK 语句结束后，阻塞事务可以继续。

LightDB的多版本并发控制语义保证读不会阻塞写入，写入不会阻塞读取。要发生行级冲突，阻塞和阻塞事务必须发出以下类型的冲突语句：

UPDATE
SELECT … FOR UPDATE
SELECT … FOR KEY SHARE

SELECT ... FOR KEY SHARE 语句是一种特殊情况。数据库使用 FOR KEY SHARE 子句来优化参照完整性的性能。行上的行级锁可以阻止引用该行的其他表上的 INSERT、UPDATE 和 DELETE 命令。

Likely causes of increased waits

When this event appears more than normal, the cause is typically UPDATE, SELECT … FOR UPDATE, or SELECT … FOR KEY SHARE statements combined with the following conditions.

Topics

高并发

LightDB for PostgreSQL可以使用细粒度的行级锁定语义。当满足以下条件时，行级冲突的概率会增加：

高并发发生在相同的行。
并发持续增加。

Idle in transaction

如果 lt_stat_activity.state 列显示为idle in transaction。此状态表示事务已经发起但是尚未COMMIT或 ROLLBACK，处于事务开启中。如果 lt_stat_activity.state值不是active状态，则 lt_stat_activity 中显示的查询是最近完成运行的查询。

如果一个空闲事务获得了行级锁，它可能会阻止其他会话获得它。这种情况导致等待事件Lock:transactionid的频繁发生。要诊断问题，可以通过pg_stat_activity 和 pg_locks 进行查询。

长事物

长时间运行的事务会获得很长时间的锁定。这些长期持有的锁可以阻止其他事务运行。

解决思路

Row-locking is a conflict among UPDATE, SELECT … FOR UPDATE, or SELECT … FOR KEY SHARE statements. Before attempting a solution, find out when these statements are running on the same row. Use this information to choose a strategy described in the following sections.

Respond to high concurrency

If concurrency is the issue, try one of the following techniques:

Lower the concurrency in the application. For example, decrease the number of active sessions.
Implement a connection pool.
Design the application or data model to avoid contending UPDATE and SELECT … FOR UPDATE statements. You can also decrease the number of foreign keys accessed by SELECT … FOR KEY SHARE statements.

Respond to idle transactions

If pg_stat_activity.state shows idle in transaction, use the following strategies:

Turn on autocommit wherever possible. This approach prevents transactions from blocking other transactions while waiting for a COMMIT or ROLLBACK.
Search for code paths that are missing COMMIT, ROLLBACK, or END.
Make sure that the exception handling logic in your application always has a path to a valid end of transaction.
Make sure that your application processes query results after ending the transaction with COMMIT or ROLLBACK.

Respond to long-running transactions

If long-running transactions are causing the frequent occurrence of Lock:transactionid, try the following strategies:

Keep row locks out of long-running transactions.
Limit the length of queries by implementing autocommit whenever possible.

ITPub博客

LightDB/PostgreSQL等待事件 Lock transactionid