SQLite提交期间数据库文件被莫名其妙地锁定 [英] Database file is inexplicably locked during SQLite commit
问题描述
我正在对SQLite数据库执行大量的插入操作.我只使用一个线程.我分批写入以提高性能，并在崩溃时有一点安全性.基本上，我将一堆数据缓存在内存中，然后在我认为合适时，遍历所有这些数据并执行INSERTS.此代码如下所示:
I'm performing a large number of INSERTS to a SQLite database. I'm using just one thread. I batch the writes to improve performance and have a bit of security in case of a crash. Basically I cache up a bunch of data in memory and then when I deem appropriate, I loop over all of that data and perform the INSERTS. The code for this is shown below:
public void Commit()
{
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
conn.Open();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
using (SQLiteCommand command = conn.CreateCommand())
{
command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";
command.Parameters.Add(this.col1Param);
command.Parameters.Add(this.col2Param);
foreach (Data o in this.dataTemp)
{
this.col1Param.Value = o.Col1Prop;
this. col2Param.Value = o.Col2Prop;
command.ExecuteNonQuery();
}
}
this.TryHandleCommit(trans);
}
conn.Close();
}
}
我现在使用以下头使事情最终起作用:
I now employ the following gimmick to get the thing to eventually work:
private void TryHandleCommit(SQLiteTransaction trans)
{
try
{
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine("Trying again...");
this.TryHandleCommit(trans);
}
}
我这样创建数据库:
public DataBase(String path)
{
//build connection string
SQLiteConnectionStringBuilder connString = new SQLiteConnectionStringBuilder();
connString.DataSource = path;
connString.Version = 3;
connString.DefaultTimeout = 5;
connString.JournalMode = SQLiteJournalModeEnum.Persist;
connString.UseUTF16Encoding = true;
using (connection = new SQLiteConnection(connString.ToString()))
{
//check for existence of db
FileInfo f = new FileInfo(path);
if (!f.Exists) //build new blank db
{
SQLiteConnection.CreateFile(path);
connection.Open();
using (SQLiteTransaction trans = connection.BeginTransaction())
{
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = DataBase.CREATE_MATCHES;
command.ExecuteNonQuery();
command.CommandText = DataBase.CREATE_STRING_DATA;
command.ExecuteNonQuery();
//TODO add logging
}
trans.Commit();
}
connection.Close();
}
}
}
然后我导出连接字符串，并使用它来获取程序不同部分中的新连接.
I then export the connection string and use it to obtain new connections in different parts of the program.
以看似随机的间隔，尽管以太高的速度无法忽略或以其他方式解决此问题，但我得到了未处理的SQLiteException:数据库文件被锁定.当我尝试提交事务时，会发生这种情况.在此之前似乎没有错误发生.这不会总是 发生.有时，整个过程运行顺利.
At seemingly random intervals, though at far too great a rate to ignore or otherwise workaround this problem, I get unhandled SQLiteException: Database file is locked. This occurs when I attempt to commit the transaction. No errors seem to occur prior to then. This does not always happen. Sometimes the whole thing runs without a hitch.
- 在提交完成之前，不对这些文件执行任何读取.
- 我有最新的SQLite二进制文件.
- 我正在为.NET 2.0进行编译.
- 我正在使用VS 2008.
- 数据库是本地文件.
- 所有这些活动都封装在一个线程/进程中.
- 病毒防护功能已关闭(尽管我认为这仅在通过网络连接时才有意义?).
- 根据苏格兰人的帖子，我进行了以下更改:
- 日记模式设置为持久" 通过
- 存储在C:\ Docs + Settings \ ApplicationData中的DB文件
- 没有内部异常
- 见证了两台不同的机器(尽管硬件和软件非常相似)
- 一直在运行Process Monitor-没有多余的进程将自身附加到数据库文件-问题肯定在我的代码中...
System.Windows.Forms.Application.AppData Windows调用- No reads are being performed on these files before the commits finish.
- I have the very latest SQLite binary.
- I'm compiling for .NET 2.0.
- I'm using VS 2008.
- The db is a local file.
- All of this activity is encapsulated within one thread / process.
- Virus protection is off (though I think that was only relevant if you were connecting over a network?).
- As per Scotsman's post I have implemented the following changes:
- Journal Mode set to Persist
- DB files stored in C:\Docs + Settings\ApplicationData via
System.Windows.Forms.Application.AppDatawindows call - No inner exception
- Witnessed on two distinct machines (albeit very similar hardware and software)
- Have been running Process Monitor - no extraneous processes are attaching themselves to the DB files - the problem is definitely in my code...
有人知道这里发生了什么吗?
Does anyone have any idea whats going on here?
我知道我只是丢了一大堆代码，但是我一直想弄清楚这太久了.感谢所有解决此问题的人！
I know I just dropped a whole mess of code, but I've been trying to figure this out for way too long. My thanks to anyone who makes it to the end of this question!
布莱恩
更新:
谢谢您的建议！我已经实施了许多建议的更改.我觉得我们越来越接近答案了...但是...
Thanks for the suggestions so far! I've implemented many of the suggested changes. I feel that we are getting closer to the answer...however...
以上代码在技术上有效，但是不确定！除了中立旋转之外，不能保证永远做任何事情.实际上，它似乎在第1次到第10次迭代之间起作用.如果我以合理的时间间隔批处理我的提交，损害将得到缓解，但是我真的不想让事情保持这种状态...
The code above technically works however it is non-deterministic! It is not guaranteed to do anything aside from spin in neutral forever. In practice it seems to work somewhere between the 1st and 10th iteration. If i batch my commits at a reasonable interval damage will be mitigated but I really do not want to leave things in this state...
欢迎更多建议！
推荐答案
运行 Sysinternals进程监视器，并在运行程序时对文件名进行过滤，以排除是否有其他进程对其执行了任何操作，并查看您的程序对文件做了什么.远射，但可能会提供线索.
Run Sysinternals Process Monitor and filter on filename while running your program to rule out if any other process does anything to it and to see what exacly your program is doing to the file. Long shot, but might give a clue.
这篇关于SQLite提交期间数据库文件被莫名其妙地锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
