Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

database/sql: design problems: prepare statement should not be closed in Tx. #25329

Closed
zhujingfa opened this issue May 10, 2018 · 6 comments
Closed

Comments

@zhujingfa
Copy link

@zhujingfa zhujingfa commented May 10, 2018

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)?

GOARCH="amd64"
GOBIN=""
GOCACHE="/Users/user/Library/Caches/go-build"
GOEXE=""
GOHOSTARCH="amd64"
GOHOSTOS="darwin"
GOOS="darwin"
GOPATH="/Users/user/project/godev"
GORACE=""
GOROOT="/usr/local/Cellar/go/1.10/libexec"
GOTMPDIR=""
GOTOOLDIR="/usr/local/Cellar/go/1.10/libexec/pkg/tool/darwin_amd64"
GCCGO="gccgo"
CC="clang"
CXX="clang++"
CGO_ENABLED="1"
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=/var/folders/vv/r0zwbxw961z1rkn5d1jqfkn40000gp/T/go-build602896078=/tmp/go-build -gno-record-gcc-switches -fno-common"

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:

2018-05-09T18:59:25.107414+08:00	 1024 Connect	hanhui@172.17.0.1 on  using TCP/IP
2018-05-09T18:59:25.128305+08:00	 1024 Query	SHOW VARIABLES
2018-05-09T18:59:25.152043+08:00	 1024 Query	SELECT @@global.max_allowed_packet
2018-05-09T18:59:25.169777+08:00	 1024 Query	SET NAMES 'utf8'
2018-05-09T18:59:25.189769+08:00	 1024 Query	USE `test`
2018-05-09T18:59:25.206143+08:00	 1024 Query	SET NAMES 'utf8mb4'
2018-05-09T18:59:25.217702+08:00	 1024 Query	begin
2018-05-09T18:59:29.953976+08:00	 1024 Query	PREPARE stmt1 FROM ...
2018-05-09T18:59:29.954970+08:00	 1024 Prepare	select * from test where id=?
2018-05-09T19:00:05.250227+08:00	 1024 Query	set @id=1
2018-05-09T19:00:07.109937+08:00	 1024 Query	EXECUTE stmt1 USING @id
2018-05-09T19:00:07.110027+08:00	 1024 Execute	select * from test where id=1
2018-05-09T19:00:18.808496+08:00	 1024 Query	EXECUTE stmt1 USING @id
2018-05-09T19:00:18.808551+08:00	 1024 Execute	select * from test where id=1
2018-05-09T19:00:30.736630+08:00	 1024 Query	commit
2018-05-09T19:00:47.398908+08:00	 1024 Query	begin
2018-05-09T19:01:01.209588+08:00	 1024 Query	EXECUTE stmt1 USING @id
2018-05-09T19:01:01.209632+08:00	 1024 Execute	select * from test where id=1
2018-05-09T19:01:17.127860+08:00	 1024 Query	PREPARE stmt4 FROM ...
2018-05-09T19:01:17.127985+08:00	 1024 Prepare	insert into test set str=?
2018-05-09T19:01:18.575662+08:00	 1024 Query	set @str="TestInsert"
2018-05-09T19:01:20.075044+08:00	 1024 Query	EXECUTE stmt4 USING  @str
2018-05-09T19:01:20.075094+08:00	 1024 Execute	insert into test set str='TestInsert'
2018-05-09T19:01:51.233715+08:00	 1024 Query	SELECT * FROM `test` ORDER BY `id` LIMIT 0,1000
2018-05-09T19:01:51.691804+08:00	 1024 Query	SELECT * FROM `test` ORDER BY `id` DESC LIMIT 0,1000
2018-05-09T19:02:44.514265+08:00	 1024 Query	commit
2018-05-09T19:02:53.349313+08:00	 1024 Query	EXECUTE stmt4 USING  @str
2018-05-09T19:02:53.349408+08:00	 1024 Execute	insert into test set str='TestInsert'

database/sql test

scheme:
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4;

