sqlite数据库ACID特性：

A – Atomicity（原子性）
├── 事务中的操作要么全部成功，要么全部失败
├── 不会出现部分成功的情况
├── 通过回滚日志实现
└── 崩溃恢复时自动回滚未完成事务

C – Consistency（一致性）
├── 事务执行前后数据库保持一致状态
├── 约束、触发器保证数据完整性
├── 外键约束维护参照完整性
└── CHECK约束保证数据有效性

I – Isolation（隔离性）
├── 并发事务之间相互隔离
├── sqlite支持SERIALIZABLE隔离级别
├── 读操作不会阻塞写操作（WAL模式）
└── 写操作不会阻塞读操作（WAL模式）

D – Durability（持久性）
├── 事务提交后数据永久保存
├── 通过WAL日志保证持久性
├── 支持同步模式配置
└── 崩溃后可通过日志恢复数据

sqlite数据库事务类型：

1. 显式事务
BEGIN TRANSACTION;
— SQL语句
COMMIT; — 或 ROLLBACK;

2. 隐式事务
— 单条SQL语句自动作为事务
INSERT INTO fgedu_users (name) VALUES (‘test’);

3. 嵌套事务（SAVEPOINT）
BEGIN TRANSACTION;
— 操作1
SAVEPOINT sp1;
— 操作2
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

事务控制语句：
├── BEGIN [DEFERRED|IMMEDIATE|EXCLUSIVE] TRANSACTION
├── COMMIT [TRANSACTION]
├── ROLLBACK [TRANSACTION] [TO SAVEPOINT name]
├── SAVEPOINT name
└── RELEASE SAVEPOINT name

事务类型说明：
├── DEFERRED：延迟获取锁（默认）
├── IMMEDIATE：立即获取保留锁
└── EXCLUSIVE：立即获取独占锁

sqlite数据库WAL模式工作原理：

传统回滚日志模式：
┌─────────────────────────────────────────┐
│ 1. 将原始数据写入回滚日志 │
│ 2. 修改数据库文件 │
│ 3. 删除回滚日志 │
└─────────────────────────────────────────┘
问题：写操作阻塞所有读操作

WAL模式：
┌─────────────────────────────────────────┐
│ 1. 将修改写入WAL文件 │
│ 2. 读操作可同时读取数据库和WAL │
│ 3. 定期将WAL合并到数据库（checkpoint） │
└─────────────────────────────────────────┘
优势：读写不互相阻塞

WAL文件结构：
├── 文件名：数据库文件名-wal
├── 内容：修改记录序列
├── 大小：自动增长
└── 合并：checkpoint操作

共享内存文件（-shm）：
├── 文件名：数据库文件名-shm
├── 用途：协调多进程访问
├── 大小：32KB固定
└── 生命周期：随WAL文件

sqlite数据库WAL模式优势：

1. 并发性能提升
├── 读操作不阻塞写操作
├── 写操作不阻塞读操作
└── 多个读操作可并发执行

2. 性能优化
├── 减少磁盘IO
├── 顺序写入WAL
└── 批量checkpoint

3. 数据安全
├── 崩溃恢复更可靠
├── WAL文件可恢复数据
└── 支持热备份

WAL模式限制：

1. 网络文件系统
├── 不支持NFS等网络文件系统
├── 需要本地文件系统
└── 锁机制依赖本地文件

2. 多进程访问
├── 需要共享内存支持
├── 进程间需要协调
└── -shm文件管理

3. 文件数量
├── 增加2个额外文件
├── 需要管理WAL文件
└── 需要定期checkpoint

sqlite数据库锁类型（5级锁）：

1. UNLOCKED（未锁定）
└── 无任何锁，初始状态

2. SHARED（共享锁）
├── 读操作获取
├── 多个进程可同时持有
└── 阻止写入

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

4. PENDING（待定锁）
├── 等待读操作完成
├── 阻止新的共享锁
└── 准备进入独占锁

5. EXCLUSIVE（独占锁）
├── 写操作获取
├── 完全独占数据库
└── 阻止所有其他访问

锁升级路径：
UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE

WAL模式下的锁：
├── 读操作：获取SHARED锁
├── 写操作：获取SHARED锁后写入WAL
├── Checkpoint：需要EXCLUSIVE锁
└── 读写可并发进行

sqlite数据库隔离级别：

sqlite默认支持SERIALIZABLE隔离级别：

隔离级别对比：
┌──────────────────┬─────────────────────────┐
│ 隔离级别 │ 说明 │
├──────────────────┼─────────────────────────┤
│ READ UNCOMMITTED │ 不支持 │
│ READ COMMITTED │ 不支持 │
│ REPEATABLE READ │ 不支持 │
│ SERIALIZABLE │ 默认支持 │
└──────────────────┴─────────────────────────┘

SERIALIZABLE特性：
├── 完全隔离并发事务
├── 读操作看到一致快照
├── 写操作串行执行
└── 防止所有并发问题

并发问题防护：
├── 脏读：不会发生
├── 不可重复读：不会发生
├── 幻读：不会发生
└── 丢失更新：不会发生

sqlite数据库事务设计原则：

1. 事务边界清晰
├── 明确事务开始和结束
├── 避免隐式事务
└── 及时提交或回滚

2. 事务粒度适当
├── 批量操作使用事务
├── 避免过长事务
└── 长事务拆分处理

3. 错误处理完善
├── 捕获异常并回滚
├── 记录事务日志
└── 提供重试机制

4. 锁竞争最小化
├── 减少锁持有时间
├── 合理选择事务类型
└── 避免热点数据竞争

事务大小建议：
├── 小事务：单条或少量记录
├── 中事务：100-1000条记录
├── 大事务：超过1000条需拆分
└── 超大事务：分批处理

sqlite数据库WAL模式配置规划：

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

关键配置参数：

1. wal_autocheckpoint
├── 默认：1000页
├── 建议：根据写入频率调整
└── 单位：页面数

2. synchronous
├── NORMAL：推荐生产使用
├── FULL：最高安全性
└── OFF：最高性能（不推荐）

3. wal_checkpoint
├── PASSIVE：不阻塞
├── RESTART：重启WAL
└── TRUNCATE：截断WAL

配置建议：
┌─────────────────┬──────────────────────────┐
│ 场景 │ 配置建议 │
├─────────────────┼──────────────────────────┤
│ 高写入频率 │ wal_autocheckpoint=500 │
│ 低写入频率 │ wal_autocheckpoint=2000 │
│ 数据安全优先 │ synchronous=FULL │
│ 性能优先 │ synchronous=NORMAL │
└─────────────────┴──────────────────────────┘

sqlite数据库并发场景规划：

1. 读多写少场景
├── 启用WAL模式
├── 配置连接池
└── 优化查询性能

2. 写多读少场景
├── 批量写入事务
├── 定期checkpoint
└── 调整WAL大小

3. 读写均衡场景
├── WAL模式
├── 合理设置checkpoint
└── 监控锁等待

4. 高并发场景
├── 连接池管理
├── 事务优化
└── 考虑分库分表

并发连接数建议：
├── 单进程应用：1个连接
├── 多线程应用：线程池+连接池
├── 多进程应用：每进程1-2个连接
└── Web应用：连接池（10-50个）

