C# Sqlite 数据库被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1166265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sqlite database locked
提问by
I'm using asp.net c# and upload a SqLite database to a server and then I do some inserting and updating. The problem is that sometimes (I think it's when somethings go wrong with the updating or so) the database gets locked. So the next time I try to upload a file again it's locked and I get an error saying "The process cannot access the file because it is being used by another process". Maybe the database file isn't disposed if something goes wrong during the transaction? The only thing to solve this problem is restarting the server.
我正在使用 asp.net c# 并将 Sqlite 数据库上传到服务器，然后进行一些插入和更新。问题是有时（我认为是更新出现问题时）数据库被锁定。因此，下次我尝试再次上传文件时，它已被锁定，并且收到一条错误消息，提示“该进程无法访问该文件，因为它正被另一个进程使用”。如果在事务过程中出现问题，可能不会处理数据库文件？解决此问题的唯一方法是重新启动服务器。
How can I solve it in my code so I can be sure it's always unlocked even if something goes wrong?
我怎样才能在我的代码中解决它，这样即使出现问题，我也可以确保它总是被解锁？
This is my code:
这是我的代码：
try
{
string filepath = Server.MapPath("~/files/db.sql");
//Gets the file and save it on the server
((HttpPostedFile)HttpContext.Current.Request.Files["sqlitedb"]).SaveAs(filepath);
//Open the database
SQLiteConnection conn = new SQLiteConnection("Data Source=" + filepath + ";Version=3;");
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
using (SQLiteTransaction transaction = conn.BeginTransaction())
{
using (cmd)
{
//Here I do some stuff to the database, update, insert etc
}
transaction.Commit();
}
conn.Close();
cmd.Dispose();
}
catch (Exception exp)
{
//Error
}
回答by Zhaph - Ben Duguid
You could try placing the Connection in a using block as well, or calling Dispose on it:
您也可以尝试将 Connection 放在 using 块中，或者对其调用 Dispose：
//Open the database
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + filepath + ";Version=3;")) {
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn)) {
using (SQLiteTransaction transaction = conn.BeginTransaction()) {
//Here I do some stuff to the database, update, insert etc
transaction.Commit();
}
}
}
This will ensure that you're disposing of the connection object's correctly (you're not at the moment, only closing it).
这将确保您正确处理连接对象（您目前不是，只是关闭它）。
Wrapping them in using blocks ensures that Dispose is called even if an exception happens - it's effectively the same as writing:
将它们包装在 using 块中可确保即使发生异常也调用 Dispose - 它实际上与编写相同：
// Create connection, command, etc objects.
SQLiteConnection conn;
try {
conn = new SQLiteConnection("Data Source=" + filepath + ";Version=3;");
// Do Stuff here...
}
catch (exception e) {
// Although there are arguments to say don't catch generic exceptions,
// but instead catch each explicit exception you can handle.
}
finally {
// Check for null, and if not, close and dispose
if (null != conn)
conn.Dispose();
}
The code in the finally block is going to be called regardless of the exception, and helps you clean up.
无论异常如何，finally 块中的代码都会被调用，并帮助您清理。
回答by Tim Sylvester
Shouldn't you do cmd.Dispose()before conn.Close()? I don't know if it makes any difference, but you generally want to clean things up in the opposite of initialization order.
你cmd.Dispose()之前不应该做conn.Close()吗？我不知道它是否有任何区别，但您通常希望以与初始化顺序相反的方式进行清理。
回答by Doug
In short, SQLite handles unmanaged resources slightly differently than other providers. You'll have to explicitly dispose the command (which seems to work even if you are working with the reader outside of the using() block.
简而言之，SQLite 处理非托管资源的方式与其他提供程序略有不同。您必须显式处理命令（即使您在 using() 块之外使用读取器，它似乎也能工作。
Read this thread for more flavor: http://sqlite.phxsoftware.com/forums/p/909/4164.aspx
阅读此线程以获得更多风味：http: //sqlite.phxsoftware.com/forums/p/909/4164.aspx
回答by tuinstoel
An asp.net application is multithreaded in the server.
asp.net 应用程序在服务器中是多线程的。
You can't do simultaneous writing (insert, select, update...) because the whole db is locked. Simultaneously selecting is allowed when no writing is happening.
您不能同时写入（插入、选择、更新...），因为整个数据库都被锁定了。没有写入时允许同时选择。
You should use the .NET ReaderWriterLock class: http://msdn.microsoft.com/en-us/library/system.threading.readerwriterlock.aspx
您应该使用 .NET ReaderWriterLock 类：http: //msdn.microsoft.com/en-us/library/system.threading.readerwriterlock.aspx