gengeral: when use sql.Tx, statement close after commit. (*sql.Tx).Commit() call tx.closePrepared() closed the active statement。
//2018-05-10T14:49:28.151255+08:00	 1532 Connect	hanhui@172.17.0.1 on test using TCP/IP
//2018-05-10T14:49:28.312583+08:00	 1532 Query	SET NAMES utf8mb4
//2018-05-10T14:49:28.343319+08:00	 1532 Prepare	SELECT id, str FROM test WHERE id = ? AND str = ? /*raw*/
//2018-05-10T14:49:28.358819+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 1 AND str = 22 /*raw*/
//2018-05-10T14:49:28.391105+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 2 AND str = 333 /*raw*/
//2018-05-10T14:49:28.403455+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*raw*/
//2018-05-10T14:49:28.513211+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 2 AND str = 333 /*raw*/
//2018-05-10T14:49:28.525539+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*raw*/
//2018-05-10T14:49:28.617063+08:00	 1532 Prepare	insert into test set str=? /*raw*/
//2018-05-10T14:49:28.629238+08:00	 1532 Execute	insert into test set str='2018-05-10 06:49:28.695629' /*raw*/
//2018-05-10T14:49:28.652475+08:00	 1532 Execute	insert into test set str='2018-05-10 06:49:28.719639' /*raw*/
//2018-05-10T14:49:28.685605+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*raw*/
//2018-05-10T14:49:28.699858+08:00	 1532 Query	START TRANSACTION
//2018-05-10T14:49:28.746087+08:00	 1533 Connect	hanhui@172.17.0.1 on test using TCP/IP
//2018-05-10T14:49:28.768790+08:00	 1533 Query	SET NAMES utf8mb4
//2018-05-10T14:49:28.788253+08:00	 1533 Prepare	SELECT id, str FROM test WHERE id = ? AND str = ? /*trx*/
//2018-05-10T14:49:28.801088+08:00	 1533 Execute	SELECT id, str FROM test WHERE id = 2 AND str = 333 /*trx*/
//2018-05-10T14:49:28.823709+08:00	 1533 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*trx*/
//2018-05-10T14:49:28.857180+08:00	 1533 Prepare	insert into test set str=? /*trx*/
//2018-05-10T14:49:28.949148+08:00	 1533 Execute	insert into test set str='2018-05-10 06:49:28.93496' /*trx*/
//2018-05-10T14:49:28.973655+08:00	 1533 Execute	insert into test set str='2018-05-10 06:49:29.039583' /*trx*/
//2018-05-10T14:49:29.012212+08:00	 1533 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*trx*/
//2018-05-10T14:49:29.022579+08:00	 1532 Query	COMMIT
//2018-05-10T14:49:29.047331+08:00	 1533 Query	START TRANSACTION
//2018-05-10T14:49:29.078750+08:00	 1533 Prepare	SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-split*/
//2018-05-10T14:49:29.091124+08:00	 1533 Execute	SELECT id, str FROM test WHERE id = 2 AND str = 333 /*trx-split*/
//2018-05-10T14:49:29.101032+08:00	 1533 Query	COMMIT
//2018-05-10T14:49:29.105843+08:00	 1533 Close stmt
//2018-05-10T14:49:29.106093+08:00	 1532 Query	START TRANSACTION
//2018-05-10T14:49:29.116525+08:00	 1532 Query	ROLLBACK
//2018-05-10T14:49:29.125332+08:00	 1533 Query	START TRANSACTION
//2018-05-10T14:49:29.133869+08:00	 1533 Prepare	insert into test set str=? /*trx-split*/
//2018-05-10T14:49:29.142936+08:00	 1533 Execute	insert into test set str='2018-05-10 06:49:29.207843' /*trx-split*/
//2018-05-10T14:49:29.156414+08:00	 1533 Execute	insert into test set str='2018-05-10 06:49:29.216836' /*trx-split*/
//2018-05-10T14:49:29.166392+08:00	 1533 Query	COMMIT
//2018-05-10T14:49:29.201274+08:00	 1533 Close stmt
//2018-05-10T14:49:29.201426+08:00	 1532 Query	START TRANSACTION
//2018-05-10T14:49:29.225007+08:00	 1532 Prepare	SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-split-new*/
//2018-05-10T14:49:29.235124+08:00	 1532 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*trx-split-new*/
//2018-05-10T14:49:29.246691+08:00	 1532 Query	COMMIT
//2018-05-10T14:49:29.263936+08:00	 1532 Close stmt