创建测试表：
sqlite> CREATE TABLE fgedu_accounts (
…> id INTEGER PRIMARY KEY,
…> account_no TEXT UNIQUE NOT NULL,
…> balance REAL NOT NULL DEFAULT 0,
…> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

sqlite> INSERT INTO fgedu_accounts (account_no, balance) VALUES
…> (‘ACC001’, 10000.00),
…> (‘ACC002’, 5000.00);

基本事务示例：
sqlite> BEGIN TRANSACTION;
sqlite> UPDATE fgedu_accounts SET balance = balance – 1000 WHERE account_no = ‘ACC001’;
sqlite> UPDATE fgedu_accounts SET balance = balance + 1000 WHERE account_no = ‘ACC002’;
sqlite> COMMIT;

验证结果：
sqlite> SELECT * FROM fgedu_accounts;
id account_no balance updated_at
— ———- ——- ——————-
1 ACC001 9000.0 2024-04-08 10:00:00
2 ACC002 6000.0 2024-04-08 10:00:00

事务回滚示例：
sqlite> BEGIN TRANSACTION;
sqlite> UPDATE fgedu_accounts SET balance = 0 WHERE account_no = ‘ACC001’;
sqlite> SELECT balance FROM fgedu_accounts WHERE account_no = ‘ACC001’;
balance
——-
0.0
sqlite> ROLLBACK;

sqlite> SELECT balance FROM fgedu_accounts WHERE account_no = ‘ACC001’;
balance
——-
9000.0

SAVEPOINT嵌套事务示例：
sqlite> BEGIN TRANSACTION;

sqlite> INSERT INTO fgedu_accounts (account_no, balance) VALUES (‘ACC003’, 3000.00);

sqlite> SAVEPOINT sp1;

sqlite> UPDATE fgedu_accounts SET balance = balance + 500 WHERE account_no = ‘ACC003’;

sqlite> SAVEPOINT sp2;

sqlite> UPDATE fgedu_accounts SET balance = balance – 200 WHERE account_no = ‘ACC003’;

sqlite> SELECT balance FROM fgedu_accounts WHERE account_no = ‘ACC003’;
balance
——-
3300.0

sqlite> ROLLBACK TO SAVEPOINT sp2;

sqlite> SELECT balance FROM fgedu_accounts WHERE account_no = ‘ACC003’;
balance
——-
3500.0

sqlite> RELEASE SAVEPOINT sp1;

sqlite> COMMIT;

sqlite> SELECT * FROM fgedu_accounts WHERE account_no = ‘ACC003’;
id account_no balance updated_at
— ———- ——- ——————-
3 ACC003 3500.0 2024-04-08 10:05:00

DEFERRED事务（默认）：
sqlite> BEGIN DEFERRED TRANSACTION;
— 延迟获取锁，第一次读写时获取
sqlite> SELECT * FROM fgedu_accounts;
sqlite> UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 1;
sqlite> COMMIT;

IMMEDIATE事务：
sqlite> BEGIN IMMEDIATE TRANSACTION;
— 立即获取RESERVED锁，阻止其他写入
sqlite> UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 1;
sqlite> COMMIT;

EXCLUSIVE事务：
sqlite> BEGIN EXCLUSIVE TRANSACTION;
— 立即获取EXCLUSIVE锁，完全独占
sqlite> UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 1;
sqlite> COMMIT;

查看当前锁状态：
sqlite> PRAGMA lock_status;
main reserved 0
temp closed 0

查看当前日志模式：
sqlite> PRAGMA journal_mode;
journal_mode
————
delete

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

验证WAL文件：
$ ls -la /sqlite/fgdata/
total 2048
-rw-r–r– 1 sqlite sqlite 524288 Apr 8 10:00 fgedudb.db
-rw-r–r– 1 sqlite sqlite 32768 Apr 8 10:00 fgedudb.db-wal
-rw-r–r– 1 sqlite sqlite 32768 Apr 8 10:00 fgedudb.db-shm

配置自动checkpoint：
sqlite> PRAGMA wal_autocheckpoint = 500;
sqlite> PRAGMA wal_autocheckpoint;
wal_autocheckpoint
——————
500

配置同步模式：
sqlite> PRAGMA synchronous = NORMAL;
sqlite> PRAGMA synchronous;
synchronous
———–
1

查看WAL状态：
sqlite> PRAGMA wal_checkpoint;
busy log checkpointed
—- — ————
0 10 10

手动checkpoint：
sqlite> PRAGMA wal_checkpoint;
busy log checkpointed
—- — ————
0 5 5

PASSIVE模式（不阻塞）：
sqlite> PRAGMA wal_checkpoint(PASSIVE);
busy log checkpointed
—- — ————
0 0 0

RESTART模式（重启WAL）：
sqlite> PRAGMA wal_checkpoint(RESTART);
busy log checkpointed
—- — ————
0 0 0

TRUNCATE模式（截断WAL文件）：
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
busy log checkpointed
—- — ————
0 0 0

查看WAL文件大小：
$ ls -lh /sqlite/fgdata/fgedudb.db-wal
-rw-r–r– 1 sqlite sqlite 0 Apr 8 10:10 /sqlite/fgdata/fgedudb.db-wal

监控WAL状态脚本：
#!/bin/bash
# wal_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

DB_PATH=”/sqlite/fgdata/fgedudb.db”
WAL_FILE=”${DB_PATH}-wal”

echo “=== WAL Monitor ===”
echo “Time: $(date)”
echo “WAL Size: $(ls -lh $WAL_FILE | awk ‘{print $5}’)”
sqlite3 $DB_PATH “PRAGMA wal_checkpoint;”

创建并发测试脚本：
#!/bin/bash
# concurrent_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

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

echo “=== Concurrent Read Test ===”
for i in {1..5}; do
sqlite3 $DB “SELECT COUNT(*) FROM fgedu_accounts;” &
done
wait
echo “Read test completed”

echo “=== Concurrent Write Test ===”
for i in {1..3}; do
sqlite3 $DB “BEGIN IMMEDIATE;
UPDATE fgedu_accounts SET balance = balance + 1 WHERE id = 1;
COMMIT;” &
done
wait
echo “Write test completed”

执行测试：
$ ./concurrent_test.sh
=== Concurrent Read Test ===
3
3
3
3
3
Read test completed
=== Concurrent Write Test ===
Write test completed

验证结果：
sqlite> SELECT * FROM fgedu_accounts WHERE id = 1;
id account_no balance updated_at
— ———- ——– ——————-
1 ACC001 9003.0 2024-04-08 10:15:00

设置锁超时时间：
sqlite> PRAGMA busy_timeout = 5000;
sqlite> PRAGMA busy_timeout;
busy_timeout
————
5000

测试锁超时：
— 终端1：
sqlite> BEGIN EXCLUSIVE TRANSACTION;
— 持有独占锁

— 终端2：
sqlite> BEGIN TRANSACTION;
sqlite> SELECT * FROM fgedu_accounts;
— 等待5秒后超时
Error: database is locked

配置建议：
├── 短事务：1000-3000毫秒
├── 一般应用：5000毫秒
├── 长事务：10000-30000毫秒
└── 批处理：60000毫秒

查看当前配置：
sqlite> PRAGMA busy_timeout;
busy_timeout
————
5000

创建转账存储过程模拟：
sqlite> CREATE TABLE fgedu_transfers (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> from_account TEXT NOT NULL,
…> to_account TEXT NOT NULL,
…> amount REAL NOT NULL,
…> status TEXT DEFAULT ‘pending’,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

转账事务脚本：
#!/bin/bash
# transfer.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

DB=”/sqlite/fgdata/fgedudb.db”
FROM_ACC=”ACC001″
TO_ACC=”ACC002″
AMOUNT=500.00

sqlite3 $DB < SELECT * FROM fgedu_accounts;
id account_no balance updated_at
— ———- ——– ——————-
1 ACC001 8503.0 2024-04-08 10:20:00
2 ACC002 6500.0 2024-04-08 10:20:00
3 ACC003 3500.0 2024-04-08 10:05:00

sqlite> SELECT * FROM fgedu_transfers;
id from_account to_account amount status created_at
— ———— ———- —— ——— ——————-
1 ACC001 ACC002 500.0 completed 2024-04-08 10:20:00

场景：高写入频率应用

初始配置检查：
sqlite> PRAGMA journal_mode;
journal_mode
————
delete

sqlite> PRAGMA wal_autocheckpoint;
wal_autocheckpoint
——————
1000

优化配置：
sqlite> PRAGMA journal_mode = WAL;
journal_mode
————
wal

sqlite> PRAGMA wal_autocheckpoint = 500;
sqlite> PRAGMA synchronous = NORMAL;

性能测试脚本：
#!/bin/bash
# wal_perf_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

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

echo “=== WAL Performance Test ===”
echo “Start: $(date)”

sqlite3 $DB < PRAGMA wal_checkpoint(TRUNCATE);
busy log checkpointed
—- — ————
0 0 0

Python多线程并发示例：
#!/usr/bin/env python3
# concurrent_python.py
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

import sqlite3
import threading
import time

DB_PATH = ‘/sqlite/fgdata/fgedudb.db’

def reader_thread(thread_id):
conn = sqlite3.connect(DB_PATH)
conn.execute(‘PRAGMA journal_mode = WAL’)
cursor = conn.cursor()
for i in range(10):
cursor.execute(‘SELECT COUNT(*) FROM fgedu_accounts’)
count = cursor.fetchone()[0]
print(f’Reader {thread_id}: count = {count}’)
time.sleep(0.1)
conn.close()

def writer_thread(thread_id):
conn = sqlite3.connect(DB_PATH)
conn.execute(‘PRAGMA journal_mode = WAL’)
cursor = conn.cursor()
for i in range(5):
cursor.execute(”’
BEGIN IMMEDIATE;
UPDATE fgedu_accounts SET balance = balance + 1 WHERE id = 1;
COMMIT;
”’)
print(f’Writer {thread_id}: updated’)
time.sleep(0.2)
conn.close()

if __name__ == ‘__main__’:
threads = []

# 创建读线程
for i in range(3):
t = threading.Thread(target=reader_thread, args=(i,))
threads.append(t)

# 创建写线程
for i in range(2):
t = threading.Thread(target=writer_thread, args=(i,))
threads.append(t)

# 启动所有线程
for t in threads:
t.start()

# 等待所有线程完成
for t in threads:
t.join()

print(‘All threads completed’)

执行结果：
$ python3 concurrent_python.py
Reader 0: count = 10003
Reader 1: count = 10003
Reader 2: count = 10003
Writer 0: updated
Writer 1: updated
…
All threads completed

sqlite数据库事务最佳实践：

1. 事务边界管理
├── 明确事务开始和结束
├── 避免隐式事务
├── 及时提交或回滚
└── 使用try-catch-finally

2. 事务大小控制
├── 批量操作使用事务
├── 单事务不超过1000条
├── 长事务拆分处理
└── 监控事务执行时间

3. 锁竞争优化
├── 使用IMMEDIATE事务
├── 减少锁持有时间
├── 合理设置超时
└── 避免热点竞争

4. 错误处理
├── 捕获所有异常
├── 自动回滚失败事务
├── 记录错误日志
└── 提供重试机制

5. 性能优化
├── 批量提交
├── 使用SAVEPOINT
├── 预编译语句
└── 连接池复用

sqlite数据库WAL优化技巧：

1. Checkpoint策略
├── 高写入：autocheckpoint=500
├── 低写入：autocheckpoint=2000
├── 定期手动checkpoint
└── 监控WAL文件大小

2. 同步模式选择
├── FULL：最高安全
├── NORMAL：推荐生产
└── OFF：仅测试用

3. WAL文件管理
├── 监控WAL大小
├── 定期TRUNCATE
├── 备份包含WAL
└── 清理旧WAL文件

4. 性能监控
├── 监控checkpoint频率
├── 监控锁等待时间
├── 监控事务响应时间
└── 监控并发连接数

5. 故障恢复
├── WAL自动恢复
├── 备份WAL文件
├── 定期完整性检查
└── 制定恢复计划

sqlite数据库并发控制总结：

事务管理：
├── 使用显式事务
├── 合理选择事务类型
├── 控制事务大小
└── 完善错误处理

WAL模式：
├── 生产环境推荐启用
├── 合理配置checkpoint
├── 监控WAL文件
└── 定期维护

锁管理：
├── 理解锁类型和升级
├── 设置合理超时
├── 减少锁竞争
└── 监控锁状态

并发优化：
├── 使用连接池
├── 批量操作
├── 读写分离
└── 监控并发指标

最佳配置：
├── journal_mode = WAL
├── synchronous = NORMAL
├── busy_timeout = 5000
└── wal_autocheckpoint = 500-1000