当另一个进程正在写入时(通过 R)从 SQLite 数据库中读取而不会出现错误

Question

我是 SQL 的新手，并且尝试读取我正在构建的数据库，同时我有另一个进程正在写入它。我正在严格阅读写入过程未写入的内容(在我的真实示例中)。

当我尝试从数据库中读取数据时，写入过程通常会停止并且数据库会被锁定——我需要杀死 R 并重新启动它。

我有点直觉地理解为什么这会有问题。但似乎有一些解决方法(“无锁”？)我无法弄清楚如何实现。

MWE 下面:运行第一个进程，然后开始另一个 session 并尝试运行第二个进程几次:

1> library(dplyr)
1> library(RSQLite)
1> system("rm dummy.sqlite3")
1> db <- src_sqlite("dummy.sqlite3", create = T)
1> df = data.frame(x = rnorm(2),y = rnorm(2))
1> table = copy_to(db, df = df, temporary = FALSE)
1> #Write process
1> for (i in 1:1e6){
1+ x = data.frame(x = rnorm(1),y = rnorm(1))
1+ db_insert_into(con = db$con, table = 'df', values = x) 
1+ }

然后在另一个 session 中结束...

3> db <- src_sqlite("dummy.sqlite3", create = F)
3> df = tbl(db,'df')
3> x1<-filter(df, x>3)
3> collect(x1)
Source: local data frame [2 x 2]

         x          y
     (dbl)      (dbl)
1 3.445299 -0.2531794
2 3.235710 -1.2147918
3> library(dplyr)
3> library(RSQLite)
3> setwd('/home/andrew/Dropbox/weirding_data')
3> db <- src_sqlite("dummy.sqlite3", create = F)
3> df = tbl(db,'df')
Error in sqliteFetch(res, n = n) : 
  rsqlite_query_fetch: failed first step: database is locked
3> x1<-filter(df, x>3)
3> collect(x1)
Source: local data frame [3 x 2]

         x          y
     (dbl)      (dbl)
1 3.445299 -0.2531794
2 3.235710 -1.2147918
3 3.457522  0.9358973
3> library(dplyr)
3> library(RSQLite)
3> setwd('/home/andrew/Dropbox/weirding_data')
3> db <- src_sqlite("dummy.sqlite3", create = F)
3> df = tbl(db,'df')
3> x1<-filter(df, x>3)
3> collect(x1)
Source: local data frame [5 x 2]

         x          y
     (dbl)      (dbl)
1 3.445299 -0.2531794
2 3.235710 -1.2147918
3 3.457522  0.9358973
4 3.265626 -0.7512677
5 3.052190 -0.1328862
3> library(dplyr)
3> library(RSQLite)
3> setwd('/home/andrew/Dropbox/weirding_data')
3> db <- src_sqlite("dummy.sqlite3", create = F)
3> df = tbl(db,'df')
Error in sqliteFetch(rs, n = -1) : 
  rsqlite_query_fetch: failed first step: database is locked
3> x1<-filter(df, x>3)
3> collect(x1)
Error in sqliteFetch(res, n = n) : 
  rsqlite_query_fetch: failed first step: database is locked

它在失败之前工作了几次。

那么，我怎样才能从正在写入的东西中读取呢？为什么失败不是确定性的？如果有查询提取与写入过程相同的数据量，那么写入速度会受到多大的影响？

Answer 1

SQLite 总是锁定整个数据库。

如果您有多个属于一起的数据库操作，请考虑使用 transactions这样你就不会在中间遇到意外的失败。

要允许并发读取和写入，请启用 WAL mode (但请注意缺点)。