sqlite3 "database is locked"

问题原因

        sqlite3_exec对于执行create table、insert、update等操作，会对整个数据库加锁，导致加锁期间其他连接执行sqlite3_exec()失败，返回“database is locked”，错误码为5。

 

测试：创建两个线程，线程1向test表插入数值1，线程2向test表插入数值2，每个线程内循环执行10次，观察现象。

#include "sqlite3.h"
#include <iostream>
#include <windows.h>
using namespace std;


//线程入口函数
DWORD WINAPI Sqlite3ExecSql(LPVOID lpParamter)
{
	char 		*msg = NULL;
	sqlite3 	*db = NULL;
	int 		nRC = 0;
	char        szSqlite3Path[1024] = { 0 };
	int			nCount = 0;
	char		*pszSql = (char*)lpParamter;

	_snprintf(szSqlite3Path, sizeof(szSqlite3Path), "%s\\%s", "F:\\VS2013Workspace\\SqliteTest\\SqliteTest", "LockTest.db");

	while (1){
		if (nCount == 10)
			break;

		//打开数据库
		nRC = sqlite3_open(szSqlite3Path, &db);
		if (SQLITE_OK != nRC) {
			cout << "error code:" << nRC << endl;
			return -1;
		}

		//执行sql语句
		nRC = sqlite3_exec(db, pszSql, 0, 0, &msg);
		if (nRC != 0){
			cout << "failed: " << pszSql << "\t" << "err msg:" << msg << ", " << "code: " << nRC << endl << endl;
		}
		else{
			cout << "succeed: " << pszSql << endl << endl;
		}

		sqlite3_close(db);

		nCount++;

		Sleep(100);
	}

	return 0;
}

void main(){
	int 		nRC = 0;
	char        szSqlite3Path[1024] = { 0 };
	sqlite3 	*db = NULL;
	char		*msg = NULL;

	_snprintf(szSqlite3Path, sizeof(szSqlite3Path), "%s\\%s", "F:\\VS2013Workspace\\SqliteTest\\SqliteTest", "LockTest.db");

	//创建数据库
	nRC = sqlite3_open(szSqlite3Path, &db);
	if (SQLITE_OK != nRC) {
		cout << "error code:" << nRC << endl;
		return;
	}

	//创建表
	nRC = sqlite3_exec(db, "create table IF NOT EXISTS test(a int)", 0, 0, &msg);
	if (nRC != 0){
		cout << "error code:" << nRC << ",msg: "<<msg << endl;
		return;
	}

	//创建线程1，每隔100ms插入一条数据，值为1
	HANDLE hThread1 = CreateThread(NULL, 0, Sqlite3ExecSql, "insert into test values(1)", 0, NULL);

	//创建线程2，每隔100ms插入一条数据，值为2
	HANDLE hThread2 = CreateThread(NULL, 0, Sqlite3ExecSql, "insert into test values(2)", 0, NULL);

	while (1){
		Sleep(5);
	}
}

执行结果：

结论：

当其中一个线程正在执行SQL语句期间，发生线程调度，另一个线程也开始执行SQL语句，此时线程一已对数据库加锁且未释放，线程二没有执行权限，导致线程二执行sqlite3_exec返回错误码5，错误信息"database is locked"。

 

 

解决方案

调用sqlite3_exec()之前，调用sqlite3_busy_timeout()设置锁等待时间。sqlite3_busy_timeout针对数据库句柄，设置执行sql语句的默认等待时间，如果在执行期间，数据库被锁住，sqlite3_exec()会不断重试，直到执行成功或者等待超时。

#include "sqlite3.h"
#include <iostream>
#include <windows.h>
using namespace std;


//线程入口函数
DWORD WINAPI Sqlite3ExecSql(LPVOID lpParamter)
{
	char 		*msg = NULL;
	sqlite3 	*db = NULL;
	int 		nRC = 0;
	char        szSqlite3Path[1024] = { 0 };
	int			nCount = 0;
	char		*pszSql = (char*)lpParamter;

	_snprintf(szSqlite3Path, sizeof(szSqlite3Path), "%s\\%s", "F:\\VS2013Workspace\\SqliteTest\\SqliteTest", "LockTest.db");

	while (1){
		if (nCount == 10)
			break;

		//打开数据库
		nRC = sqlite3_open(szSqlite3Path, &db);
		if (SQLITE_OK != nRC) {
			cout << "error code:" << nRC << endl;
			return -1;
		}

		//设置超时等待时间:1s
		sqlite3_busy_timeout(db, 1000);

		//执行sql语句
		nRC = sqlite3_exec(db, pszSql, 0, 0, &msg);
		if (nRC != 0){
			cout << "failed: " << pszSql << "\t" << "err msg:" << msg << ", " << "code: " << nRC << endl << endl;
		}
		else{
			cout << "succeed: " << pszSql << endl << endl;
		}

		sqlite3_close(db);

		nCount++;

		Sleep(100);
	}

	return 0;
}

void main(){
	int 		nRC = 0;
	char        szSqlite3Path[1024] = { 0 };
	sqlite3 	*db = NULL;
	char		*msg = NULL;

	_snprintf(szSqlite3Path, sizeof(szSqlite3Path), "%s\\%s", "F:\\VS2013Workspace\\SqliteTest\\SqliteTest", "LockTest.db");

	//创建数据库
	nRC = sqlite3_open(szSqlite3Path, &db);
	if (SQLITE_OK != nRC) {
		cout << "error code:" << nRC << endl;
		return;
	}

	//创建表
	nRC = sqlite3_exec(db, "create table IF NOT EXISTS test(a int)", 0, 0, &msg);
	if (nRC != 0){
		cout << "error code:" << nRC << ",msg: "<<msg << endl;
		return;
	}

	//创建线程1，每隔100ms插入一条数据，值为1
	HANDLE hThread1 = CreateThread(NULL, 0, Sqlite3ExecSql, "insert into test values(1)", 0, NULL);

	//创建线程2，每隔100ms插入一条数据，值为2
	HANDLE hThread2 = CreateThread(NULL, 0, Sqlite3ExecSql, "insert into test values(2)", 0, NULL);

	while (1){
		Sleep(5);
	}
}

执行结果：

结论：在等待时间内，线程重新获得CPU控制权并且锁已被另一个线程释放，sqlite3_exec就可以执行成功，因此所有插入全部执行成功。