SQLite Forum

SQLite and Concurrency Bug?
Login

SQLite and Concurrency Bug?

(1) By henry (hankas) on 2020-12-04 07:19:32 [source]

Hi,

I am not sure if this is a bug or just my misunderstanding about how SQLite works.

I use SQLite as a test database during development. I open the SQLite with a blank database name and my understanding is that it defaults to using a temporary file to store the database.

Now I have two threads. Well, technically they are not threads. I use Go and they are actually goroutines which get multiplexed into the underlying thread pool. For simplicity sake, I will just refer to them as threads. One thread creates a transaction from the connection and starts doing its own stuffs. The other accesses the connection directly and also does its own stuffs.

I don't synchronize my database access because I assume that SQLite would have taken care of this.

My understanding is that as the first thread is working with its transaction, the second thread should block until the first thread is done with its transaction. Apparently, that is not the case. What happens is that as the first thread is working with its transaction, the second thread does not block and instead it returns an error: no such table. The error occurs because the first thread has not finished with its transaction. Once I put additional synchronization code into my database access, the error disappears and things work correctly.

My question is: is this a bug or just my misunderstanding about how SQLite works? If this is a bug, is it SQLite bug or the Go driver for SQLite bug?

Thanks

Henry

(2) By Richard Hipp (drh) on 2020-12-04 13:05:38 in reply to 1 [link] [source]

[While] the first thread is working with its transaction, the second thread should block until the first thread is done with its transaction.

I don't know how Go is suppose to work, but it would surprise me if it did work that way. Why would the second thread wait on the first thread? How does the second thread even know that the first thread exists?

Perhaps you are confused and are thinking that because you have two threads you also have two separate database connections? You do not. You have a single database connection (because you are using a temporary database and a temporary database can only have a single connection). How is this one database connection suppose to discern that it should only accept commands from one particular thread?

If you want the database engine to provide serialization, then create a real (non-temporary) database file and open separate connections to that one database, one from each thread. The separate connections will know about each other and will serialize writes for you, automatically.

(4) By henry (hankas) on 2020-12-04 15:28:28 in reply to 2 [link] [source]

Thanks for the reply.

I finally understand what the actual problem is. It appears that this is also related to the driver specific behavior.

The expected behavior for Go database driver is that when you open a connection to a database, what you get is actually a pool of connections. In my earlier post, I mistakenly referred to this pool as "connection" whereas it should be "a pool of connections". In Go, this pool is transparent to the user that it is normal to think of it as just a connection. The pool is concurrent safe and any query is automatically mapped onto the underlying connections. When you start a transaction, the connection the transaction is from will be busy until the transaction is committed or rolled back. Meanwhile, you can still do stuffs with the remaining connections in the pool.

When dealing with in-memory or temporary SQLite database and the connection is busy with work, the pool tries to establish a second connection. Your mentioning about temporary database accepting only a single connection gave me this clue. The pool ends up creating a new database and hence it cannot find such table because it accesses a brand-new database.

I have another question. Does shared-cache mode work with temporary database?

(5) By Richard Hipp (drh) on 2020-12-04 18:17:10 in reply to 4 [link] [source]

Shared-cache only works when you have two or more connections to the same database file. And you cannot have that with temporary databases. So, no it does not.

(6) By henry (hankas) on 2020-12-05 03:18:44 in reply to 5 [link] [source]

Thank you for taking the time to explain things to me. I have better understanding how to use SQLite now.

(3) By Keith Medcalf (kmedcalf) on 2020-12-04 13:37:15 in reply to 1 [link] [source]

The second thread obviously tried to access the table before the first thread created it. This is how multitasking works.