CC @kardianos

@coderYPG I'm not seeing why two stmts were created. Can you also print the SQL associated with each statement on fmt.Println("stmt Created!", query)? That may shed some light on this.

@coderYPG I'm not seeing why two stmts were created. Can you also print the SQL associated with each statement on fmt.Println("stmt Created!", query)? That may shed some light on this.

    I think i can explain it. DB.prepareDC prepare a driverStmt with a connection, and release(line 1503) the connection immediately.
    When statement exec, it first take a free connection from free list(line 1494). If the connection is not the previous connection the driverStmt prepared on, a new driverStmt is prepared on this connection and exec. The previous driverStmt will be closed by user with

defer stmt.Close()

but i don't see any call or hook to close the latter driverStmt.

What happens when you defer db.Close()?

statements should close after a time I believe, esp if you put time limits on open connections.

I'm not saying it is great, I don't like how prepared statements are designed in general, but I'm not sure if there is a bug here.

What happens when you defer db.Close()?

statements should close after a time I believe, esp if you put time limits on open connections.

I'm not saying it is great, I don't like how prepared statements are designed in general, but I'm not sure if there is a bug here.

    db.Close() not work.
    an opened statement is not closed, not close after a time.
    driver.Stmt.Close() will send a comStmtClose pkg to sql server to release the resource this stmt occupys.
So the implement nowaday may cause resource leaking.

I found tutorial has mention this problem

Because statements will be re-prepared as needed when their original connection is busy,  
it’s possible for high-concurrency usage of the database,  
which may keep a lot of connections busy, to create a large number of prepared statements.  
This can result in apparent leaks of statements, statements being prepared and re-prepared more often than you think,  
and even running into server-side limits on the number of statements.

@coderYPG I can explain this:

conn 1 is opened when PrepareContext is ran.
conn 1 is then used when db.Conn() is called. It is not returned ever.
conn 2 is prepared and run when stmt.QueryContext is called, because conn 1 (which was orig prepared) is busy.
conn 2 is released back to the pool at the defer. However, the conn 1 stmt cannot be released until the connection returns.

In the above code, conn 1 is never returned.

This is working as intended. Below is code that can run in sql_test.go in the std library.

func TestStatementNotClosed(t *testing.T) {
	db := newTestDB(t, "people")
	defer closeDB(t, db)

	ctx := context.Background()

	stmt, err := db.PrepareContext(ctx, "SELECT|people|name|age=?")

	// simulate others take a free connection
	conn, err := db.Conn(ctx)
	const releaseConn = true
	if releaseConn {
		defer conn.Close()
	}

	if err != nil {
		t.Fatal(err)
	}
	defer stmt.Close()

	rows, err := stmt.QueryContext(ctx, 1)
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()

	var name string
	for rows.Next() {
		err := rows.Scan(&name)
		if err != nil {
			t.Fatal(err)
		}
		fmt.Printf("name=%+v", name)
	}

	err = rows.Err()
	if err != nil {
		t.Fatal(err)
	}

}

If you switch releaseConn = false, then the test will fail because almost every unit test checks for pending connections. When the conn doesn't return, it raises a flag.