Absolute lack of thread-safety `SqlBackend` should be documented.

[merijn]

Right now SqlBackend has almost no documentation, there should be some big, bright warning saying "No way, no how can you ever under any conditions assume that using a single SqlBackend is remotely safe to use from separate threads"

I've been bitten by cryptic errors in the past and only NOW by browsing the source do I realise how utterly unsafe SqlBackend is in this regard.

[parsonsmatt]

Dang. That's a surprising thing to hear! thanks for reporting it.

[merijn]

@parsonsmatt the issue boils down most (all?) query functions being build on top of rawQueryRes, which in turn uses getStmtConn, which as you can see maintains a map of Statement in an IORef indexed by the query text. This means that all queries with the same text will map to a single Statement that is used for stepping and preparing. This means if any threads ever run the same query they will interfere with each other's preparation and stepping and generally just cause arbitrary failures due to racing eachother.

[merijn]

I'm actually somewhat baffled what prompted this design in the first place...

[parsonsmatt]

Hell, you're entirely right.

I think I get the intent - we want to prepare a statement once, then execute it multiple times. But sharing it across multiple threads is problematic.

Gating access behind an MVar or TVar would introduce locking/blocking on queries, which also seems bad.

Changing it to Map (ThreadId, Text) (IORef Statement) would be a relatively easy change to make and should stop 'bad' access patterns:

- pure $ Map.lookup sqlText stmtMap
+ threadId <- myThreadId
+ pure $ Map.lookup (threadId, sqlText) stmtMap

This fixes the multi-threaded access problem, but it isn't obvious to me that this is "good" in the initial design goals for the SqlBackend to store a map of Statements.

Could aslo have:

- Map Sql (IORef Statement)
+ Map Sql (IO Statement)

where the IO action has a way of mapping the Statements to appropriate ThreadIds.

[merijn]

Ok, bad news first:

After having lain awake all night figuring out how to work around thisthought about it overnight, I have concluded things are even worse than I initially suggested. It's not just the current case that's broken, but also the single-threaded case is just nonsense. I have a large dataset that I stream through conduit via, e.g. selectSourceRes, somewhere further down my conduit pipeline I do some more queries lookups while processing elements. If any of these downstream components for some reason execute a query that was already running in an upstream conduit then everything is now broken, because they'll be racing the same Statement resulting in just garbage output.

This means your suggested fix 1) wouldn't actually fix it and 2) would trigger version bumps across the entire set of persistent packages, since SqlBackend is part of the public API.

Now, on to the good news:

I believe it's possible to implement a workaround that keeps the current types/API that should have negligible impact on current uses that are safe and minor performance impact (in the form of additional statement preparation/finalisation, but I think that generally shouldn't be that costly) on uses that are currently utterly broken and unsafe anyway.

[merijn]

Spoke too soon, since persistent exports almost all of its internals I think a version bump is unavoidable...

[merijn]

I think the right way to go is to change getStmtConn into return IO (Acquire Statement) instead of IO Statement, which removes the Statement from the map for the duration of the run, this causes concurrent identical statements to create a new Statement to use (now the concurrent queries don't conflict) and then have the Acquire finalizer insert the Statement into the map (if not there) or finalize it (if there's already a Statement in the map.

The happy path (i.e. code that's currently correct) will just keep reusing the same prepared statement from the map, whereas N concurrent queries with the same statement end up creating N-1 new prepared statements for the duration of their query. The duplicate prepared Statement is unavoidable so any slowdown is just a fact of life.

Alternatively, the Statement map could actually keep a pool of prepared Statement for concurrent use, but this seems like it'd be way too easy to leak lots of memory in the form of prepared statements and the cost of preparing multiple statements seems relatively small compared to leaking lots of memory.

Unfortunately a change like this will have a pretty large ripple effect across persistent since getStmtConn is passed to lots of different functions in the SqlBackend, which all will need their type adjusted.

On the upside this change should be fairly mechanical and simple.

[merijn]

Ok, so I actually did figure out how to make a backwards compatible fix to getStmtConn only to have that idea torpedoed in the last second, because I just realised that insert (for SQLite, MySQL uses a similar approach..) relies on last_inserted_rowid() to work, which is unreliable/broken in concurrent usage.

So without a fix for that issue my fix is somewhat useless (well, it still fixes a whole bunch of issues, including running the same query multiple times in the same thread and concurrent non-insert operations).

[merijn]

This can probably be solved by extending InsertSqlResult to have a case that wraps the queries in a transaction that should presumably fix both the MySQL and SQLite case. Alternatively, we can just always wrap multi query inserts with a transaction, which is presumably fairly low cost.

[merijn]

I migrated my own code to use a Pool until I figure out if how to tackle the stuff I discussed here. (Which has in turn spawned #983 and #984, because I'm a busy beaver).

[TomMD]

So can this issue now be closed thanks to 983 and 984? I see #917 but am not clear on how that relates.

[merijn]

Eh, no, this is very much not solved. 983 and 984 just exist so you can more easily use 1 connection per thread with the RawSqlite database. It's still completely unsound to use the same connection from multiple threads.

#917 is the exact error you get when you try to use the same persistent-sqlite SqlBackend from multiple threads concurrently.