c - 为什么 sqlite 允许第二个进程进行写操作,而第一个进程已经在进行写操作
问题描述
我写了两个过程代码,两个代码都复制在下面,让我们假设 sqlitep1.c 指的是 p1,sqlitep2.c 指的是 p2
p1 不断地从数据库文件 sample.db 中读取,p2 写入数据库文件 sample.db,
我执行了一个 p2 & p3 实例和一个 p1 实例,根据 sqlite 文档,两个进程不能同时写入 sqlite。
在代码 sqlitep2.ci 中有两个版本,一个打开连接并执行写入然后关闭 (p2),另一个版本打开连接并写入 db 文件并使用 while(1) 阻塞,如 sqlitep2.c 的第二个版本所示它作为 p3 执行。
我先执行p3然后p2,根据代码p3写入后会阻塞,此时我假设sqlite锁没有释放,因为连接没有关闭。
但是在我的结果中,即使 p3 没有释放锁,我也可以看到 p2 能够写入而不会出现任何忙错误。
注意:在linux机器上执行。
sqlitep1.c
#include <stdio.h>
#include </sqlite/sqlite3.h>
int main()
{
sqlite3_stmt *pSqlStmt = NULL;
sqlite3 *pSqlHandle = NULL;
int ret = SQLITE_ERROR;
char *pcSharedPath = "sample.db";
char* pcSqlQuery = NULL;
char *name = NULL;
int retry_count = 0;
while (1)
{
printf ("process 1.....\n");
printf ("-----------------------------\n\n");
ret = SQLITE_ERROR;
/*open connection to sqlite*/
while (ret != SQLITE_OK) {
printf ("process 1, open connection\n");
ret = sqlite3_open_v2 (pcSharedPath, &(pSqlHandle), (SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX), NULL);
if (ret != SQLITE_OK)
{
printf ("process 1, opening failed....\n");
if (ret == SQLITE_BUSY)
{
printf ("process 1, open connection busy error....\n");
}
}
sleep(1);
}
printf ("process 1, database connection opened...\n");
/* prepare query */
ret = SQLITE_ERROR;
pcSqlQuery = "SELECT * FROM EMP";
while (ret != SQLITE_OK)
{
ret = sqlite3_prepare_v2 (pSqlHandle, pcSqlQuery, -1, &pSqlStmt, NULL);
if (ret == SQLITE_BUSY) {
printf ("process 1, prepare busy error....\n");
}
if (ret == SQLITE_ERROR) {
printf("SQLITE_ERROR\n");
}
sleep(1);
}
printf ("process 1, prepare success...\n");
/* extract result from query */
while(1)
{
ret = sqlite3_step (pSqlStmt);
if (ret == SQLITE_DONE)
break;
if (ret != SQLITE_ROW) {
printf("process 1, no row exists...\n");
break;
}
name = sqlite3_column_text (pSqlStmt, 1);
printf ("%s \n", name);
}
/* finalize */
if (NULL != pSqlStmt)
{
ret = SQLITE_ERROR;
// while (ret != SQLITE_OK) {
ret = sqlite3_finalize (pSqlStmt);
printf ("process 1, Finalizing %d...\n", ret);
// }
pSqlStmt = NULL;
}
/* close sqlite connection */
ret = SQLITE_ERROR;
retry_count = 0;
while (ret != SQLITE_OK && retry_count != 5) {
ret = sqlite3_close(pSqlHandle);
printf("sqlite3_close %d...\n", ret);
retry_count++;
}
retry_count=0;
if (SQLITE_OK != ret) {
printf ("sqlite close failed....Exiting process 1...\n");
return 0;
}
}
}
写入操作后不阻塞的 sqlitep2.c 的第一个版本(没有 while(1))
#include <stdio.h>
#include </sqlite/sqlite3.h>
#include <sys/time.h>
int main(int argc, char *argv[])
{
sqlite3_stmt *pSqlStmt = NULL;
sqlite3 *pSqlHandle = NULL;
int ret = SQLITE_ERROR;
char *pcSharedPath = "sample.db";
char pcSqlQuery[100] = "";
char *name = NULL;
int i = atoi(argv[1]);
int retry_count = 0;
while (1)
{
printf ("process 2.....\n");
printf ("-----------------------------\n\n");
ret = SQLITE_ERROR;
/*open connection to sqlite*/
while (ret != SQLITE_OK) {
printf ("process 2, open connection\n");
ret = sqlite3_open_v2 (pcSharedPath, &(pSqlHandle), (SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX), NULL);
if (ret != SQLITE_OK)
{
printf ("process 2, opening failed %d....\n", ret);
if (ret == SQLITE_BUSY)
{
printf ("process 2, open connection busy error....\n");
}
}
usleep(10*1000);
}
printf ("process 2, database connection opened...\n");
sqlite3_busy_timeout(pSqlHandle, 1000);
/* prepare query */
ret = SQLITE_ERROR;
char name[50];
while (ret != SQLITE_OK && retry_count != 10)
{
pSqlStmt = NULL;
sqlite3_snprintf(50, name, "\"Sachin%d\"", i);
sqlite3_snprintf(100,pcSqlQuery, "INSERT INTO EMP VALUES (%d, %s)", i, name);
printf ("%s\n", pcSqlQuery);
ret = sqlite3_prepare_v2 (pSqlHandle, "INSERT INTO EMP(ID, NAME) VALUES (?1, ?2);", -1, &pSqlStmt, NULL);
sqlite3_bind_int(pSqlStmt, 1, i);
sqlite3_bind_text(pSqlStmt, 2, name, -1, SQLITE_STATIC);
if (ret == SQLITE_BUSY) {
printf ("process 2, prepare busy error....\n");
}
else {
if (ret == SQLITE_ERROR)
{
printf ("SQLITE_ERROR...\n");
}
ret = SQLITE_ERROR;
while (ret != SQLITE_OK) {
ret = sqlite3_step(pSqlStmt);
printf ("process 2, return from sqlite3_step : %d...\n", ret);
if (ret != SQLITE_DONE) {
printf ("process 2, insert error...\n");
} else if (ret == SQLITE_BUSY) {
printf("sqlite3_step busy error...\n");
} else {
i++;
ret = SQLITE_OK;
}
}
}
printf ("process 2, ret value of insert op %d\n ", ret);
usleep(10*1000);
retry_count++;
}
retry_count=0;
printf ("process 2, prepare success...\n");
/* finalize */
if (NULL != pSqlStmt)
{
ret = SQLITE_ERROR;
while (ret != SQLITE_OK) {
ret = sqlite3_finalize (pSqlStmt);
printf ("process 2, Finalizing %d...\n", ret);
}
pSqlStmt = NULL;
}
/* close sqlite connection */
ret = SQLITE_ERROR;
retry_count = 0;
ret = sqlite3_close(pSqlHandle);
while (ret != SQLITE_OK) {
ret = sqlite3_close(pSqlHandle);
printf("sqlite3_close %d...\n", ret);
retry_count++;
sleep(1);
}
retry_count=0;
pSqlHandle = NULL;
if (SQLITE_OK != ret) {
printf ("sqlite close failed....Exiting process 2...\n");
return 0;
}
sleep(1);
}
}
sqlitep2.c 的第二个版本,在写操作后有一个无限块 while(1),这意味着它没有释放锁。
#include <stdio.h>
#include </sqlite/sqlite3.h>
#include <sys/time.h>
int main(int argc, char *argv[])
{
sqlite3_stmt *pSqlStmt = NULL;
sqlite3 *pSqlHandle = NULL;
int ret = SQLITE_ERROR;
char *pcSharedPath = "sample.db";
char pcSqlQuery[100] = "";
char *name = NULL;
int i = atoi(argv[1]);
int retry_count = 0;
while (1)
{
printf ("process 2.....\n");
printf ("-----------------------------\n\n");
ret = SQLITE_ERROR;
/*open connection to sqlite*/
while (ret != SQLITE_OK) {
printf ("process 2, open connection\n");
ret = sqlite3_open_v2 (pcSharedPath, &(pSqlHandle), (SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX), NULL);
if (ret != SQLITE_OK)
{
printf ("process 2, opening failed %d....\n", ret);
if (ret == SQLITE_BUSY)
{
printf ("process 2, open connection busy error....\n");
}
}
usleep(10*1000);
}
printf ("process 2, database connection opened...\n");
sqlite3_busy_timeout(pSqlHandle, 1000);
/* prepare query */
ret = SQLITE_ERROR;
char name[50];
while (ret != SQLITE_OK && retry_count != 10)
{
pSqlStmt = NULL;
sqlite3_snprintf(50, name, "\"Sachin%d\"", i);
sqlite3_snprintf(100,pcSqlQuery, "INSERT INTO EMP VALUES (%d, %s)", i, name);
printf ("%s\n", pcSqlQuery);
ret = sqlite3_prepare_v2 (pSqlHandle, "INSERT INTO EMP(ID, NAME) VALUES (?1, ?2);", -1, &pSqlStmt, NULL);
sqlite3_bind_int(pSqlStmt, 1, i);
sqlite3_bind_text(pSqlStmt, 2, name, -1, SQLITE_STATIC);
if (ret == SQLITE_BUSY) {
printf ("process 2, prepare busy error....\n");
}
else {
if (ret == SQLITE_ERROR)
{
printf ("SQLITE_ERROR...\n");
}
ret = SQLITE_ERROR;
while (ret != SQLITE_OK) {
ret = sqlite3_step(pSqlStmt);
printf ("process 2, return from sqlite3_step : %d...\n", ret);
if (ret != SQLITE_DONE) {
printf ("process 2, insert error...\n");
} else if (ret == SQLITE_BUSY) {
printf("sqlite3_step busy error...\n");
} else {
i++;
ret = SQLITE_OK;
}
}
}
printf ("process 2, ret value of insert op %d\n ", ret);
usleep(10*1000);
retry_count++;
}
while (1) {} // block here, DO NOT proceed further.
retry_count=0;
printf ("process 2, prepare success...\n");
/* finalize */
if (NULL != pSqlStmt)
{
ret = SQLITE_ERROR;
while (ret != SQLITE_OK) {
ret = sqlite3_finalize (pSqlStmt);
printf ("process 2, Finalizing %d...\n", ret);
}
pSqlStmt = NULL;
}
/* close sqlite connection */
ret = SQLITE_ERROR;
retry_count = 0;
ret = sqlite3_close(pSqlHandle);
while (ret != SQLITE_OK) {
ret = sqlite3_close(pSqlHandle);
printf("sqlite3_close %d...\n", ret);
retry_count++;
sleep(1);
}
retry_count=0;
pSqlHandle = NULL;
if (SQLITE_OK != ret) {
printf ("sqlite close failed....Exiting process 2...\n");
return 0;
}
sleep(1);
}
}
解决方案
SQLite 会锁定事务,而不是打开连接。如果没有显式事务(您似乎没有这样做),每个 SQL 语句都是一个单独的事务,在执行语句时结束。你有一个未完成的语句意味着什么,因为“步骤”执行了你的 INSERT 并释放了锁。
推荐阅读
- docker - 使用修改后的 fabric-server-config 初始化结构 ca
- c# - 在大文件中搜索性能差异
- python - 获取神经网络的预测列表
- kotlin - 听众对第二次呼叫没有反应
- python - 实现骰子游戏
- python - 在 Tensorflow 2.0 中翻转和旋转张量(在 U-net 内)会耗尽内存。如何正确地做到这一点?
- python - 循环遍历视频python中的所有帧
- jestjs - 从 Redux Thunk 模拟调度作为单元测试?
- flutter - 弹出PopUpWindow后如何调用setState(重建页面)?
- reactjs - 使用 React 将图像上传到 Azure Blob 存储