c - 为什么 sqlite 允许第二个进程进行写操作而第一个进程已经在进行写操作

Question

我写了两个流程代码都复制在下面，让我们假设 sqlitep1.c 指的是 p1，sqlitep2.c 指的是 p2

p1不断地从数据库文件sample.db中读取，p2不断地写入数据库文件sample.db，

我执行了 p2 和 p3 的一个实例以及 p1 的一个实例，根据 sqlite 文档，两个进程不能同时写入 sqlite。

在代码 sqlitep2.c 中我有两个版本，一个打开连接并写入然后关闭 (p2) 另一个版本打开连接并写入 db 文件并使用 while(1) block ，如第二个版本所示作为 p3 执行的 sqlitep2.c。

我先执行p3再执行p2，根据代码p3写入后会阻塞，此时我假设sqlite锁没有释放，因为连接没有关闭。

但在我的结果中，我可以看到 p2 能够写入而不会出现任何繁忙错误，即使 p3 尚未释放锁也是如此。

注意:在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 的第二个版本在写操作后有一个无限 block 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);
    }
}

Answer 1

SQLite 会在事务周围加锁，而不是打开连接。如果没有显式事务(您似乎没有这样做)，每个 SQL 语句都是一个单独的事务，在执行语句时结束。你有一个未完成的声明没有任何意义，因为“step”执行了你的 INSERT 并释放了锁。