一、SQLite锁竞争问题的本质

SQLite作为一个轻量级的嵌入式数据库，最大的特点就是简单易用。但正是这种"简单"的设计，也让它在高并发场景下容易遇到锁竞争问题。想象一下，当多个线程或进程同时操作同一个数据库文件时，就像几个人抢着通过一扇旋转门，难免会发生拥堵。

SQLite默认使用文件锁来控制并发访问，主要包含以下几种锁状态：

  • UNLOCKED：无锁状态
  • SHARED：共享读锁
  • RESERVED：预留写锁
  • PENDING：等待独占锁
  • EXCLUSIVE：独占写锁

锁升级过程是线性的：UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE。这个过程中任何一个环节出现阻塞，就会导致我们常见的"database is locked"错误。

二、典型锁竞争场景分析

1. 长时间运行的写事务

# 技术栈：Python + sqlite3
import sqlite3
import time

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

try:
    # 开启事务（SQLite默认自动开启）
    cursor.execute("BEGIN TRANSACTION")
    
    # 执行耗时更新操作
    cursor.execute("UPDATE users SET status = 0 WHERE last_login < date('now','-30 day')")
    
    # 模拟耗时操作（实际可能是复杂计算）
    time.sleep(30)  # ← 这里会导致锁保持30秒！
    
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"事务失败: {e}")
finally:
    conn.close()

这个例子中，sleep模拟了耗时操作，在此期间其他连接的所有写操作都会被阻塞。

2. 读写操作交错

// 技术栈：Java + JDBC
Connection conn1 = DriverManager.getConnection("jdbc:sqlite:test.db");
Connection conn2 = DriverManager.getConnection("jdbc:sqlite:test.db");

// 连接1开始读操作
ResultSet rs = conn1.createStatement().executeQuery("SELECT * FROM products");

while(rs.next()) {
    // 连接2尝试写操作（会被阻塞）
    conn2.createStatement().executeUpdate(
        "UPDATE inventory SET stock = stock-1 WHERE product_id = " + rs.getInt("id"));
    
    // 如果数据量大，这里会形成恶性循环
}

这种场景下，读操作没有及时关闭，导致写操作排队等待。

三、实战解决方案

1. 事务优化技巧

// 技术栈：C# + System.Data.SQLite
using (var conn = new SQLiteConnection("Data Source=test.db"))
{
    conn.Open();
    
    // 设置超时时间为5秒
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "PRAGMA busy_timeout = 5000"; 
        cmd.ExecuteNonQuery();
    }
    
    // 使用立即事务模式
    using (var trans = conn.BeginTransaction(IsolationLevel.Serializable))
    {
        try {
            // 快速执行批量更新
            for (int i = 0; i < 1000; i++) {
                var updateCmd = conn.CreateCommand();
                updateCmd.CommandText = "INSERT INTO logs VALUES(@time, @msg)";
                updateCmd.Parameters.AddWithValue("@time", DateTime.Now);
                updateCmd.Parameters.AddWithValue("@msg", $"Log entry {i}");
                updateCmd.ExecuteNonQuery();
            }
            trans.Commit();
        } catch {
            trans.Rollback();
            throw;
        }
    }
}

关键优化点：

  • 设置busy_timeout避免立即失败
  • 使用立即事务模式减少锁等待
  • 批量操作放在单个事务中

2. WAL模式实战

# 技术栈：Python + aiosqlite
import asyncio
import aiosqlite

async def concurrent_operations():
    # 启用WAL模式
    async with aiosqlite.connect('test.db') as db:
        await db.execute("PRAGMA journal_mode=WAL")
        
        # 并行执行读写
        tasks = [
            write_data(db, i) for i in range(5)
        ] + [
            read_data(db) for _ in range(3)
        ]
        
        await asyncio.gather(*tasks)

async def write_data(db, num):
    async with db.execute("BEGIN CONCURRENT"):
        await db.execute("INSERT INTO test VALUES (?, ?)", (num, f"data{num}"))
        await db.commit()

async def read_data(db):
    async with db.execute("SELECT * FROM test") as cursor:
        return await cursor.fetchall()

WAL模式优势：

  • 读操作不会阻塞写操作
  • 写操作之间可以并发执行
  • 支持真正的MVCC

四、高级调优策略

1. 连接池配置

// 技术栈：Java + HikariCP
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:sqlite:test.db");
config.setMaximumPoolSize(5);  // 控制连接数
config.setConnectionTimeout(3000);  // 连接等待超时
config.addDataSourceProperty("journal_mode", "WAL");
config.addDataSourceProperty("synchronous", "NORMAL");

HikariDataSource ds = new HikariDataSource(config);

// 使用示例
try (Connection conn = ds.getConnection()) {
    // 执行查询...
}

连接池最佳实践：

  • 根据CPU核心数设置连接数
  • 统一配置PRAGMA参数
  • 设置合理的超时时间

2. 分库分表方案

# 技术栈：Python + SQLite分表示例
import sqlite3
from hashlib import md5

def get_shard_conn(user_id):
    # 按用户ID分片
    shard_id = int(md5(str(user_id).encode()).hexdigest()[:2], 16) % 8
    return sqlite3.connect(f'userdb_{shard_id}.db')

# 使用分片连接写入数据
def save_user_data(user_id, data):
    conn = get_shard_conn(user_id)
    try:
        conn.execute("INSERT INTO users VALUES (?, ?)", (user_id, data))
        conn.commit()
    finally:
        conn.close()

分片策略建议：

  • 按业务键哈希分片
  • 每个分片保持较小数据量
  • 考虑跨分片查询需求

五、避坑指南

  1. 避免热点更新：不要频繁更新同一条记录，可以考虑内存缓存+批量更新
  2. 索引优化：确保查询使用索引，减少锁持有时间
  3. 监控锁等待：通过PRAGMA lock_status监控锁状态
  4. 合理设置缓存：调整cache_size(建议2000-10000)减少磁盘IO
  5. 定期维护：执行VACUUM和ANALYZE保持数据库性能
// 技术栈：Dart + sqflite
final db = await openDatabase('test.db');

// 监控锁状态的实用方法
void monitorLocks() async {
  final status = await db.rawQuery('PRAGMA lock_status');
  print('当前锁状态: $status');
  
  // 检查等待锁的连接
  final waiting = await db.rawQuery(
    'SELECT * FROM sqlite_master WHERE type = "table" AND name = "sqlite_stat1"');
  if (waiting.isEmpty) {
    print('无锁等待');
  }
}

六、总结

SQLite的锁竞争问题本质上是设计权衡的结果。通过合理配置WAL模式、优化事务粒度、使用连接池等技术，完全可以应对中等规模的并发场景。对于更高并发的需求，建议考虑分库分表或迁移到客户端-服务器架构的数据库。记住，没有银弹，只有最适合当前业务场景的解决方案。