Skip to content

graphdb: fix potential sql tx exhaustion#10428

Merged
yyforyongyu merged 2 commits intolightningnetwork:masterfrom
ziggie1984:fix-sql-pool-exhaustion
Dec 9, 2025
Merged

graphdb: fix potential sql tx exhaustion#10428
yyforyongyu merged 2 commits intolightningnetwork:masterfrom
ziggie1984:fix-sql-pool-exhaustion

Conversation

@ziggie1984
Copy link
Collaborator

@ziggie1984 ziggie1984 commented Dec 7, 2025

We should avoid taking the lock of a mutex inside transaction.
Currently we also take this lock in other places and there is a
chance that in case the application lock aquires the lock but
all transactions are already blocked waiting for the mutex to
unlock, we end up in a deadlock.

@gemini-code-assist
Copy link

gemini-code-assist bot commented Dec 7, 2025

Summary of Changes

Hello @ziggie1984, I'm Gemini Code Assist1! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!

This pull request resolves a critical issue where taking a mutex lock inside a database transaction could lead to deadlocks. By strategically moving the mutex acquisition to occur before the transaction begins and releasing it after the transaction completes, the changes establish a clear and consistent locking hierarchy. This ensures that database operations and application-level caching mechanisms interact without contention, thereby enhancing system stability and preventing transaction exhaustion.

Highlights

  • Deadlock Prevention: The pull request addresses a potential deadlock scenario by reordering the acquisition of a read lock (cacheMu.RLock()) to occur before initiating database transactions. This ensures a consistent lock ordering, preventing situations where application locks might block transactions waiting for the mutex.
  • Consistent Lock Ordering: The fetchNextChanUpdateBatch and ChanUpdatesInHorizon functions have been refactored. The cacheMu.RLock() call is now placed outside and prior to the database transaction, and the corresponding cacheMu.RUnlock() is either moved outside and after the transaction or removed if redundant within the transaction loop.
Using Gemini Code Assist

The full guide for Gemini Code Assist can be found on our documentation page, here are some quick tips.

Invoking Gemini

You can request assistance from Gemini at any point by creating a comment using either /gemini <command> or @gemini-code-assist <command>. Below is a summary of the supported commands on the current page.

Feature Command Description
Code Review /gemini review Performs a code review for the current pull request in its current state.
Pull Request Summary /gemini summary Provides a summary of the current pull request in its current state.
Comment @gemini-code-assist Responds in comments when explicitly tagged, both in pull request comments and review comments.
Help /gemini help Displays a list of available commands.

Customization

To customize Gemini Code Assist for GitHub experience, repository maintainers can create a configuration file and/or provide a custom code review style guide (such as PEP-8 for Python) by creating and adding files to a .gemini/ folder in the base of the repository. Detailed instructions can be found here.

Limitations & Feedback

Gemini Code Assist may make mistakes. Please leave feedback on any instances where its feedback is incorrect or counter productive. You can react with 👍 and 👎 on @gemini-code-assist comments. If you're interested in giving your feedback about your experience with Gemini Code Assist for Github and other Google products, sign up here.

You can also get AI-powered code generation, chat, as well as code reviews directly in the IDE at no cost with the Gemini Code Assist IDE Extension.

Footnotes

  1. Review the Privacy Notices, Generative AI Prohibited Use Policy, Terms of Service, and learn how to configure Gemini Code Assist in GitHub here. Gemini can make mistakes, so double check it and use code with caution. ↩

gemini-code-assist[bot]
gemini-code-assist bot reviewed Dec 7, 2025
View reviewed changes
Copy link

@gemini-code-assist gemini-code-assist bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Code Review

This pull request addresses a potential deadlock issue by changing the lock acquisition order in kv_store.go and sql_store.go. The changes ensure that the cacheMu read lock is taken before initiating a database transaction, which resolves the transaction exhaustion problem. The implementation is correct and clean in both files, using defer where appropriate in kv_store.go and manual lock/unlock inside the loop in sql_store.go. The code is now more robust against deadlocks. Great work!

@saubyk saubyk added the database Related to the database/storage of LND label Dec 7, 2025
@saubyk saubyk added this to lnd v0.20 Dec 7, 2025
@saubyk saubyk moved this to In progress in lnd v0.20 Dec 7, 2025
@saubyk saubyk added this to the v0.20.1 milestone Dec 7, 2025
@ziggie1984
Copy link
Collaborator Author

ziggie1984 commented Dec 7, 2025 •
edited
Loading

A more general description of the problem:

  Problem: Lock Ordering Deadlock with Limited Connection Pool

  Root Cause

  ChanUpdatesInHorizon acquires cacheMu.RLock() inside a database transaction, creating deadlock risk when the connection pool is exhausted.

  Deadlock Scenario

  With connection pool size = 3:

  1. 3 goroutines running ChanUpdatesInHorizon:
    - Each acquires a DB connection (3/3 pool exhausted)
    - Each executes query successfully
    - Each tries to acquire cacheMu.RLock() inside the transaction
  2. Any other goroutine holds cacheMu (write lock)
  3. Deadlock:
    - The 3 goroutines block waiting for the mutex
    - They cannot commit transactions (blocked in Go code)
    - Connections remain held, showing "idle in transaction" in PostgreSQL
    - No connections available for other operations
    - System stuck

  Fix

  Acquire cacheMu.RLock() before starting the database transaction:
  cacheMu.RLock()
  BeginTx() → query → Commit()
  cacheMu.RUnlock()

  This ensures locks are never held while waiting for database I/O, preventing the deadlock.

@ziggie1984
Copy link
Collaborator Author

ziggie1984 commented Dec 7, 2025

the relevant pg log from the affected noderunner is here:

