Sqlite3实现脏读

参考文档 http://www.sqlite.org/sharedcache.html

sqlite3实现脏读需要进行如下配置：

(A) 打开共享cache， 调用sqlite3接口sqlite3_enable_shared_cache(1)。

(B) 执行语句"PRAGMA read_uncommitted = TRUE"。

#include <iostream>
#include <string>
#include <vector>
#include <list>
#include <cassert>
#include <thread>

#include "sqlite3.h"

static const char* kDatabaseName = "test.db";

static bool OpenDB(const char* path, sqlite3** db) {
  assert(db != NULL);

  sqlite3_enable_shared_cache(1);

  int rc = sqlite3_open(path, db);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to open " << kDatabaseName << std::endl;
    std::cout << "Error msg: " << sqlite3_errmsg(*db) << std::endl;
    return false;
  }

  sqlite3_exec(*db, "PRAGMA read_uncommitted = TRUE", NULL, NULL, NULL);

  return true;
}

static void PrepareTable() {
  sqlite3* db = NULL;

  if (!OpenDB(kDatabaseName, &db)) {
    return;
  }

  const char* kCreateTableSql = "CREATE TABLE DIRTYREAD_TEST(ID INT);";
  char* error_msg = NULL;

  int rc = sqlite3_exec(db, kCreateTableSql, NULL, NULL, &error_msg);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to create table!" << std::endl;
    std::cout << "Error msg: " << error_msg << std::endl;
    sqlite3_free(error_msg);
  }

  sqlite3_close(db);
}

static void ClearTable() {
  sqlite3* db = NULL;
  if (!OpenDB(kDatabaseName, &db)) {
    return;
  }

  const char* kClearTableSql = "DELETE FROM DIRTYREAD_TEST;";
  char* error_msg = NULL;

  int rc = sqlite3_exec(db, kClearTableSql, NULL, NULL, &error_msg);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to clear table!" << std::endl;
    std::cout << "Error msg: " << error_msg << std::endl;
    sqlite3_free(error_msg);
  }

  sqlite3_close(db);
}

static void InsertData() {
  sqlite3* db = NULL;
  if (!OpenDB(kDatabaseName, &db)) {
    return;
  }

  const char* kInsertDataSql = "INSERT INTO DIRTYREAD_TEST VALUES(?);";

  sqlite3_exec(db, "begin", NULL, NULL, NULL);

  sqlite3_stmt* stmt = NULL;
  sqlite3_prepare_v2(db, kInsertDataSql, strlen(kInsertDataSql), &stmt, NULL);

  for (int i = 0; i < 10000000; i++) {
    sqlite3_reset(stmt);
    sqlite3_bind_int(stmt, 1, i);
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
      std::cout << "Failed to execute sql when insert " << i << "!" << std::endl;
      std::cout << "Error code: " << sqlite3_errcode(db) << std::endl;
      std::cout << "Error msg: " << sqlite3_errmsg(db) << std::endl;
      break;
    }
  }

  sqlite3_finalize(stmt);

  sqlite3_exec(db, "commit", NULL, NULL, NULL);

  sqlite3_close(db);
}

static int SelectCallback(void* data, int col_count, char** col_values, char** col_names) {
  for (int i = 0; i < col_count; ++i) {
    if (col_values[i] == NULL) {
      continue;
    }

    std::cout << col_names[i] << " = " << col_values[i] << std::endl;
  }

  return 0;
}

static void ReadData() {
  sqlite3* db = NULL;
  if (!OpenDB(kDatabaseName, &db)) {
    return;
  }

  const char* kSelectSql = "SELECT * FROM DIRTYREAD_TEST WHERE ID = 1;";
  char* error_msg = NULL;

  for (int i = 0; i < 1000; i++) {
    int rc = sqlite3_exec(db, kSelectSql, SelectCallback, NULL, &error_msg);
    if (rc == SQLITE_OK) {
      std::cout << "Select successfully!" << std::endl;
    } else {
      std::cout << "Select failed! Error msg: " << error_msg << std::endl;
      sqlite3_free(error_msg);
    }
  }

  sqlite3_close(db);
}

int main4() {
  //PrepareTable();
  ClearTable();

  std::thread thread1(InsertData);
  std::thread thread2(ReadData);

  thread1.join();
  thread2.join();

  return 0;
}