基于Plotly Dash实现服务器端缓存：解决多客户端定时更新引发的数据库死锁问题

Great question—this is a super common pain point with Dash when scaling to multiple users. The issue with your current setup is that dcc.Interval runs per client session, so every connected user triggers their own database query. That’s a surefire way to hit database deadlocks fast.

APScheduler is exactly the right tool here: it lets you run a single, server-side scheduled task that fetches data once and shares it with all clients. Here’s how to integrate it cleanly with your existing Dash app and dcc.Store:

Step 1: Install Required Packages #

First, add APScheduler to your environment:

pip install apscheduler

Step 2: Modified Full Code with APScheduler Integration #

Here’s the adjusted version of your app, with comments explaining key changes:

from apscheduler.schedulers.background import BackgroundScheduler
import pyodbc
import pandas as pd
from dash import Dash, html, dcc, Input, Output, Trigger
from dash_extensions.enrich import CallbackCache, FileSystemCache

app = Dash(__name__)
app.layout = html.Div([ 
    html.Button("Query data", id="btn"),
    dcc.Graph(id='Time-graph', style={'width': '100%', 'height': '900px'}), 
    dcc.Store(id="store", storage_type="session"), 
    dcc.Interval( 
        id='interval-component-Time', 
        interval=1 * 8000, # Keep your original update interval
        n_intervals=0 
    ) 
]) 

# Reuse your existing cache setup for shared data storage
cc = CallbackCache(cache=FileSystemCache(cache_dir="../cache"))
GLOBAL_DATA_KEY = "shared_database_data"  # Unique key for cached data

# 1. Define the server-side database query task
def scheduled_data_fetch():
    # Connect to DB and fetch data (same logic as before)
    channel = pyodbc.connect("...connection string...")
    Run = pd.read_sql("command ", channel).iloc[0]['Name']
    Event = pd.read_sql("command ", channel).iloc[0]['EventId']
    Lap = pd.read_sql("command ", channel)
    groups = [Run, Event, Lap]
    
    # Store results in global cache (shared across all clients)
    cc.cache.set(GLOBAL_DATA_KEY, groups)
    channel.close()  # Critical: avoid database connection leaks

# 2. Initialize and start the scheduler
scheduler = BackgroundScheduler()
# Run the fetch task every 8 seconds (matches your original interval)
scheduler.add_job(scheduled_data_fetch, 'interval', seconds=8)
scheduler.start()

# 3. Client-side callback: Pull cached data to update dcc.Store
@cc.cached_callback(Output("store", "data"), [Trigger("interval-component-Time", "n_intervals")])
def update_store_from_cache():
    # Fetch pre-computed data from cache instead of querying DB
    return cc.cache.get(GLOBAL_DATA_KEY)

# 4. Your existing graph update callback stays the same
@cc.callback(Output("Time-graph", "figure"), [Input("store", "data")])
def update_graph(data):
    if not data:
        # Handle case where initial data hasn't loaded yet
        return {}
    # ...your existing figure generation code...
    return fig

# Optional: Add manual refresh via button
@cc.callback(Output("store", "data"), [Input("btn", "n_clicks")], prevent_initial_call=True)
def manual_refresh(n_clicks):
    scheduled_data_fetch()  # Trigger immediate data update
    return cc.cache.get(GLOBAL_DATA_KEY)

cc.register(app)

if __name__ == '__main__':
    try:
        app.run_server(debug=True)
    finally:
        # Clean up scheduler when server shuts down
        scheduler.shutdown()

Key Improvements & Explanations #

• Single Source of Truth: APScheduler runs one background task on the server, so only one database query happens every 8 seconds—no matter how many clients are connected. This eliminates the deadlock issue entirely.
• Cache Reuse: We leverage your existing FileSystemCache to store the latest database results. All clients pull from this shared cache instead of hitting the DB directly.
• Resource Safety: Added explicit database connection closure and scheduler shutdown to prevent resource leaks.
• Backward Compatibility: Kept your original dcc.Interval and dcc.Store setup, so client-side behavior feels identical to users.

内容的提问来源于stack exchange，提问作者MaxB