https://gist.github.com/xmrk-btc/f2f1c58c30ace6e109525292fc9d72dd

@ziggie1984 ziggie1984 requested a review from Roasbeef December 7, 2025 20:58
ellemouton
ellemouton approved these changes Dec 9, 2025
View reviewed changes
Copy link
Collaborator

@ellemouton ellemouton left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

lgtm!

@xmrk-btc
Copy link

xmrk-btc commented Dec 9, 2025 •
edited
Loading

I reported the problem, it occurred with lnd 0.20.0,

I have been running for 36 hours lnd 0.20.0 + cherry-picked commit 1831ffa . No issues so far, and without the fix, lnd got stuck in less than 24 hours. Also, my current pg_stat_activity doesn't show any stuck GetChannelsByPolicyLastUpdateRange query.

So lgtm.

yyforyongyu
yyforyongyu reviewed Dec 9, 2025
View reviewed changes
Copy link
Member

@yyforyongyu yyforyongyu left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks! Pending CI, otherwise good to go!

@ziggie1984
graphdb: fix potential sql tx exhaustion
2d25bce 
We should avoid taking the lock of a mutex inside transaction.
Currently we also take this lock in other places and there is a
chance that in case the application lock aquires the lock but
all transactions are already blocked waiting for the mutex to
unlock, we end up in a deadlock.
@ziggie1984
docs: add release-notes for LND 20.1
f289e49
@ziggie1984 ziggie1984 force-pushed the fix-sql-pool-exhaustion branch from 6829f57 to f289e49 Compare December 9, 2025 10:51
yyforyongyu
yyforyongyu approved these changes Dec 9, 2025
View reviewed changes
Copy link
Member

@yyforyongyu yyforyongyu left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM 🔨

Double checked the CI failure, and it's not related,

=========> tranche 2 finished, tested 33 cases, mined blocks: 55
--- FAIL: TestLightningNetworkDaemon (417.60s)
    harness_setup.go:25: Setting up HarnessTest...
    harness_setup.go:38: Prepare the miner and mine blocks to activate segwit...
    harness_setup.go:46: Connecting the miner at 127.0.0.1:10023 with the chain backend...
    --- FAIL: TestLightningNetworkDaemon/tranche02/71-of-260/btcd/multihop-local_claim_outgoing_htlc_anchor_zero_conf (185.72s)
        harness_node.go:395: Starting node (name=Alice) with PID=5400
        harness_node.go:395: Starting node (name=Bob) with PID=8916
        harness_node.go:395: Starting node (name=Carol) with PID=2552
        lnd_multi-hop_force_close_test.go:467: Bob's timelock to_local output=3, timelock on second stage htlc=3
        harness_assertion.go:1646: 
            	Error Trace:	D:/a/lnd/lnd/lntest/harness_assertion.go:1646
            	            				D:/a/lnd/lnd/itest/lnd_multi-hop_force_close_test.go:528
            	            				D:/a/lnd/lnd/itest/lnd_multi-hop_force_close_test.go:238
            	            				D:/a/lnd/lnd/lntest/harness.go:315
            	            				D:/a/lnd/lnd/itest/lnd_test.go:130
            	Error:      	Received unexpected error:
            	            	payment: 8346cdfea1123734df610273a835877db24ac3edca93eef5d2e366527056f1cc failure reason not match, want FAILURE_REASON_NO_ROUTE(2) got FAILURE_REASON_TIMEOUT(1)
            	Test:       	TestLightningNetworkDaemon/tranche02/71-of-260/btcd/multihop-local_claim_outgoing_htlc_anchor_zero_conf
            	Messages:   	timeout checking payment failure reason
        harness.go:393: finished test: multihop-local_claim_outgoing_htlc_anchor_zero_conf, start height=493, end height=527, mined blocks=34
        harness.go:352: test failed, skipped cleanup
        harness_rpc.go:100: 
            	Error Trace:	D:/a/lnd/lnd/lntest/rpc/harness_rpc.go:100
            	            				D:/a/lnd/lnd/lntest/rpc/lnd.go:188
            	            				D:/a/lnd/lnd/lntest/harness_assertion.go:1557
            	            				D:/a/lnd/lnd/lntest/harness_assertion.go:1631
            	            				D:/a/lnd/lnd/lntest/wait/wait.go:51
            	            				D:/a/lnd/lnd/lntest/wait/wait.go:27
            	            				C:/hostedtoolcache/windows/go/1.25.3/x64/src/runtime/asm_amd64.s:1693
            	Error:      	Received unexpected error:
            	            	rpc error: code = Canceled desc = context canceled
            	Messages:   	Alice: failed to call ListPayments
    lnd_test.go:138: Failure time: 2025-12-09 11:04:46.760
FAIL

@yyforyongyu yyforyongyu merged commit cb3991e into lightningnetwork:master Dec 9, 2025
37 of 41 checks passed
@github-project-automation github-project-automation bot moved this from In progress to Done in lnd v0.20 Dec 9, 2025
@ziggie1984 ziggie1984 mentioned this pull request Dec 9, 2025
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@yyforyongyu yyforyongyu yyforyongyu approved these changes

@ellemouton ellemouton ellemouton approved these changes

@Roasbeef Roasbeef Awaiting requested review from Roasbeef

+1 more reviewer

@gemini-code-assist gemini-code-assist[bot] gemini-code-assist[bot] left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

@ziggie1984 ziggie1984

Labels

database Related to the database/storage of LND

Projects

lnd v0.20
Status: Done

Milestone

v0.20.1

Development

Successfully merging this pull request may close these issues.

5 participants

@ziggie1984 @xmrk-btc @yyforyongyu @ellemouton @saubyk