Extensive ecological restoration initiatives have been systematically executed on a global scale, particularly within arid and semiarid regions. These endeavors have substantially transformed the landscapes of water-limited ecosystems, exemplified by the Inner Mongolia section of the Yellow River Basin in China. However, the balance between carbon sequestration and water usage continues to be a subject of ongoing debate. Utilizing multisource remote sensing data and an interpretable machine learning framework, this study disentangles the spatiotemporal dynamics of functional carbon pools (irrecoverable, manageable, and vulnerable) from 2017 to 2025. We show that the region acted as a substantial net carbon sink (+55.4 Tg C), driven by a complex interplay between anthropogenic management and distinct environmental thresholds. Unlike the forest-dominated carbon sinks in humid regions, we observe that manageable carbon (primarily from agricultural management) contributed more to the regional carbon increment (+18.7 Tg C) than vegetation restoration (+8.1 Tg C), exhibiting higher stability than vegetation restoration in water-limited transition zones. Crucially, we identified a nonlinear topographic threshold at 1,200 m: below this elevation, carbon density increases rapidly with intensive human management; however, above this threshold, the growth rate saturates due to hydrothermal constraints, indicating a limit to the biological carbon pump. Based on these findings, we conclude that future sustainability depends on a spatially differentiated strategy: policies must prioritize the protection of irrecoverable carbon in fragile uplands while optimizing water-saving agricultural practices to enhance manageable carbon in the plains.