Implement Lock View (Sprint 3)

[MyonKeminta]

Development Task

We are going to implement the feature Lock View in Sprint3, which will be helpful and much improves the experience when diagnosing transaction lock contention, blocking and deadlocking issue.

The development tasks will be done by me and @longfangsong .

Design doc: #24375

Major development tasks:

• Implement TIDB_TRX table which provides information of ongoing transactions.
  • Basic implementation @longfangsong txn: Add txn state's view #22908
  • Implement ALL_SQL_DIGESTS field @MyonKeminta infoschema, executor, txn: implement DATA_LOCK_WAITS table #24750
  • Avoid potential inconsistency among columns caused by not atomically fetching different fields (ref) @MyonKeminta infoschema, executor, txn: implement DATA_LOCK_WAITS table #24750
  • Implement other non-important fields in our design
• Update the protocol in kvproto to pass necessary information around @MyonKeminta Add proto for implementing lock view kvproto#752
• TiKV Support reporting full wait chain information for deadlock errors @MyonKeminta server/lock_manager: Support reporting the full wait chain when deadlock is detected tikv/tikv#10085
• Implement TiKV APIs to support exposing lock waiting information to TiDB @longfangsong txn: export get_lock_wait_info API for querying current lock wait infomation tikv/tikv#10153
• TiDB support passing information about the statement (SQL Digest) to TiKV when acquiring pessimistic lock @MyonKeminta executor, store: Pass the SQL digest down to pessimistic lock request #24380
• Implement DATA_LOCK_WAIT table that shows lock waiting information. @longfangsong infoschema, executor, txn: implement DATA_LOCK_WAITS table #24750
• Implement DEADLOCK table that shows recent several deadlock events.
  • Basic implementation @MyonKeminta infoschema, executor: Add the deadlock table #24524
  • Query CLUSTER_TIDB_TRX on deadlock for more rich information for the related transactions
  • Configurable history capacity @longfangsong config: make deadlock history's max size configuable #24925

Documentation

• User document @MyonKeminta Add Lock View related documents docs-cn#6408

Optional improvements:

• Record information of not-finished statement to statements_summary, so that it can be used to find statements that's being blocked and can't finish.
• Add metrics to monitor memory usage of lock manager and deadlock detector.
• Display internal transactions' information in TIDB_TRX
• Avoid full scanning for TIDB_TRX and DATA_LOCK_WAITS to reduce the performance risk since TiDB automatically queries it when it encounters deadlocks.
• Check if TIDB SQL memquota can work for lock view

Misc

• Design document patch for renaming and introducing configs docs: Some proposal for renaming and configurations for Lock View #24718 docs: Rename some tables' column names in Lock View to reduce confusion #24904
• Renaming of TIDB_TRX's LEN and SIZE column infoschema, executor, txn: change some field name in TIDB_TRX table #25061
• Remove the unimplemented ALL_SQL_DIGESTS field from the deadlocks table infoschema: Remove the unimplemented ALL_SQL_DIGESTS column from the DEADLOCKS table #24939
• Fix unstable test tests: Fix the unstable integration test for get_lock_wait_info api tikv/tikv#10311

[longfangsong]

/assign "Implement DATA_LOCK_WAIT table that shows lock waiting information."

[MyonKeminta]

Ok, I'm the bot :)

[MyonKeminta]

@longfangsong Maybe you need to implement the get_lock_wait_info api first (Implement TiKV APIs to support exposing lock waiting information to TiDB)

[longfangsong]

@longfangsong Maybe you need to implement the get_lock_wait_info api first (Implement TiKV APIs to support exposing lock waiting information to TiDB)

Yes, I think these two should be implemented together.

[MyonKeminta]

Ok, I'll assign you both

[morgo]

I played around with this feature today, and it looks really useful! I have a couple of questions/suggestions:

1. The digest seems to be the last statement executed; but the len+size relates to the whole transaction. Is it possible to actually "digest" a transaction, so that I could see the statements that it is composed of? If this is not planned initially, maybe it is better to rename it to LAST_STATEMENT_DIGEST to be clearer? I was initially confused because all my digests were the same, but that was because I was querying select * from information_schema.tidb_trx in the trx session!

2. Related to the above question, I would love to have a way to see historical transactions as well as active (by digest). As well as locks held, I would like to know a count of the number of statements, the wall time of statements executing, and the wall time of the transaction. MySQL does not currently offer this functionality, but cockroachdb has this (and actually more).

[MyonKeminta]

@morgo Thanks for your feedback!

1. Currently the digest field shows the last executed statement, but that's actually not an expected behavior. In our design, it should show the current executing statement, which is corrected along with this PR. I agree that the naming is kind of confusing, and I'll propose a patch to the design to rename it to CURR_STMT_DIGEST. It's ok since the feature is not released in any version yet. As for the transaction digest, it sounds a good idea to me. However, it's not planned in this sprint, and I think we don't have enough time to implement it in addition.
2. We used to discuss about the information about historical transactions, but finally we thought the time is too short in this sprint, so we didn't plan it in the first version.

Though there are many useful things we didn't plan due to the time limit, we still have chance to continue improving it in the next sprint, but it depends on our PMs' decisions. For now, I think you can still expect that it will be improved in the next version.

[MyonKeminta]

Known issue +1 #25029

[MyonKeminta]

Sprint 3 have ended. This issue will be closed and remaining tasks will be done in the future.
Future works will be managed by this GitHub project.