Allow threadsafe insertion of blobs

[herzfeldd]

I recently ran into an issue where I happened to be sharing a DB connection across a Threads.@threads for loop. I am wondering if it would be useful to expose the sqlite3_config function (or a subset of the configuration) in api.jl to allow SQLite's threading mode to be selected. Something like:

function sqlite3_config_threads(thread_config::Int)
    return ccall( (:sqlite3_config, libsqlite),
        Cint, (Cint,),
        thread_config)
end

This way a user could specify sqlite3_config_threads(SQLite.SQLITE_CONFIG_SERIALIZED) before opening the first database, which should allow sharing the DB across Julia threads.

Or, even better yet (in my opinion) is SQLite.jl attempting to set SQLITE_CONFIG_SERIALIZED when the SQLite module is first loaded. I don't see a downside - assuming that the cost of the mutex inside the SQLite code base is pretty minimal for single threaded applications.

[herzfeldd]

But I see now that my SQLite library was compiled with -DTHREADSAFE=1, suggesting that SERIALIZED was already selected as the default. So my issue is probably not with the SQLite library but the Julia SQLite.jl implementation. I have a feeling it is related to const GLOBAL_BUF = IOBuffer(), but I am unsure - will have to do some more digging.

I am happy to split this into two issues if that is easier to follow: 1) support for threading in SQLite.jl and 2) support for configuration of threading (or other configuration parameters) via sqlite3_config.

[herzfeldd]

Here is MWE (at least on my machine) that demonstrates a crash due to threading. Here Threads.nthreads() = 4. The SQLite library is compiled with THREADSAFE=1.

import SQLite
println(Threads.nthreads())

db = SQLite.DB("test.sqlite")
SQLite.execute(db, "CREATE TABLE test_table(test_column BLOB NOT NULL)")

Threads.@threads for i = 1:100
    # Generate a new random array and insert it into the database
    x = rand(10000)
    SQLite.execute(db, "INSERT INTO test_table(test_column) VALUES (?)", (x,))
end

Here's the stacktrace:

double free or corruption (!prev)

signal (6): Aborted
in expression starting at /tmp/mwe.jl:7
gsignal at /usr/bin/../lib/x86_64-linux-gnu/libc.so.6 (unknown line)
abort at /usr/bin/../lib/x86_64-linux-gnu/libc.so.6 (unknown line)
unknown function (ip: 0x7f3e0b3e2735)
unknown function (ip: 0x7f3e0b3eb08b)
unknown function (ip: 0x7f3e0b3ecf5b)
cfree at /usr/bin/../lib/x86_64-linux-gnu/libc.so.6 (unknown line)
unknown function (ip: 0x7f3e0b683b95)
unknown function (ip: 0x7f3e0b684780)
unknown function (ip: 0x7f3e0b6598e7)
unknown function (ip: 0x7f3e0b65a442)
_growend! at ./array.jl:892 [inlined]
resize! at ./array.jl:1085 [inlined]
take! at ./iobuffer.jl:395
sqlserialize at /home/herzfeldd/.julia/packages/SQLite/jf6KT/src/SQLite.jl:318
bind! at /home/herzfeldd/.julia/packages/SQLite/jf6KT/src/SQLite.jl:321 [inlined]
bind! at /home/herzfeldd/.julia/packages/SQLite/jf6KT/src/SQLite.jl:273
execute at /home/herzfeldd/.julia/packages/SQLite/jf6KT/src/SQLite.jl:446
execute at /home/herzfeldd/.julia/packages/SQLite/jf6KT/src/SQLite.jl:467
macro expansion at /tmp/mwe.jl:10 [inlined]
#15#threadsfor_fun at ./threadingconstructs.jl:81
#15#threadsfor_fun at ./threadingconstructs.jl:48
unknown function (ip: 0x7f3de70edb4c)
unknown function (ip: 0x7f3e0b656d5b)
unknown function (ip: (nil))
Allocations: 6697439 (Pool: 6694176; Big: 3263); GC: 6
Aborted (core dumped)

So this indeed seems to be an issue with GLOBAL_BUF.

[herzfeldd]

Creating a new instance of IOBuffer() for every call of sqlserialize() seems to prevent the crash. For instance, the following works just fine:

function sqlserialize(x)
    buffer = IOBuffer()
    # deserialize will sometimes return a random object when called on an array
    # which has not been previously serialized, we can use this mutable struct to check
    # that the array has been serialized
    s = Serialized(x)
    Serialization.serialize(buffer, s)
    return take!(buffer)
end

Is there any reason for the original buffer to be declared outside the sqlserialize function? I imagine it is performance related...

Happy to submit a pull request if this is deemed an acceptable solution.