基于机器学习的设备稳定性测试系统：sqlite数据库锁定原因分析

sqlite数据库锁定原因分析

现象描述

执行数据库写入操作时出现以下错误：

错误: database is locked
ERROR:database:SQL执行失败: 
            INSERT OR REPLACE INTO device_details 
            (device_ip, cpu_id, dev_id, device_id, inner_net_card, mac, mg_id, out_net_card, start_times, project_number, last_updated)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))

该错误表明在高并发写入场景下，SQLite 数据库文件被锁定，导致写入操作失败。

问题根源

SQLite 基于文件级锁机制，在多并发写入场景下易出现锁定冲突。

锁定冲突场景示意

┌─────────────────────────────────────────────────────────────┐
│                      锁定冲突场景                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  设备A写入 ──┐                                              │
│              │                                              │
│  设备B写入 ──┼──▶ 竞争同一把锁 ──▶ database is locked     │
│              │                                              │
│  设备C写入 ──┘                                              │
│                                                             │
│  并发问题：50台设备同时监控 → 50个写入请求竞争锁             │
└─────────────────────────────────────────────────────────────┘

本次触发原因分析

因素	说明
并发过高	监控设备超过50台，每台设备均触发并发写入
WAL模式未生效	预写式日志配置可能存在问题，未能缓解锁冲突
超时设置过短	10秒的锁等待超时在高并发场景下不足
缺乏重试机制	写入失败后直接退出，未进行重试

已实施的优化措施

优化项	预期效果
timeout=30	延长锁等待时间至30秒，提高并发成功率
check_same_thread=False	允许同一连接在多线程中访问
PRAGMA synchronous=NORMAL	降低写入可靠性要求以提升性能，减少锁竞争
3次重试 + 延迟退避	临时锁冲突时自动重试，提高写入成功率

长期优化计划

若设备规模持续扩大（如超过100台），建议进行以下架构调整：

• 数据库升级：从 SQLite 迁移至 MySQL / PostgreSQL，支持更高并发
• 引入写缓冲：通过消息队列（如 Kafka / Redis）缓冲写入请求，批量落库
• 读写分离：监控读取仍可使用 SQLite，写入操作通过队列异步处理