Lazarus中多用户同时写入SQLite数据库时锁冲突问题求助

在Lazarus环境下操作SQLite时，多用户同时写入触发数据库锁定冲突是常见问题，以下是几种实用的解决方法：

1. 实现自动重试机制 #

SQLite遇到锁冲突时会抛出异常，我们可以捕获异常并循环重试写入操作，直到成功或达到预设的最大重试次数。修改你的示例代码如下：

var
  RetryCount: Integer;
  MaxRetries: Integer = 5; // 最多重试5次
begin
  MaxRetries := 5;
  RetryCount := 0;
  SQLite3Connection1.Connected := true;
  try
    sql := 'Insert into data (a, b, c) values (:a, :b, :c)';
    SQLMaterial.SQL.Text := sql;
    SQLMaterial.ParamByName('a').AsString := text1;
    SQLMaterial.ParamByName('b').AsString := text2;
    SQLMaterial.ParamByName('c').AsString := text3;

    while RetryCount < MaxRetries do
    begin
      try
        SQLMaterial.ExecSQL;
        Break; // 成功写入则退出循环
      except
        on E: Exception do
        begin
          Inc(RetryCount);
          if RetryCount >= MaxRetries then
            raise; // 达到重试上限，抛出原异常
          Sleep(100); // 等待100毫秒后重试
        end;
      end;
    end;
  finally
    SQLMaterial.Close;
    SQLite3Connection1.Connected := false;
  end;
end;

2. 设置SQLite的忙等待超时 #

通过设置BusyTimeout参数，让SQLite在遇到数据库锁定时自动等待指定时长，而不是立即返回错误。只需在连接数据库前增加一行配置：

SQLite3Connection1.BusyTimeout := 5000; // 设置等待5秒
SQLite3Connection1.Connected := true;

这个参数会让SQLite内部在遇到锁时自动重试，无需手动捕获异常（但极端情况下仍可能需要结合重试机制）。

3. 优化连接与事务管理 #

• 复用数据库连接：避免每次写入都打开/关闭连接，长连接可以减少锁竞争的概率，同时提升性能。
• 使用显式事务：将写入操作包裹在事务中，减少锁的持有时间，降低冲突概率。示例：

begin
  SQLite3Connection1.BusyTimeout := 5000;
  SQLite3Connection1.Connected := true;
  try
    SQLMaterial.BeginTransaction; // 开启事务
    try
      sql := 'Insert into data (a, b, c) values (:a, :b, :c)';
      SQLMaterial.SQL.Text := sql;
      SQLMaterial.ParamByName('a').AsString := text1;
      SQLMaterial.ParamByName('b').AsString := text2;
      SQLMaterial.ParamByName('c').AsString := text3;
      SQLMaterial.ExecSQL;
      
      SQLMaterial.Commit; // 提交事务
    except
      SQLMaterial.Rollback; // 异常时回滚
      raise;
    end;
  finally
    SQLMaterial.Close;
    SQLite3Connection1.Connected := false;
  end;
end;

4. 缩短锁持有时间 #

确保所有写入操作完成后立即提交事务、释放资源，不要在事务执行过程中插入UI交互、文件读写等耗时操作，尽可能缩短锁的持有时间，减少冲突可能。

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