![]() ![]() It seems like this is expected behaviour when using sqlite in a multi-threaded context. I think there is no proper solution where we can use multiple sql.DB connections in combination with sqlite3 in Go without handling "database is locked" by retrying or by incrementing the busy_timeout. To update the conclusion at the top of the original version of this comment: Using one connection across multiple goroutines by using db.SetMaxOpenConns(1) and letting database/sql synchronize access to the shared connection with its mutexes might cause problems, since connections in database/sql are stateful, which means that a db.Begin() in one goroutine effects other goroutines. Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle SQLITE_BUSY appropriately. Make sure that each thread opens the database file and keeps its own sqlite structure. Make sure you're compiling SQLite with -DTHREADSAFE=1. The conclusion on the wiki page is as follows: This sqlite wiki entry also shows how sqlite behaves when used in multiple threads with multiple database instances: a lot of SQLITE_BUSY/ "database is locked" errors. Apparently that is the recommended way to go. ![]() I found some sample code that shows usage of multiple db instances across multiple threads. If anything of what I just said is wrong, please feel free to point it out.īasically everything I said above is wrong.Īpparently what I said about "thread-safety guarantees" only in "shared db instance across threads" is wrong. So I'd say that the only solution is to use db.SetMaxOpenConns(1) (or use the busy timeout). If multiple clients (processes, goroutines with their own connection) use the same database file, the synchronization has to happen through the database file, in which case it's locked completely for every write action. ) is that sqlite3 synchronizes access to the database file on the client-side, which is only possible if multiple threads/goroutines use the same client. The difference between sqlite and client/server databases (PostgreSQL, MySQL. Other processes just wait on the writer to finish then continue about their business. But that normally only takes a few milliseconds. When any process wants to write, it must lock the entire database file for the duration of its update. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. We're now essentially in "multiple processes accessing the same file"-land. Now each connection has to do its own synchronization and they have to synchronise with the the other connections. If we don't limit the amount of sql.DB connections (which is the database/sql default), we create multiple sqlite3 *db instances in our program.sqlite can then coordinate access to the database in its own functions. The thread-safety guarantees sqlite makes only cover the case where you have one sqlite3 *db in your program and access it from different threads.After some more reading and investigating, I think there is no proper solution where we can use multiple sql.DB connections in combination with sqlite3. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |