database/sql: design problems: prepare statement should not be closed in Tx. #25329
Labels
FrozenDueToAge
NeedsInvestigation
Someone must examine and confirm this is a valid issue and not a duplicate of an existing one.
Milestone
Comments
agnivade
added
the
NeedsInvestigation
|
/cc @kardianos |
|
You can use (*Tx).Stmt to reuse an existing *Stmt in a transaction. If the statement was already prepared on the transactions connection it won't be prepared again. Is this what you want? |
|
@nussjustin The documentation states the following:
|
|
@urandom From looking at the code for *Stmt.Close it looks like prepared statements that are created from (*Tx).Stmt (parentStmt != nil) won't be closed. I tested this with the following program: package main
import (
"database/sql"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxIdleConns(1)
if err := db.Ping(); err != nil {
log.Fatal(err)
}
stmt, err := db.Prepare("INSERT INTO bla (id) VALUES (?) ON DUPLICATE KEY UPDATE id = VALUES(id)")
if err != nil {
log.Fatal(err)
}
// take the connection on which stmt was prepared so the first doTx has to create a new connection and reprepare the stmt
if _, err := db.Begin(); err != nil {
log.Fatal(err)
}
db.SetMaxIdleConns(1)
db.SetMaxOpenConns(2)
doTx(db, stmt, 1)
doTx(db, stmt, 2)
doTx(db, stmt, 3)
}
func doTx(db *sql.DB, stmt *sql.Stmt, i int) {
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Commit()
txstmt := tx.Stmt(stmt)
if _, err := txstmt.Exec(i); err != nil {
log.Fatal(err)
}
}The whole query log: The statement is only prepared twice: Once on the initial Prepare() call and then once on the new connection. |
|
@nussjustin If every trx has 10 sqls, this would be a sensible waste. This usage is not my perfered. Then I hack use this code works well: General log |
|
It sounds like there is nothing to do here. Please comment if you disagree. |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
What did you expect to see?
I pick it up to here, so guys can find my emphasis.
The prepared statement should be reused beyond Tx instance. The basic problem is the Tx inner conn is not fixed. Now database/sql tx arch is DB->Tx->Fetch conn，this is a problem, the ideal arch is DB->connPool->fetchConn->Tx arch. The Tx and prepared statement source should bound to conn source, not Tx instance.
why need this: Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. from postgresql doc, if bound to Tx, I this this may be a misuse.
What version of Go are you using (
go version)?go version go1.10 darwin/amd64
Does this issue reproduce with the latest release?
latest now.
What operating system and processor architecture are you using (
go env)?What did you do?
Test database/sql driver mysql github.com/go-sql-driver/mysql with prepare statement.
What did you expect to see?
The prepared statement should be reused beyond Tx instance. The basic problem is the Tx inner conn is not fixed. Now database/sql tx arch is DB->Tx->Fetch conn，this is a problem, the ideal arch is DB->connPool->fetchConn->Tx arch. The Tx and prepared statement source should bound to conn source, not Tx instance.
What did you see instead?
prepare statement can not shared between transaction, but mysql / postgresql all can.
This is mysql general log:
use mysql sql raw string cmd:
database/sql test
The text was updated successfully, but these errors were encountered: