1. UNLOCKED（未锁定状态）
├── 初始状态
├── 无任何锁
├── 可以被任何进程访问
└── 不持有任何资源

2. SHARED（共享锁）
├── 读操作获取
├── 多个进程可同时持有
├── 允许其他SHARED锁
├── 阻止RESERVED/EXCLUSIVE锁
└── 用于SELECT操作

3. RESERVED（保留锁）
├── 准备写入时获取
├── 只有一个进程可持有
├── 其他进程可继续读取（SHARED锁）
├── 阻止其他RESERVED锁
└── 用于准备写入

4. PENDING（待定锁）
├── 从RESERVED升级
├── 等待现有SHARED锁释放
├── 阻止新的SHARED锁
├── 准备进入EXCLUSIVE锁
└── 过渡状态

5. EXCLUSIVE（独占锁）
├── 写操作获取
├── 完全独占数据库
├── 阻止所有其他锁
├── 用于实际写入
└── 持有时间最短

读操作锁路径：
UNLOCKED → SHARED → UNLOCKED

写操作锁路径（回滚日志模式）：
UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE → UNLOCKED

WAL模式锁路径：
UNLOCKED → SHARED（读）
UNLOCKED → SHARED → RESERVED（写，写入WAL）
UNLOCKED → SHARED → EXCLUSIVE（checkpoint）

锁升级示意图：
┌─────────────┐
│ UNLOCKED │
└──────┬──────┘
│ SELECT
↓
┌─────────────┐
┌───────│ SHARED │←───────┐
│ └──────┬──────┘ │
│ │ │
│ │ 准备写入 │ 读取完成
│ ↓ │
│ ┌─────────────┐ │
│ │ RESERVED │ │
│ └──────┬──────┘ │
│ │ │
│ │ 等待读者 │
│ ↓ │
│ ┌─────────────┐ │
│ │ PENDING │────────┘
│ └──────┬──────┘
│ │
│ │ 获取独占
│ ↓
│ ┌─────────────┐
└──────→│ EXCLUSIVE │
└─────────────┘

1. 多进程模式
├── 多个独立进程访问同一数据库
├── 通过文件锁协调
├── 需要本地文件系统
└── 不支持NFS等网络文件系统

2. 多线程模式
├── 多个线程共享同一连接
├── 需要启用线程安全编译选项
├── 序列化模式（默认）
└── 连接不能跨线程共享

3. 混合模式
├── 多进程 + 多线程
├── 每进程独立连接
├── 线程池管理连接
└── 推荐生产使用

线程模式配置：
├── sqlite3_threadsafe()返回值
├── 0：单线程模式
├── 1：序列化模式（默认）
└── 2：多线程模式

编译选项：
├── SQLITE_THREADSAFE=0：禁用线程安全
├── SQLITE_THREADSAFE=1：序列化模式
└── SQLITE_THREADSAFE=2：多线程模式

传统模式（回滚日志）：
├── 写操作阻塞所有读操作
├── 读操作阻塞写操作
├── 并发性能较低
└── 适合读多写少

WAL模式：
├── 读操作不阻塞写操作
├── 写操作不阻塞读操作
├── 多个读操作可并发
├── 写操作串行执行
└── 并发性能显著提升

WAL模式并发示意：
┌─────────────────────────────────────────┐
│ 时间线 │
├─────────────────────────────────────────┤
│ 读操作1: ════════════════════════════ │
│ 读操作2: ════════════════════════════ │
│ 读操作3: ════════════════════════════│
│ 写操作: ████ │
│ Checkpoint: ████ │
└─────────────────────────────────────────┘

WAL模式限制：
├── 不支持网络文件系统
├── 需要共享内存支持
├── 多进程需要协调
└── 额外的-shm和-wal文件

1. 写操作阻塞
├── 场景：进程A持有SHARED锁，进程B尝试写入
├── 现象：进程B等待进程A释放SHARED锁
├── 解决：设置busy_timeout或优化事务

2. 读操作阻塞
├── 场景：进程A持有EXCLUSIVE锁，进程B尝试读取
├── 现象：进程B等待进程A释放EXCLUSIVE锁
├── 解决：使用WAL模式减少阻塞

3. Checkpoint阻塞
├── 场景：WAL模式下checkpoint需要EXCLUSIVE锁
├── 现象：checkpoint等待所有读操作完成
├── 解决：调整checkpoint策略

4. 锁竞争
├── 场景：多个进程同时尝试获取RESERVED锁
├── 现象：只有一个成功，其他等待
├── 解决：使用IMMEDIATE事务

