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/[email protected]/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/[email protected]/memory/table.go:1502
8 0x00000000011859d1 in github.com/dolthub/go-mysql-server/memory.(*pkTableEditAccumulator).ApplyEdits
at /go/pkg/mod/github.com/trimble-oss/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/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/[email protected]/memory/table_editor.go:424