SQLite isolation clarification needed
Hi, I'm trying to better understand SQLite isolation guarantees. I'm reading https://sqlite.org/isolation.html and my understanding that transactions from the same connection should see each other updates, even uncommitted ones.
I've made a small Go program trying to exhibit this behaviour — I'm expecting that TX 2 will see uncommitted update from TX 1, but it does not: https://gist.github.com/andreyst/30322c7df8af6f4969445ab85f7dcc74
Can you help me understand what am I missing?
(2.1) By Keith Medcalf (kmedcalf) on 2021-02-14 10:11:07 edited from 2.0 in reply to 1 [link] [source]
This is a GO-SQLite3 wrapper behaviour. That is, the issue you are seeing is entirely a matter of the GO wrapper you are using to interface go with SQLite3.
Attempting to issue nested transactions in SQLite3 would throw an error. That is, if the connection were really in a transaction then attempting to start another transaction while one is already in progress would cause the library to return an error.
From this and the behaviour that you are observing it would appear that db.Begin is returning a new connection which lives for the duration of the transaction.
Determining why the go-sqlite3 wrapper is behaving the way it is can likely be best addressed by having recourse to the documentation for go and/or the wrapper being used.
Transaction state is an attribute of a connection.
A single connection can either be (a) not in a transaction or (b) in a transaction.
If something is leading you to believe that more that N transactions are active at the same time (concurrently) then this means that there are (at least) N connections active at the same time (concurrently).
Just use the sqlite3 executable for your experiments, leaving out any language specific drivers and wrappers.
Several transactions over the same connection cannot happen at the same time, only one after the other.
This is because
sql.DB represents a connection pool, not a single database connection. https://golang.org/pkg/database/sql/#DB
Basically, when you call
db.Begin the second time, the Go standard library creates a second connection to the database, because it recognizes that the first is still in use.
Thank you all for prompt replies. I've added logging to Go's stdlib and confirmed that this is really a database/sql behaviour to create more connections, as suggested by anonymous (5). Code in question doing this is here: https://golang.org/src/database/sql/sql.go?s=47934:48006#L1303.
Thank you Keith for explaining, now I understand the tx/connection mechanics a bit better.
Thanks Holger for suggesting working with bare sqlite, I'll keep that in mind with further experiments.