Concurrent queries in a single transaction

[Ain]

Hi

It is my understanding that DB Tx is generally not safe to use concurrently, ie consider following code:

    tx, err := db.Begin()
    defer tx.Rollback()
    ...
    statements[0], err := tx.Prepare("...")
    defer s1.Close()
    statements[1], err := tx.Prepare("...")
    defer s2.Close()
    ...
    wg.Add(len(statements))
    for x := range statements {
        go func(x int) {
            defer wg.Done()
            statements[x].QueryRow(...).Scan(...)
        }(x)
    }
    wg.Wait()

Here we prepare bunch of statements for a Tx which are "independent of each other", ie query different tables and scan to different variables etc. However they can't be executed concurrently, in a goroutine, as they share the same connection and thus interfere with each other?

It might be safe when db driver takes care to serialise the statements but database/sql doesn't guarantee safe usage for given secenario.
Is that correct?

So how would you go about executing multiple queries in the same transaction context concurrenty?


TIA
ain

[dwa...@gmail.com]

Hello,

On Wednesday, June 27, 2018 at 7:19:27 AM UTC-5, Ain wrote:

So how would you go about executing multiple queries in the same transaction context concurrenty?

As far as I know, you can't. Tx isn't marked as threadsafe because it doesn't make sense to use it concurrently in most implementations.

A transaction is scoped to a connection, which typically corresponds to something like a network socket. Generally, that means the server is reading queries from the socket one at a time, and sending a response to each before processing the next query. In principle, it would be possible to pipeline multiple concurrent requests over the same socket (along the lines of HTTP/2) but the databases that I'm aware of (MySQL and PostgreSQL) don't support this, because it would complicate the protocol and the server implementation. So there's no point in supporting this behavior in the client library.

Similarly, SQLite doesn't use network sockets, but its idea of a "connection" is a data structure that isn't safe for concurrent access. You must either guarantee that your code doesn't access the same connection concurrently from multiple threads, or you can configure SQLite to run in "serialized" mode which enforces that behavior for you with mutexes.

Is there a reason you can't execute your queries on separate connections?

[Ain]

Executing queries on separate connections means one can't rollback these as one.

I'm exportig data from csv to DB and to insert one "master record" (basically bunch of FKs to other tables) several other tables must be checked for a value, if there isn't one then that "helper table" must be updated. To speed it up I thought to run the "check does the value exists in helper table, if not insert" parts concurrently. But it seems taht it can't be done in single Tx... I'm probably going to test with separate connections anyway, just to see would it actually speed thing up.
Another thing to check out would be the multiple resultset feature (https://golang.org/pkg/database/sql/#example_DB_Query_multipleResultSets) but this requires implementig the program flow in a different way...

I think today it makes perfect sense that one would want to execute multiple queries in single Tx concurrently but most major RDBMS are from the era when that wasn't the case and thus their implemantation made corresponding design choices.


ain

[Glen Newton]

When I do things like this (load a DB with tables with foreign keys) I use maps of the keys for the foreign tables. This will work if your DB is not huge and generates huge maps in your application, and has the benefit of being much faster than a DB query...

glen

[Space A.]

Hi,

DB in common case will do lock/unlock when necessary for you implicitly. It is safe to call queries simultaneously. Moreover it is general approach in Enterprise solutions to have let's say hundreds of open connections sockets towards DB and execute statements from hundreds of threads. However to answer your question accurate you need to look at underlying implementation. As I understand, most of Go DB implementations don't even have connection pool capabilities. So they are even "safer" from that point because single network socket will become additional (and unnecessary) "serialization" mechanism. You could probably try to implement connection pool by yourself.

Regards,

среда, 27 июня 2018 г., 15:19:27 UTC+3 пользователь Ain написал:

[nilsocket]

Does any solution exist for this problem?

I'm facing similar problem, want to run bunch of queries inside a transaction,

but want to run them concurrently, as running sequential is taking more than 1 second.

[Robert Engels]

As you state, the driver (and db) need to support this. The synchronization for something like this is internal. 

But, if the operations are modifying the same tables you may end up with serialized operations anyway in order to ensure consistency while avoiding deadlocks. 

On Apr 20, 2024, at 12:11 AM, nilsocket <nils...@gmail.com> wrote:

Does any solution exist for this problem?

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/4c798067-55cd-4d20-9eba-875c4743ce73n%40googlegroups.com.