Goroutine-safe wrapper for sqlite3?

Question

go

Help to deal with multi-threaded access to the database.
I can't understand the point. I ask for a code example.
As I understand it, with the help of sql.Open I get a receiving object, through which I can read from the database without restrictions and write it in any gorutine. But when the moment comes to change the same value or read something that should change ... I googled what is possible through mutex, but isn’t mutex, for example, through db.Exec, I block the pull itself (connection object)? I can not understand how to organize a record, a change with a mutex. Let's say there are 4 tables in the database. The server reads from three tables and sends the data to the client. Another client makes a request that changes the data of a certain field in 2 tables. The third client also makes a request that will change the data in 1 out of 4 tables ... How can I actually use this mutex with sqlite3

Answer 1

If you work with sql, data consistency is guaranteed and provided by the database engine through the transaction mechanism. Transactions are reflected in the database/sql package from the standard library https://golang.org/pkg/database/sql/#Tx There is no need to do anything special at the language level, just do not refer to one transaction from several goroutines.

Answer 2

You can limit the maximum number of connections to 1:
db.SetMaxOpenConns(1)
This will cause the read requests to stop running in parallel - so it may be suitable if you have "nimble" requests.
The advantage is that there will be no need to change anything in the code, no mutexes.
But since you have few write places in the database, you can also use mutexes - you are unlikely to miss something.
If there were many places for changing the database, then you can also consider a wrapper over *sql.DB, in which the mutex is automatically used for all "suspicious" methods - for example, Exec methods (based on the assumption that they usually change the database) or Query methods if the query has an insert, update or delete.
Here is another link on the topic:https://stackoverflow.com/questions/35804884/sqlit...