DB lock is not released

[sschmidhuber]

The documentation states, that the DB connection will be "...automatically closed/shutdown when it goes out of scope".
But I think it does, only sometimes. I can reproduce the issue with Julia 1.2 on Fedora Linux with this script:

using SQLite
using DataFrames
using Dates

function write_to_db()
    df = DataFrame(:time => [now() |> string], :foo => [rand()], :bar => [rand()])
    db = SQLite.DB("Test.sqlite")
    df |> SQLite.load!(db, "Table")
end

for i in 1:10
    @info "call number $i"
    write_to_db()
    sleep(1)
end

Output:

julia> include("sqlite_test.jl")
[ Info: call number 1
[ Info: call number 2
[ Info: call number 3
ERROR: LoadError: SQLite.SQLiteException("database is locked")
Stacktrace:
 [1] execute(::SQLite.Stmt, ::Tuple{}) at /home/stefan/.julia/packages/SQLite/KwDwo/src/SQLite.jl:317
 [2] execute at /home/stefan/.julia/packages/SQLite/KwDwo/src/SQLite.jl:322 [inlined] (repeats 2 times)
 [3] commit at /home/stefan/.julia/packages/SQLite/KwDwo/src/SQLite.jl:422 [inlined]
 [4] transaction at /home/stefan/.julia/packages/SQLite/KwDwo/src/SQLite.jl:408 [inlined]
 [5] #load!#27(::Bool, ::Bool, ::Bool, ::typeof(SQLite.load!), ::Tables.Schema{(:time, :foo, :bar),Tuple{String,Float64,Float64}}, ::Tables.RowIterator{NamedTuple{(:time, :foo, :bar),Tuple{Array{String,1},Array{Float64,1},Array{Float64,1}}}}, ::SQLite.DB, ::String, ::String, ::Bool) at /home/stefan/.julia/packages/SQLite/KwDwo/src/tables.jl:156
 [6] load!(::Tables.Schema{(:time, :foo, :bar),Tuple{String,Float64,Float64}}, ::Tables.RowIterator{NamedTuple{(:time, :foo, :bar),Tuple{Array{String,1},Array{Float64,1},Array{Float64,1}}}}, ::SQLite.DB, ::String, ::String, ::Bool) at /home/stefan/.julia/packages/SQLite/KwDwo/src/tables.jl:149
 [7] #load!#24(::Base.Iterators.Pairs{Union{},Union{},Tuple{},NamedTuple{(),Tuple{}}}, ::typeof(SQLite.load!), ::DataFrame, ::SQLite.DB, ::String) at /home/stefan/.julia/packages/SQLite/KwDwo/src/tables.jl:142
 [8] load! at /home/stefan/.julia/packages/SQLite/KwDwo/src/tables.jl:138 [inlined]
 [9] #22 at /home/stefan/.julia/packages/SQLite/KwDwo/src/tables.jl:134 [inlined]
 [10] |> at ./operators.jl:854 [inlined]
 [11] write_to_db() at /home/stefan/Dokumente/Julia/sqlite_test.jl:9
 [12] top-level scope at /home/stefan/Dokumente/Julia/sqlite_test.jl:14
 [13] include(::String) at ./client.jl:431
 [14] top-level scope at REPL[2]:1
in expression starting at /home/stefan/Dokumente/Julia/sqlite_test.jl:12

I even added some timeout after each call to the DB (I think that shouldn't be necessary, actually), but it doesn't help. Also an explicit DBInterface.close!(db) doesn't change anything.

Is this an issue of SQLite.jl, my system or am I doing something wrong?

[NicholasWMRitchie]

I've seen this too in more recent Julia releases (1.3.1)
For example, I'd like to build a database in my Test code and then delete it (the file containing it) when the tests have completed. Until the Julia instance shuts down, the file lock remains.

[fingolfin]

I think the documentation is simply wrong: looking at the code of this package, what it does is to install a finalizer via finalizer(_close, db) -- but a finalize in general is only run when a garbage collection happens.
So I guess you could try if inserting GC.gc() calls between runs helps.

Sadly, I don't think this can be fixed. Elsewhere in Julia, this kind of thing is solved via do statements. Let's take for example open:

  open(f::Function, args...; kwargs....)

  Apply the function f to the result of open(args...; kwargs...) and close the resulting file
  descriptor upon completion.

Implementing this shouldn't be too hard, I'd think? (I am currently evaluating how we could store some datasets we need; if I end up using SQLite.jl, I'll look into making a PR, but no promises until then ;-).

[sschmidhuber]

So I guess you could try if inserting GC.gc() calls between runs helps.

Yes, you are right! I just added the call as you suggested and it works.

[felipenoris]

I think this exposes a bug. If DBInterface.close!(db) does not do the job, then there is still a pointer somewhere to the file. My bet is that methods like SQLite.load! that open statements (or any struct that has a pointer to a DB, or a Query that has a pointer to a Stmt and so on) must always call DBInterface.close!(stmt) on a finally block, instead of relying on the GC to do this.