这是官方网站找到的解释：http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
下面我做了几个实验：
1、多个线程（pthread），使用同一个句柄（一次sqlite3_open，同一个数据库文件），在多个线程中同时使用此句柄，这些线程中有的是进行select操作的，有的是进行update操作的，使用的都是sqlite3_exec函数；结果是没有发现database is locked错误；
程序源码（mutitrhead.c ）：
#include <sqlite3.h>
#include <stdio.h>
#include <pthread.h>
#include <string.h>
#include "main.h"
#ifdef _WIN32
#include <windows.h>
#define sleep(x) Sleep((x)*1000)
#else
#define sleep(x) sleep(x)
#endif
#define CREATE_SQL \
"CREATE TABLE IF NOT EXISTS \"mutiprocess\" (" \
"\"id\" INTEGER NOT NULL PRIMARY KEY," \
"\"src\" TEXT(1024)," \
"\"videotype\" INTEGER NOT NULL," \
"\"postfiledir\" TEXT(1024)" \
");"
#define REPLACE_SQL "REPLACE INTO mutiprocess (id,src,videotype,postfiledir) VALUES(NULL,%d,%d,%d)"
#define UPDATE_SQL "UPDATE mutiprocess SET videotype = '2' WHERE id = 1"
#define SELECT_SQL "SELECT * FROM mutiprocess"
static void* select_cf(void *param)
{
int rc = -1;
sqlite3 *db = (sqlite3*)param;
char *szErrMsg;
pthread_t tid;
tid = pthread_self();
while(1){
rc = sqlite3_exec(db ,SELECT_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("tid %u -- select error.[%s] -- rc=[%d]\n", tid, szErrMsg, rc);
if(strstr(szErrMsg, "database is locked")){
printf("<database is locked> when executing [%s], retry after 1 second.\n", SELECT_SQL);
}
}else{
printf("tid %u -- successfully execute [%s].\n", tid, SELECT_SQL);
}
sleep(1);
}
}
static void* update_cf(void *param)
{
int rc = -1;
sqlite3 *db = (sqlite3*)param;
char *szErrMsg;
pthread_t tid;
tid = pthread_self();
while(1){
rc = sqlite3_exec(db ,UPDATE_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("tid %u -- update error.[%s] -- rc=[%d]\n", tid, szErrMsg, rc);
if(strstr(szErrMsg, "database is locked")){
printf("<database is locked> when executing [%s], retry after 1 second.\n", UPDATE_SQL);
}
}else{
printf("tid %u -- successfully execute [%s].\n", tid, UPDATE_SQL);
}
sleep(1);
}
}
int main()
{
pthread_t pid[20];
int rc;
sqlite3 *db = 0;
char *szErrMsg;
int i = 0;
char sql[1024] = {0};
rc = sqlite3_open("mutiprocess.db", &db);
if (rc != SQLITE_OK) {
printf("open sqlite3 error.\n");
}
rc = sqlite3_exec(db ,CREATE_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("create db error-[%s]\n", szErrMsg);
}
for(i = 0; i < 4; i++){
sprintf(sql, REPLACE_SQL, i, i, i);
rc = sqlite3_exec(db ,sql, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("replace db error-[%s]\n", szErrMsg);
}
}
for(i = 0; i < 10; i++){
pthread_create(&(pid[i]), 0, select_cf, db);
pthread_detach(pid[i]);
}
for(; i < 20; i++){
pthread_create(&(pid[i]), 0, update_cf, db);
pthread_detach(pid[i]);
}
while(1){
sleep(-1);
}
return 0;
}
编译：gcc -o mutitrhead mutitrhead.c -lsqlite3 -lpthread
运行：./mutithread
注意：只运行这一个进程没有发现任何database is locked的错误提示；如果在运行一个./mutithread进程，那么两个进程都会出现database is locked错误；如果kill掉其中一个进程，那么另外一个不再出现database is locked；
注意两个进程和一个进程的区别，一个是进程数不同，一个是使用的数据库句柄连接不同；上述程序虽然在一个进程中使用了多线程，但是多个线程都是使用了同一个数据库连接（使用一个sqlite3_open返回的句柄），不好区分是什么问题（其实官方网站有一句话很重要『SQLITE_BUSY means that another database connection (probably in another process) is using the database in a way that prevents you from using it』）；这其实已经说明了产生database is locked的原因了，就是出在多个连接上，那么我们在做下面的实验；
2、同一个进程，启动多个线程，每个线程中都打开一个连接（connection，使用一个sqlite3_open返回的句柄），并且在其中做select或者update的操作；结果会出现database is locked错误；
程序源码（muticonnection.c）：
#include <sqlite3.h>
#include <stdio.h>
#include <pthread.h>
#include <string.h>
#include "main.h"
#ifdef _WIN32
#include <windows.h>
#define sleep(x) Sleep((x)*1000)
#else
#define sleep(x) sleep(x)
#endif
#define CREATE_SQL \
"CREATE TABLE IF NOT EXISTS \"mutiprocess\" (" \
"\"id\" INTEGER NOT NULL PRIMARY KEY," \
"\"src\" TEXT(1024)," \
"\"videotype\" INTEGER NOT NULL," \
"\"postfiledir\" TEXT(1024)" \
");"
#define REPLACE_SQL "REPLACE INTO mutiprocess (id,src,videotype,postfiledir) VALUES(NULL,%d,%d,%d)"
#define UPDATE_SQL "UPDATE mutiprocess SET videotype = '2' WHERE id = 1"
#define SELECT_SQL "SELECT * FROM mutiprocess"
static void* select_cf(void *param)
{
int rc;
sqlite3 *db = 0;
char *szErrMsg;
int i = 0;
char sql[1024] = {0};
pthread_t tid;
rc = sqlite3_open("mutiprocess.db", &db);
if (rc != SQLITE_OK) {
printf("open sqlite3 error.\n");
}
rc = sqlite3_exec(db ,CREATE_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("create db error-[%s]\n", szErrMsg);
}
for(i = 0; i < 4; i++){
sprintf(sql, REPLACE_SQL, i, i, i);
rc = sqlite3_exec(db ,sql, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("replace db error-[%s]\n", szErrMsg);
}
}
tid = pthread_self();
while(1){
rc = sqlite3_exec(db ,SELECT_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("tid %u -- select error.[%s] -- rc=[%d]\n", tid, szErrMsg, rc);
if(strstr(szErrMsg, "database is locked")){
printf("<database is locked> when executing [%s], retry after 1 second.\n", SELECT_SQL);
}
}else{
printf("tid %u -- successfully execute [%s].\n", tid, SELECT_SQL);
}
sleep(1);
}
}
static void* update_cf(void *param)
{
int rc;
sqlite3 *db = 0;
char *szErrMsg;
int i = 0;
char sql[1024] = {0};
pthread_t tid;
rc = sqlite3_open("mutiprocess.db", &db);
if (rc != SQLITE_OK) {
printf("open sqlite3 error.\n");
}
rc = sqlite3_exec(db ,CREATE_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("create db error-[%s]\n", szErrMsg);
}
for(i = 0; i < 4; i++){
sprintf(sql, REPLACE_SQL, i, i, i);
rc = sqlite3_exec(db ,sql, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("replace db error-[%s]\n", szErrMsg);
}
}
tid = pthread_self();
while(1){
rc = sqlite3_exec(db ,UPDATE_SQL, 0, 0, &szErrMsg);
if (rc != SQLITE_OK) {
printf("tid %u -- update error.[%s] -- rc=[%d]\n", tid, szErrMsg, rc);
if(strstr(szErrMsg, "database is locked")){
printf("<database is locked> when executing [%s], retry after 1 second.\n", UPDATE_SQL);
}
}else{
printf("tid %u -- successfully execute [%s].\n", tid, UPDATE_SQL);
}
sleep(1);
}
}
int main()
{
pthread_t pid[20];
int i = 0;
for(i = 0; i < 10; i++){
pthread_create(&(pid[i]), 0, select_cf, 0);
pthread_detach(pid[i]);
}
for(; i < 20; i++){
pthread_create(&(pid[i]), 0, update_cf, 0);
pthread_detach(pid[i]);
}
while(1){
sleep(-1);
}
return 0;
}
编译：gcc -o muticonnection muticonnection.c -lsqlite3 -lpthread
运行：./muticonnection
总结：看来出现这个错误是因为多数是因为使用了多个连接(多个sqlite3_open返回的句柄)导致的，这里我的说法是“多数”，因为还有别的原因，请见官方网站的解释：http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
还有一点要注意：
database is locked是SQLITE_BUSY的错误，不要和SQLITE_LOCKED混淆；
SQLITE_LOCKED（6） means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error.
SQLITE_BUSY（5） means that another database connection (probably in another process) is using the database in a way that prevents you from using it.
附：SQLITE_LOCKED(6)错误见：http://sqlite.1065341.n5.nabble.com/Begin-immediate-transaction-gt-SQLITE-BUSY-database-is-locked-td64878.html