LightDB/PostgreSQL等待事件 Lock transactionid

哎呀我的天吶發表於2022-05-17
SQL事件

今天使用benchmarksql壓測LightDB資料庫等待事件排名第二的event type 是lock，wait event為transactionid那麼transactionid具體是什麼意思呢？今天我們看一下

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

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

The  Lock:transactionid  event occurs when a transaction is waiting for a row-level lock.

當事務試圖獲取已授予同時執行的事務的行級鎖時，會發生 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部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2894957/，如需轉載，請註明出處，否則將追究法律責任。

相關文章