in-memory database not threadsafe #4459

[joel-rieke]

Please see below for the concurrency issue.

This exception happened intermittently while performing a combinations of SELECT/INSERT/UPDATES on a single table with many rows...

Looking at the code however, it is clear to me why this happened.

I tried fixing this by applying a table level lock around the partition access, but this seems heavy handed. I think there could be a more elegant approach to solving this.

**This was due to a classic insert, but many simultaneous inserts:
Something like:
INSERT INTO tableA (columnP, columnQ, columnR, columnS) VALUES (?, ?, ?, ?)

tablename and column names and counts changed obviously.**

============================

"concurrent map read and map write"
Stack:
2 0x000000000117e905 in github.com/dolthub/go-mysql-server/memory.partitionssort.Less
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/memory/table.go:1523
3 0x000000000118fc3c in github.com/dolthub/go-mysql-server/memory.(*partitionssort).Less
at :1
4 0x00000000004d4318 in sort.partialInsertionSort
at /usr/local/go/src/sort/zsortinterface.go:202
5 0x00000000004d3b29 in sort.pdqsort
at /usr/local/go/src/sort/zsortinterface.go:101
6 0x00000000004d070e in sort.Sort
at /usr/local/go/src/sort/sort.go:48
7 0x000000000117e23c in github.com/dolthub/go-mysql-server/memory.(*Table).sortRows
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/memory/table.go:1502
8 0x00000000011859d1 in github.com/dolthub/go-mysql-server/memory.(*pkTableEditAccumulator).ApplyEdits
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/memory/table_editor.go:460
9 0x00000000011819e2 in github.com/dolthub/go-mysql-server/memory.(*tableEditor).StatementComplete
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/memory/table_editor.go:101
10 0x0000000001181493 in github.com/dolthub/go-mysql-server/memory.(*tableEditor).Close
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/memory/table_editor.go:76
11 0x00000000013cf20b in github.com/dolthub/go-mysql-server/sql/plan.(*insertIter).Close
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/insert.go:508
12 0x000000000143d28a in github.com/dolthub/go-mysql-server/sql/plan.checkpointingTableEditorIter.Close
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/table_editor.go:118
13 0x000000000148e99d in github.com/dolthub/go-mysql-server/sql/plan.(*checkpointingTableEditorIter).Close
at :1
14 0x0000000001409f4e in github.com/dolthub/go-mysql-server/sql/plan.(*accumulatorIter).Next.func2
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/row_update_accumulator.go:322
16 0x0000000001409bc6 in github.com/dolthub/go-mysql-server/sql/plan.(*accumulatorIter).Next
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/row_update_accumulator.go:360
17 0x00000000014437c8 in github.com/dolthub/go-mysql-server/sql/plan.transactionCommittingIter.Next
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/transaction_committing_iter.go:106
18 0x000000000148fa0d in github.com/dolthub/go-mysql-server/sql/plan.(*transactionCommittingIter).Next
at :1
19 0x00000000013f3502 in github.com/dolthub/go-mysql-server/sql/plan.(*trackedRowIter).Next
at /go/pkg/mod/github.com/trimble-oss/go-mysql-server@v0.12.0-1.4/sql/plan/process.go:381

[zachmu]

Yes, unfortunately the in-memory database is not completely threadsafe right now. We have this on our roadmap to address, but it's a bit of work to accomplish.

[joel-rieke]

RWLock might be a good option in table.go layer.

[joel-rieke]

SELECT FOR UPDATE would also be nice if anyone is in this layer implementing.

[kom0055]

concurrent map writes
it also happened in a very high frequency in v0.17.0
github.com/dolthub/go-mysql-server@v0.17.0/memory/table_editor.go:424

[srabraham]

Hi there, could a project member (e.g. @zachmu) clarify how this package should be used to not hit race conditions?

The main README says "To avoid concurrency issues, limit DDL and DML statements (CREATE TABLE, INSERT, etc.) to a single goroutine."
Is that equivalent to saying that if someone is using many goroutines, that those goroutines must serialize their statement executions? For example, if I create one shared sync.Mutex and require that every statement be executed (and results fully returned) while locking that Mutex, would that suffice?

EDIT: I'm now finding locally that it seems to suffice to just set db.SetMaxOpenConns(1) on my *sql.DB.