database sqlite 错误数据库被锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8853954/
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 error database is locked
提问by coder
I am have a sqlite database in the iPhone application I am writing. I get an error with following code that I am running in a background thread. In the background thread, I call this method:
我正在编写的 iPhone 应用程序中有一个 sqlite 数据库。我在后台线程中运行以下代码时出现错误。在后台线程中，我调用这个方法：
- (BOOL) songIsInDatabase:(NSString *)songTitle
{
NSString *docsDir;
NSArray *dirPaths;
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];
//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;
if(sqlite3_open(dbpath, &DB) == SQLITE_OK){
NSString *insertSQL = [NSString stringWithFormat:@"select * from Bpm_Table where song_title = '%@'", songTitle];
const char *insert_stmt = [insertSQL UTF8String];
if(sqlite3_prepare_v2(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK)
{
while (sqlite3_step(statement) == SQLITE_ROW)
{
return YES;
break;
}
}else{
NSLog(@"the error is %s", sqlite3_errmsg(DB));
}
sqlite3_finalize(statement);
}
[databasePath release];
return NO;
}
Then I call this method:
然后我调用这个方法：
- (void) addSongToDatabase: (NSString *) songTitle andBPM: (int)bpm andGenre: (NSString *) genre
{
NSString *docsDir;
NSArray *dirPaths;
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];
//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;
if(sqlite3_open(dbpath, &DB) == SQLITE_OK){
NSString *insertSQL = [NSString stringWithFormat:@"insert or replace into Bpm_Table (song_title, bpm, genre) values (\"%@\", \"%d\", \"%@\")", songTitle, bpm, genre];
const char *insert_stmt = [insertSQL UTF8String];
if(sqlite3_prepare(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK){
if(sqlite3_step(statement) == SQLITE_DONE)
{
} else {
NSLog(@"error: %s", sqlite3_errmsg(DB));
}
}sqlite3_finalize(statement);
}
[databasePath release];
}
If I run both these methods, one right after the other, I get an error that says database is locked. I added the sqlite3_finalizestatements after poking around google in hopes that would fix the issue. If I comment out either of the methods, I don't get this error.
如果我一个接一个地运行这两种方法，我会收到一条错误消息，指出database is locked。我sqlite3_finalize在浏览谷歌后添加了这些语句，希望能解决这个问题。如果我注释掉其中一种方法，则不会出现此错误。
Does anyone know what's wrong?
有谁知道出了什么问题？
回答by Saurabh
you must always close the sqlite database after using it ... so add this line sqlite3_close(DB);just after sqlite3_finalize(statement);
您必须在使用后始终关闭 sqlite 数据库...所以sqlite3_close(DB);在后面添加这一行sqlite3_finalize(statement);
Update -
更新 -
You are returning YES in one of your while loop -
您在其中一个 while 循环中返回 YES -
while (sqlite3_step(statement) == SQLITE_ROW)
{
return YES;
break;
}
so here you are nither finalizing nor closing the database.. you need to close if everytime you open it
所以在这里您既没有完成也没有关闭数据库..如果每次打开它都需要关闭
回答by vinayak jadi
your database is open close it using sqlite3_close(db) if you dont close then the process which accessed your database will be running background which will cause database is locked error.
您的数据库已打开，请使用 sqlite3_close(db) 关闭它，如果您不关闭，则访问您的数据库的进程将在后台运行，这将导致数据库被锁定错误。
if you want to remove database is locked error then follow these steps 1.copy your database file to some other location. 2.then replace the database with the copied database 3.this will dereference all processes which were accessing your database file
如果您想删除数据库被锁定错误，请按照以下步骤操作 1.将您的数据库文件复制到其他位置。2.然后用复制的数据库替换数据库 3.这将取消引用访问数据库文件的所有进程
回答by Hot Licks
But note that if you access the same SQLite DB from multiple threads, without some sort of separate synchronization (some way to know that you'll never have near-simultaneous accesses from multiple threads) then you're likely to get "database locked" errors even if you close everything correctly on each access.
但请注意，如果您从多个线程访问同一个 SQLite 数据库，而没有某种单独的同步（某种方式知道您永远不会从多个线程几乎同时访问），那么您可能会“锁定数据库”即使您在每次访问时正确关闭所有内容也会出错。
SQLite does not provide any sort of "lock wait" mechanism.
SQLite 不提供任何类型的“锁定等待”机制。
Your basic options are:
您的基本选项是：
- Do all accesses from one thread.
- Use a separate lock protocol around all accesses.
- If you get a "database locked" error wait a brief period of time and retry.
- 从一个线程进行所有访问。
- 对所有访问使用单独的锁定协议。
- 如果您收到“数据库锁定”错误，请稍等片刻，然后重试。
回答by prashant wadi
Your database is open. Close it using sqlite3_close(db).
您的数据库已打开。使用 关闭它sqlite3_close(db)。
If you don't close, then the process accessing your database will be running background which will cause database is locked error.
如果你不关闭，那么访问你的数据库的进程将在后台运行，这将导致数据库被锁定错误。
回答by turingtested
If you have tried sqlite3_close(DB)it's probably because your methods are trying to access the same database at the same time.
Try to place this line of code
如果您尝试过，sqlite3_close(DB)那可能是因为您的方法试图同时访问同一个数据库。尝试放置这行代码
sqlite3_busy_timeout(DB, 500);
somewhere between sqlite3_openand sqlite3_prepare_v2in the method from where you get the "database is locked"-error message.
在您获得“数据库已锁定”错误消息的方法之间sqlite3_open和之间的某处sqlite3_prepare_v2。
The database-connection in that method will then try to write/read in 500 milliseconds before it gives up, which is usually enough time to escape the locking.
然后，该方法中的数据库连接将在放弃之前的 500 毫秒内尝试写入/读取，这通常是逃避锁定的足够时间。
回答by user3439187
I just spent an hour with the same problem. The problem for me arose when i unwittingly killed a process by closing the shell (when the script was running a while loop and timer), while a db journal temp file was still opened. Windows does not kill the process even though you have killed it in Python, regardless if you have used db.close() or finalize in the script; if you exit all Python apps there will still be one running in the Task manager.
我只是花了一个小时来解决同样的问题。当我通过关闭外壳程序（脚本正在运行 while 循环和计时器时）无意中杀死了一个进程时，我的问题就出现了，而 db 日志临时文件仍处于打开状态。即使您在 Python 中终止了该进程，Windows 也不会终止该进程，无论您是否在脚本中使用了 db.close() 或 finalize；如果您退出所有 Python 应用程序，任务管理器中仍会运行一个。
Hence you will need to end all Python processes in Windows Task Manager, restart them, and it should be fine(ifthat is actually the cause of the problem).
因此，您需要在 Windows 任务管理器中结束所有 Python 进程，重新启动它们，应该没问题（如果这实际上是问题的原因）。
回答by meggar
For one thing, you're not closing the database connection with sqlite3_close() before you open a new connection
一方面，在打开新连接之前，您没有使用 sqlite3_close() 关闭数据库连接
回答by Pinkoy Montano
I had the same problem a while ago. I tried copying the database file itself, renamed it to different filename, then checked it - It actually WORKED!
不久前我遇到了同样的问题。我尝试复制数据库文件本身，将其重命名为不同的文件名，然后检查它 - 它实际上有效！
I am using SQLite3.
我正在使用 SQLite3。
I hope this will also work for you!
我希望这也适用于你！