阻塞时间计算：
├── 默认：立即返回错误
├── 设置busy_timeout：等待指定毫秒
└── busy_handler：自定义等待逻辑

1. busy_timeout
├── 设置等待超时时间
├── 自动重试获取锁
├── 单位：毫秒
└── 推荐值：5000-30000

2. busy_handler
├── 自定义等待回调函数
├── 可实现指数退避
├── 更灵活的控制
└── 需要编程实现

3. 事务类型选择
├── DEFERRED：延迟获取锁
├── IMMEDIATE：立即获取RESERVED锁
└── EXCLUSIVE：立即获取EXCLUSIVE锁

4. WAL模式
├── 减少读写阻塞
├── 提高并发性能
└── 推荐生产使用

配置示例：
sqlite> PRAGMA busy_timeout = 5000;
sqlite> BEGIN IMMEDIATE TRANSACTION;
sqlite> — 执行操作
sqlite> COMMIT;

1. 事务类型选择
├── 读操作：DEFERRED（默认）
├── 写操作：IMMEDIATE
├── 批量操作：EXCLUSIVE
└── 长事务：IMMEDIATE

2. 锁持有时间
├── 最小化锁持有时间
├── 事务内避免耗时操作
├── 及时提交或回滚
└── 避免用户交互

3. 锁顺序
├── 按固定顺序获取锁
├── 避免死锁
├── 使用超时机制
└── 监控锁等待

4. 锁粒度
├── 单数据库文件锁
├── 无法行级锁
├── 通过事务控制
└── 合理拆分数据库

锁超时配置建议：
┌─────────────────┬──────────────────────────┐
│ 场景 │ busy_timeout建议 │
├─────────────────┼──────────────────────────┤
│ 快速响应应用 │ 1000-3000毫秒 │
│ 一般应用 │ 5000-10000毫秒 │
│ 批处理应用 │ 30000-60000毫秒 │
│ 后台任务 │ 60000毫秒以上 │
└─────────────────┴──────────────────────────┘

1. 单进程应用
├── 单连接：简单可靠
├── 连接池：提高性能
└── 推荐：连接池大小=CPU核心数

2. 多线程应用
├── 每线程独立连接
├── 连接池管理
├── 避免跨线程共享连接
└── 推荐：线程池+连接池

3. 多进程应用
├── 每进程独立连接
├── WAL模式
├── 合理设置超时
└── 推荐：进程数≤CPU核心数

4. Web应用
├── 连接池（10-50个）
├── WAL模式
├── 读写分离（可选）
└── 推荐：连接池+缓存

并发连接数计算：
├── 基础：CPU核心数
├── 读密集：2×CPU核心数
├── 写密集：CPU核心数
└── 混合：1.5×CPU核心数

1. 预防策略
├── 使用WAL模式
├── 优化事务设计
├── 减少锁持有时间
└── 合理设置超时

2. 检测策略
├── 监控锁等待时间
├── 记录阻塞事件
├── 设置告警阈值
└── 定期分析日志

3. 处理策略
├── 自动重试机制
├── 超时回滚
├── 降级处理
└── 告警通知

4. 优化策略
├── 分析阻塞原因
├── 优化热点数据
├── 调整事务设计
└── 升级硬件资源

监控指标：
├── 锁等待次数
├── 锁等待时间
├── 超时次数
├── 死锁次数
└── 事务响应时间

查看数据库状态：
sqlite> PRAGMA database_list;
seq name file
— —- ———————–
0 main /sqlite/fgdata/fgedudb.db
2 temp

查看WAL模式状态：
sqlite> PRAGMA journal_mode;
journal_mode
————
wal

查看锁等待配置：
sqlite> PRAGMA busy_timeout;
busy_timeout
————
5000

测试锁状态：
— 终端1：开始事务
sqlite> BEGIN EXCLUSIVE TRANSACTION;
sqlite> — 此时持有EXCLUSIVE锁

— 终端2：查看锁状态
sqlite> PRAGMA lock_status;
main reserved 0
temp closed 0

— 终端2：尝试读取（会阻塞）
sqlite> SELECT * FROM fgedu_users LIMIT 1;
— 等待busy_timeout后超时
Error: database is locked

sqlite> PRAGMA busy_timeout;
busy_timeout
————
10000

使用busy_handler（Python示例）：
#!/usr/bin/env python3
# busy_handler.py
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

import sqlite3
import time

