Concurrency insert in one transaction

[wisecsj]

Issue description

1. I know concurrency query in one transaction is not allowed,it will report error "busy buffer".But i want to confirm that is concurrency exec in one transaction is correct or allowed? I have done some tests and concurrency exec works fine

2. According to database/sql doc,
   
   But when i do concurrency query in one Stmt,the same error occurs:"busy buffer".

So,what's the correct way to use Stmt ...

Example code

	tx, err := db.Begin()
	if err != nil {
		t.Fatal(err)
	}
	defer tx.Rollback()
	stmt, err := tx.Prepare("select id from lnmp where id in (?,?,?,?,?,?,?)")
	if err != nil {
		t.Fatal(err)
	}
	defer stmt.Close()
	var wg sync.WaitGroup
	for i := 1; i < 10; i++ {
		i := i
		wg.Add(1)
		go func() {
			defer wg.Done()
			rows, err := stmt.Query(i, i+1, i+2, i+3,i+4,i+5,i+6)
			if err != nil {
				t.Logf("stmt query error:%v", err)
				return
			}
			defer rows.Close()
			for rows.Next() {
				var id int
				rows.Scan(&id)
			}
		}()
	}
	wg.Wait()

Configuration

Driver version (or git SHA):
1.4.0
Go version: run go version in your console
1.13
Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 5.7
Server OS: E.g. Debian 8.1 (Jessie), Windows 10
mac os 10.15.5

[shogo82148]

How did you get the "busy buffer" error?
show us your reproducible example code, please.

[wisecsj]

@shogo82148 sry, already update

[shogo82148]

sql.Tx is not safe for concurrent use by multiple goroutines because it is bound to a single connection.
You should use a sql.Stmt that is prepared on a sql.DB.

[methane]

When Stmt is created by DB.Prepare, it is safe for concurrent use.

When Stmt is created by Conn.Prepare or Tx.Prepare, only Stmt.Exec is safe for concurrent use.
Stmt.Query() doesn't read the result eagerly. Until Rows.Close() is called, the connection can not be used.
But database/sql don't protect it, because it is not only for go-sql-driver/mysql.

You shoudn't use Tx and Stmt bound to Tx in multiple goroutine, or you should guard it with Mutex.

[wisecsj]

@shogo82148 @methane Got it ,thank you!

And i want to konw ,concurrency Tx.Exec in one transaction is correct or allowed ?As you said,Stmt.Exec is safe for concurrent use.And Tx.Exec do the almost same thing:Exec would read buffer util EOF,database/sqllock the conn when call func Exec

[methane]

I think so, but your question is about database/sql, not this driver. So I am not 100% sure.