test code
func TestMysqlRawDriverPrepareTest(t *testing.T) {
	eng, err := sql.Open("mysql", "dbsource")
	if err!=nil {
		t.Fatalf("database.GetDbEngine(`mysql`): %v", err.Error())
	}

	if err := eng.Ping(); err != nil {
		t.Fatal(err)
	}

	funcRawTestPrepare(t, eng)

	funcRawTestEngineQueryTrxPrepare(t, eng)

	funcRawSplitTestEngineQueryTrxPrepare(t, eng)
}

func funcRawTestPrepare(t *testing.T, eng *sql.DB){
	t.Logf("Start funcRawTestPrepare...")
	//res, err:=eng.Exec("-- golang prepare test\nSELECT id, str FROM test WHERE id = ? AND str = ?", 1, 22)
	stmt, err:=eng.Prepare("SELECT id, str FROM test WHERE id = ? AND str = ? /*raw*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err:=stmt.Exec(1, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err:=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	res, err=stmt.Exec(2, 333)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	t.Logf("eng.Exec: result %v %v", aff, err)

	res, err=stmt.Exec(2, 333)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	stmt1, err:=eng.Prepare("insert into test set str=? /*raw*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)

	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)
}


func funcRawTestEngineQueryTrxPrepare(t *testing.T, eng *sql.DB){
	t.Logf("Start funcRawTestEngineQueryTrxPrepare...")
	tx, err := eng.Begin()
	if err != nil {
		t.Fatal(err)
	}
	defer tx.Commit()

	stmt, err:=eng.Prepare("SELECT id, str FROM test WHERE id = ? AND str = ? /*trx*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err:=stmt.Exec(2, 333)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err:=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	stmt1, err:=eng.Prepare("insert into test set str=? /*trx*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)

	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)
}

func funcRawSplitTestEngineQueryTrxPrepare(t *testing.T, eng *sql.DB){
	t.Logf("Start funcRawSplitTestEngineQueryTrxPrepare...")
	tx, err := eng.Begin()
	if err != nil {
		t.Fatal(err)
	}

	stmt, err:=tx.Prepare("SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-split*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err:=stmt.Exec(2, 333)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err:=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	tx.Commit()

	//开启一个新的事务
	tx, err = eng.Begin()
	if err != nil {
		t.Fatal(err)
	}

	//(*sql.Tx).Commit()调用tx.closePrepared()关闭了活跃的statement。
	res, err=stmt.Exec(4, 22)
	if err==nil || err.Error()!="sql: statement is closed" {
		//go_mysql_driver_raw_prepare_test.go:175: eng.Exec: sql: statement is closed
		t.Fatalf("eng.Exec: error must be sql: statement is closed, got %v", err.Error())
	}
	//rollback for error.
	tx.Rollback()

	tx, err = eng.Begin()
	if err != nil {
		t.Fatal(err)
	}
	stmt1, err:=tx.Prepare("insert into test set str=? /*trx-split*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}

	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)

	tx.Commit()


	tx, err = eng.Begin()
	if err != nil {
		t.Fatal(err)
	}
	//重新prepare
	stmt, err=tx.Prepare("SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-split-new*/")
	if err != nil {
		t.Fatal(err)
	}
	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	tx.Commit()
}

@agnivade agnivade added this to the Go1.11 milestone May 10, 2018
@agnivade
Copy link
Contributor

@agnivade agnivade commented May 10, 2018

@nussjustin
Copy link
Contributor

@nussjustin nussjustin commented May 10, 2018

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?

@urandom
Copy link

@urandom urandom commented May 10, 2018

@nussjustin The documentation states the following:

The returned statement operates within the transaction and will be closed when the transaction has been committed or rolled back.

@nussjustin
Copy link
Contributor

@nussjustin nussjustin commented May 10, 2018

@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:

2018-05-10T13:30:24.853929Z        12 Connect   root@localhost on test using TCP/IP
2018-05-10T13:30:24.854452Z        12 Prepare   INSERT INTO bla (id) VALUES (?) ON DUPLICATE KEY UPDATE id = VALUES(id)
2018-05-10T13:30:24.854531Z        12 Query     START TRANSACTION
2018-05-10T13:30:24.855038Z        13 Connect   root@localhost on test using TCP/IP
2018-05-10T13:30:24.855212Z        13 Query     START TRANSACTION
2018-05-10T13:30:24.855444Z        13 Prepare   INSERT INTO bla (id) VALUES (?) ON DUPLICATE KEY UPDATE id = VALUES(id)
2018-05-10T13:30:24.855536Z        13 Execute   INSERT INTO bla (id) VALUES (1) ON DUPLICATE KEY UPDATE id = VALUES(id)
2018-05-10T13:30:24.855722Z        13 Query     COMMIT
2018-05-10T13:30:24.855854Z        13 Query     START TRANSACTION
2018-05-10T13:30:24.855961Z        13 Execute   INSERT INTO bla (id) VALUES (2) ON DUPLICATE KEY UPDATE id = VALUES(id)
2018-05-10T13:30:24.856102Z        13 Query     COMMIT
2018-05-10T13:30:24.856213Z        13 Query     START TRANSACTION
2018-05-10T13:30:24.856311Z        13 Execute   INSERT INTO bla (id) VALUES (3) ON DUPLICATE KEY UPDATE id = VALUES(id)
2018-05-10T13:30:24.856448Z        13 Query     COMMIT
2018-05-10T13:30:24.856559Z        13 Close stmt
2018-05-10T13:30:24.856615Z        13 Quit

The statement is only prepared twice: Once on the initial Prepare() call and then once on the new connection.

@zhujingfa
Copy link
Author

@zhujingfa zhujingfa commented May 16, 2018

@nussjustin If every trx has 10 sqls, this would be a sensible waste. This usage is not my perfered.
After read code, I found that I can fetch consist Conn use DB.Conn method.

Then I hack use this code works well:

General log

2018-05-16T19:01:58.598926+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.609667+08:00	15800 Query	DELETE FROM test
2018-05-16T19:01:58.616787+08:00	15800 Query	INSERT INTO test (id, str) VALUES ('1', '22')
2018-05-16T19:01:58.625222+08:00	15800 Query	COMMIT
2018-05-16T19:01:58.648731+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.655883+08:00	15800 Query	INSERT INTO test (id, str) VALUES ('2', '22')
2018-05-16T19:01:58.664995+08:00	15800 Query	INSERT INTO test (id, str) VALUES ('1', '22')
2018-05-16T19:01:58.672376+08:00	15800 Query	ROLLBACK
2018-05-16T19:01:58.686763+08:00	15800 Query	select count(1) as count from test
2018-05-16T19:01:58.698408+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.704976+08:00	15800 Prepare	SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-conn*/
2018-05-16T19:01:58.713831+08:00	15800 Execute	SELECT id, str FROM test WHERE id = 2 AND str = 333 /*trx-conn*/
2018-05-16T19:01:58.725700+08:00	15800 Query	COMMIT
2018-05-16T19:01:58.739530+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.753022+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.779967+08:00	15800 Prepare	insert into test set str=? /*trx-conn*/
2018-05-16T19:01:58.794428+08:00	15800 Execute	insert into test set str='2018-05-16 11:01:58.833674' /*trx-conn*/
2018-05-16T19:01:58.804008+08:00	15800 Execute	insert into test set str='2018-05-16 11:01:58.84327' /*trx-conn*/
2018-05-16T19:01:58.815794+08:00	15800 Query	COMMIT
2018-05-16T19:01:58.843343+08:00	15800 Query	START TRANSACTION
2018-05-16T19:01:58.851404+08:00	15800 Execute	SELECT id, str FROM test WHERE id = 4 AND str = 22 /*trx-conn*/
2018-05-16T19:01:58.859161+08:00	15800 Query	COMMIT
2018-05-16T19:01:58.871078+08:00	15800 Close stmt
2018-05-16T19:01:58.871232+08:00	15800 Close stmt
func funcRawConnTestEngineQueryTrxPrepare(t *testing.T, eng *sql.DB){
	t.Logf("Start funcRawConnTestEngineQueryTrxPrepare...")

	conn, err:=eng.Conn(context.Background())
	if err != nil {
		t.Fatal(err)
	}

	tx, err := conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}
	conn.ExecContext(context.Background(), `DELETE FROM test`)
	res, err:=conn.ExecContext(context.Background(), `INSERT INTO test (id, str) VALUES ('1', '22');`)
	if err!=nil {
		tx.Rollback()
	}else{
		tx.Commit()
	}

	tx, err = conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}
	//No error
	res, err=conn.ExecContext(context.Background(), `INSERT INTO test (id, str) VALUES ('2', '22');`)
	if err!=nil {
		tx.Rollback()
		t.Logf("Error Found: conn.ExecContext %v, rollbacked.", err.Error())
	}
	//Must be error
	res, err=conn.ExecContext(context.Background(), `INSERT INTO test (id, str) VALUES ('1', '22');`)
	if err!=nil {
		tx.Rollback()
		t.Logf("Error Found: conn.ExecContext %v, rollbacked.", err.Error())
	}else{
		t.Fatalf("conn.ExecContext Must be error.")
	}
	rows, err:=conn.QueryContext(context.Background(), `select count(1) as count from test`)
	if err!=nil {
		t.Fatalf("Error Found: conn.QueryContext %v.", err.Error())
	}else{
		defer rows.Close()
		for rows.Next() {
			var count int
			err = rows.Scan(&count)
			if err!=nil {
				t.Fatalf("Error Found: rows.Scan %v.", err.Error())
			}else{
				t.Logf("Found rows: %v", count)
				if count!=1 {
					t.Fatalf("Error Found: after trx, there must be 1 row, got %v.", count)
				}
			}
		}
	}


	tx, err = conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}

	stmt, err:=conn.PrepareContext(context.Background(),"SELECT id, str FROM test WHERE id = ? AND str = ? /*trx-conn*/")
	if err != nil {
		t.Fatal(err)
	}
	defer stmt.Close()
	res, err=stmt.Exec(2, 333)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err:=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	tx.Commit()

	//开启一个新的事务
	tx, err= conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}

	tx, err= conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}
	stmt1, err:=conn.PrepareContext(context.Background(),"insert into test set str=? /*trx-conn*/")
	if err != nil {
		t.Fatal(err)
	}
	defer stmt1.Close()
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}

	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)
	res, err=stmt1.Exec(time.Now())
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec Insert: result %v %v", aff, err)

	tx.Commit()


	tx, err= conn.BeginTx(context.Background(),nil)
	if err != nil {
		t.Fatal(err)
	}
	res, err=stmt.Exec(4, 22)
	if err!=nil {
		t.Fatalf("eng.Exec: %v", err.Error())
	}
	aff, err=res.RowsAffected()
	t.Logf("eng.Exec: result %v %v", aff, err)

	tx.Commit()
}

@ianlancetaylor
Copy link
Contributor

@ianlancetaylor ianlancetaylor commented Jun 23, 2018

It sounds like there is nothing to do here. Please comment if you disagree.

@golang golang locked and limited conversation to collaborators Jun 23, 2019
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
6 participants
You can’t perform that action at this time.