def busy_handler(retries):
if retries < 10: wait_time = min(100 * (2 ** retries), 5000) print(f"Lock busy, retry {retries}, wait {wait_time}ms") time.sleep(wait_time / 1000) return True return False conn = sqlite3.connect('/sqlite/fgdata/fgedudb.db') conn.set_busy_handler(busy_handler) cursor = conn.cursor() cursor.execute('SELECT * FROM fgedu_users') print(cursor.fetchall()) conn.close() 执行结果： $ python3 busy_handler.py Lock busy, retry 0, wait 100ms Lock busy, retry 1, wait 200ms Lock busy, retry 2, wait 400ms [(1, 'fgedu01', ...)]

DB=”/sqlite/fgdata/fgedudb.db”

echo “=== SQLite Concurrent Test ===”
echo “Start: $(date)”

# 启动多个读进程
for i in {1..5}; do
sqlite3 $DB “PRAGMA busy_timeout=5000;
SELECT COUNT(*) FROM fgedu_users;” &
done

# 启动写进程
for i in {1..3}; do
sqlite3 $DB “PRAGMA busy_timeout=5000;
BEGIN IMMEDIATE;
INSERT INTO fgedu_users (username, email)
VALUES (‘test_$i’, ‘test_$i@fgedu.net.cn’);
COMMIT;” &
done

wait
echo “End: $(date)”

执行测试：
$ ./concurrent_test.sh
=== SQLite Concurrent Test ===
Start: Mon Apr 8 10:30:00 CST 2024
10004
10004
10004
10004
10004
End: Mon Apr 8 10:30:05 CST 2024

验证结果：
sqlite> SELECT COUNT(*) FROM fgedu_users;
COUNT(*)
——–
10007

import sqlite3
from queue import Queue
import threading

class SQLiteConnectionPool:
def __init__(self, db_path, pool_size=10):
self.db_path = db_path
self.pool_size = pool_size
self.pool = Queue(maxsize=pool_size)
self.lock = threading.Lock()

for _ in range(pool_size):
conn = sqlite3.connect(db_path, check_same_thread=False)
conn.execute(‘PRAGMA journal_mode = WAL’)
conn.execute(‘PRAGMA busy_timeout = 5000’)
self.pool.put(conn)

def get_connection(self):
return self.pool.get()

def return_connection(self, conn):
self.pool.put(conn)

def close_all(self):
while not self.pool.empty():
conn = self.pool.get()
conn.close()

# 使用示例
pool = SQLiteConnectionPool(‘/sqlite/fgdata/fgedudb.db’, pool_size=5)

def query_users():
conn = pool.get_connection()
try:
cursor = conn.cursor()
cursor.execute(‘SELECT COUNT(*) FROM fgedu_users’)
result = cursor.fetchone()
print(f”Count: {result[0]}”)
finally:
pool.return_connection(conn)

# 多线程测试
threads = []
for i in range(10):
t = threading.Thread(target=query_users)
threads.append(t)
t.start()

for t in threads:
t.join()

pool.close_all()

DB=”/sqlite/fgdata/fgedudb.db”
LOG_FILE=”/sqlite/logs/blocking.log”

echo “=== SQLite Blocking Monitor ===” | tee -a $LOG_FILE
echo “Time: $(date)” | tee -a $LOG_FILE

# 检查WAL文件大小
WAL_SIZE=$(ls -lh ${DB}-wal 2>/dev/null | awk ‘{print $5}’)
echo “WAL Size: $WAL_SIZE” | tee -a $LOG_FILE

# 检查锁状态
echo “Lock Status:” | tee -a $LOG_FILE
sqlite3 $DB “PRAGMA lock_status;” | tee -a $LOG_FILE

# 检查checkpoint状态
echo “Checkpoint Status:” | tee -a $LOG_FILE
sqlite3 $DB “PRAGMA wal_checkpoint(PASSIVE);” | tee -a $LOG_FILE

# 检查数据库完整性
echo “Integrity Check:” | tee -a $LOG_FILE
sqlite3 $DB “PRAGMA integrity_check;” | tee -a $LOG_FILE

echo “Monitor completed” | tee -a $LOG_FILE

执行监控：
$ ./blocking_monitor.sh
=== SQLite Blocking Monitor ===
Time: Mon Apr 8 10:35:00 CST 2024
WAL Size: 32K
Lock Status:
main reserved 0
temp closed 0
Checkpoint Status:
0 5 5
Integrity Check:
ok
Monitor completed

1. 检查长时间运行的事务：
sqlite> SELECT * FROM pragma_lock_status;

2. 检查WAL状态：
sqlite> PRAGMA wal_checkpoint;
busy log checkpointed
—- — ————
0 100 50

3. 手动checkpoint：
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
busy log checkpointed
—- — ————
0 0 0

4. 检查数据库文件锁：
$ fuser /sqlite/fgdata/fgedudb.db
/sqlite/fgdata/fgedudb.db: 12345 12346

$ lsof /sqlite/fgdata/fgedudb.db
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
sqlite3 12345 sqlite 11u REG 253,0 524288 123 fgedudb.db
sqlite3 12346 sqlite 11u REG 253,0 524288 123 fgedudb.db

5. 终止阻塞进程：
$ kill -9 12345

6. 验证数据库状态：
sqlite> PRAGMA integrity_check;
integrity_check
—————
ok

排查步骤：

1. 检查数据库配置：
sqlite> PRAGMA journal_mode;
journal_mode
————
delete

sqlite> PRAGMA busy_timeout;
busy_timeout
————
0

2. 检查当前连接：
$ lsof /sqlite/fgdata/fgedudb.db
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
python3 12345 sqlite 11u REG 253,0 524288 123 fgedudb.db
python3 12346 sqlite 11u REG 253,0 524288 123 fgedudb.db
python3 12347 sqlite 11u REG 253,0 524288 123 fgedudb.db

3. 启用WAL模式：
sqlite> PRAGMA journal_mode = WAL;
journal_mode
————
wal

4. 设置超时：
sqlite> PRAGMA busy_timeout = 5000;

5. 验证修复：
$ python3 app.py
Operation completed successfully

根本原因：
├── 使用回滚日志模式
├── 未设置busy_timeout
├── 多进程并发写入
└── 锁竞争导致超时

解决方案：
├── 启用WAL模式
├── 设置busy_timeout
├── 使用IMMEDIATE事务
└── 优化事务设计

初始配置：
├── journal_mode: delete
├── busy_timeout: 0
├── 连接数: 无限制
└── 性能: TPS 50

优化步骤：

1. 启用WAL模式：
sqlite> PRAGMA journal_mode = WAL;

2. 设置超时：
sqlite> PRAGMA busy_timeout = 10000;

3. 优化同步模式：
sqlite> PRAGMA synchronous = NORMAL;

4. 配置连接池：
pool_size = 20
max_overflow = 10

5. 优化checkpoint：
sqlite> PRAGMA wal_autocheckpoint = 500;

优化后配置：
├── journal_mode: wal
├── busy_timeout: 10000
├── synchronous: NORMAL
├── 连接池: 20+10
└── 性能: TPS 500

性能对比：
┌─────────────────┬──────────┬──────────┐
│ 指标 │ 优化前 │ 优化后 │
├─────────────────┼──────────┼──────────┤
│ TPS │ 50 │ 500 │
│ 平均响应时间 │ 200ms │ 20ms │
│ 锁等待次数 │ 高 │ 低 │
│ 错误率 │ 5% │ 0.01% │
└─────────────────┴──────────┴──────────┘

问题分析：
1. 批量导入使用单个大事务
2. 事务持有EXCLUSIVE锁时间过长
3. 其他操作无法获取锁

解决方案：

优化前：
BEGIN TRANSACTION;
— 导入100万条数据
INSERT INTO fgedu_data VALUES (…); — 100万条
COMMIT;

优化后：
— 分批处理，每批1000条
BEGIN IMMEDIATE TRANSACTION;
INSERT INTO fgedu_data VALUES (…); — 1000条
COMMIT;

— 使用脚本分批处理
#!/bin/bash
# batch_import.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

DB=”/sqlite/fgdata/fgedudb.db”
BATCH_SIZE=1000
TOTAL=1000000

for ((i=0; i<$TOTAL; i+=$BATCH_SIZE)); do sqlite3 $DB <

Part05-风哥经验总结与分享

5.1 sqlite数据库锁管理最佳实践

基于多年的实践经验，以下是sqlite数据库锁管理的最佳实践总结。学习交流加群风哥QQ113257174

5.1.1 锁管理最佳实践总结

5.2 sqlite数据库并发优化技巧

以下是sqlite数据库并发优化的技巧总结。from sqlite视频:www.itpux.com

5.2.1 并发优化技巧汇总

5.3 sqlite数据库阻塞处理总结

综合各种因素，以下是sqlite数据库阻塞处理的总结。更多视频教程www.fgedu.net.cn

5.3.1 阻塞处理总结