case
Case1: maxOpenConns > 1
func fewConns(a) {
db, _ := db.Open("mysql"."root:rootroot@/dqm? charset=utf8&parseTime=True&loc=Local")
db.SetMaxOpenConns(10)
rows, err := db.Query("select * from test where name = 'jackie' limit 10")
iferr ! =nil {
fmt.Println("query error")
}
row, _ := db.Query("select * from test")
fmt.Println(row, rows)
}
Copy the codeHere maxOpenConns is set to 10, which is sufficient for the two queries here.
The program executes normally and ends, printing a bunch of unprocessed results like this:
&{0xc0000fc180 0x10bbb80 0xc000106050 <nil> <nil> {{0 0} 0 0 0 0} false <nil> []} &{0xc0000f4000 0x10bbb80 0xc0000f8000 <nil> <nil> {{0 0} 0 0 0 0} false <nil> []}
Copy the codeCase2: maxOpenConns = 1
func oneConn(a) {
db, _ := db.Open("mysql"."root:rootroot@/dqm? charset=utf8&parseTime=True&loc=Local")
db.SetMaxOpenConns(1)
rows, err := db.Query("select * from test where name = 'jackie' limit 10")
iferr ! =nil {
fmt.Println("query error")
}
row, _ := db.Query("select * from test")
fmt.Println(row, rows)
}
Copy the codeHere maxOpenConns is set to 1, but there are two queries that require two connections, and debugging finds that the block is always in
row, _ := db.Query("select * from test")
Copy the codeIt is blocked because the connection is not available and the available connection has been occupied by the last query.
The following figure shows the execution result
Case3: maxOpenConns = 1 + for rows.next ()
It was discovered through case2 that there might be a connection leak, so keep maxOpenConns=1
func oneConnWithRowsNext(a) {
db, _ := db.Open("mysql"."root:rootroot@/dqm? charset=utf8&parseTime=True&loc=Local")
db.SetMaxOpenConns(1)
rows, err := db.Query("select * from test where name = 'jackie' limit 10")
iferr ! =nil {
fmt.Println("query error")}for rows.Next() {
fmt.Println("close")
}
row, _ := db.Query("select * from test")
fmt.Println(row, rows)
}
Copy the codeIn addition to maxOpenConns=1, there is more code for the rows traversal.
The result is as follows
close
close
close
close
close
close
&{0xc000104000 0x10bbfe0 0xc0000e40f0 <nil> <nil> {{0 0} 0 0 0 0} false <nil> []} &{0xc000104000 0x10bbfe0 0xc0000e40a0 <nil> <nil> {{0 0} 0 0 0 0} true 0xc00008e050 [[97 99] [105 101 2 49 56 12] [0 12]]}
Copy the codeObviously, the second query here does not block, but instead gets the connection and results.
So, the rows traversal must be doing something for us to get the connection.
Case4: maxOpenConns = 1 + for Rows.next () +
func oneConnWithRowsNextWithError(a) {
db, _ := db.Open("mysql"."root:rootroot@/dqm? charset=utf8&parseTime=True&loc=Local")
db.SetMaxOpenConns(1)
rows, err := db.Query("select * from test where name = 'jackie' limit 10")
iferr ! =nil {
fmt.Println("query error")
}
i := 1
for rows.Next() {
i++
if i == 3 {
break
}
fmt.Println("close")
}
row, _ := db.Query("select * from test")
fmt.Println(row, rows)
}
Copy the codeIn case3, we added the rows traversal code, so that the next query can get the connection, so we continue to examine whether the SQL statement will be affected if the rows traversal process has an early exit.
The following figure shows the execution result
We can see that the rows traversal ends early, which affects the later query, and the same situation occurs as in case2, that is, the database connection cannot be obtained, which is blocked.
Case5: maxOpenConns = 1 + for rows.next () + rows.close ()
func oneConnWithRowsNextWithErrorWithRowsClose(a) {
db, _ := db.Open("mysql"."root:rootroot@/dqm? charset=utf8&parseTime=True&loc=Local")
db.SetMaxOpenConns(1)
rows, err := db.Query("select * from test where name = 'jackie' limit 10")
iferr ! =nil {
fmt.Println("query error")
}
i := 1
for rows.Next() {
i++
if i == 3 {
break
}
fmt.Println("close")
}
rows.Close()
row, _ := db.Query("select * from test")
fmt.Println(row, rows)
}
Copy the codeCase4 is blocked on the second query.
Looking at the code above, after the exception exits, we call the statement that closes rows and proceed with the second query.
The result is as follows
close
&{0xc00010c000 0x10f0ab0 0xc0000e80a0 <nil> <nil> {{0 0} 0 0 0 0} false <nil> []} &{0xc00010c000 0x10f0ab0 0xc0000e8050 <nil> <nil> {{0 0} 0 0 0 0} true <nil> [[51] [104 101 108 108 111 2] [56 11]]}
Copy the codeThis time, according to the execution result, the second query executes normally and does not block.
So, why?
How does database/ SQL connection pooling work
Database/SQL connection pool
There are many articles on the web about database/ SQL connection pooling implementations.
Gorm orM framework database connection pool is also multiplexed database/ SQL connection pool.
There are roughly four steps
Step 1: Driver registration
We provide the main function code for the above cases
package main
import (
db "database/sql"
"fmt"
//_ "github.com/jinzhu/gorm/dialects/mysql"
_ "github.com/go-sql-driver/mysql"
)
func main(a) {
// maxConn > 1
fewConns()
// maxConn = 1
oneConn()
// maxConn = 1 + for rows.Next()
oneConnWithRowsNext()
// maxConn = 1 + for rows.next () + exit early
oneConnWithRowsNextWithError()
// maxConn = 1 + for rows.next () + defer rows.close ()
oneConnWithRowsNextWithErrorWithRowsClose()
}
Copy the codeThe driver registration here refers to
_ "github.com/go-sql-driver/mysql"
Copy the codeYou can also use MySQL driver registration in GORM that is
_ "github.com/jinzhu/gorm/dialects/mysql"
Copy the codeDriver registration mainly registers different data sources, such as MySQL, PostgreSQL, etc
Step 2: Initialize DB
To initialize DB, we call the Open function, which does not actually get the connection for the DB operation. We just initialize a DB data structure.
Step 3: Get the connection
Fetching joins is performed in concrete SQL statements, such as Query methods, Exec methods, and so on.
Using the Query method as an example, you can always trace the source code implementation, which is as follows
sql.go(Query()) -> sql.go(QueryContext()) -> sql.go(query()) -> sql.go(conn())
Copy the codeThe implementation logic that goes into the conn() method is to take out any free connections in the pool that have not expired;
If the actual number of connections exceeds the maximum number of connections, that is, maxOpenConns mentioned in the case above, the task is added to the task queue to wait.
If no, create a new connection to perform DB operations.
Step 4: Release the connection
When the DB operation is finished, the connection needs to be released, such as back into the connection pool, for the next DB operation.
The code that releases the connection implements the putConn() method in SQL.go.
Its main job is to determine whether a connection has expired and put it back into the connection pool if it has not.
The complete implementation logic for connection pooling is shown below
Case analysis
With the previous background knowledge, let’s analyze the above five cases
case1
The maximum number of connections is 10, and there are only two query tasks in the code. It is perfectly possible to create two connections to execute.
case2
The maximum number of connections is 1, and the first query has been used. The second query is blocked because the connection is not released after the first query is completed, and the second query cannot get the connection because the maximum number of connections is 1.
case3
The maximum number of connections is 1, but after the first query completes, the Rows traversal code is invoked. Through the source code can know the rows traversal code
func (rs *Rows) Next(a) bool {
var doClose, ok bool
withLock(rs.closemu.RLocker(), func(a) {
doClose, ok = rs.nextLocked()
})
if doClose {
rs.Close()
}
return ok
}
Copy the codeThe rows traversal calls the rows.close () method on the last traversal, which releases the connection.
So case3’s links are released in rows traversal
case4
The maximum number of connections is 1. Rows is also used, but the connection is still not released.
As explained in Case3, the rows.close () method is called only on the last walk, because the rows exit in the middle of the walk, causing the code to release the connection not to be executed. So the second query is still blocked and the connection is not available.
case5
The maximum number of connections is 1, the use of rows traversal, and exit in the middle, but the active call rows.close (), equivalent to the complete execution of the rows traversal, that is, the release of the connection, so the second query to get the connection normal execution of the query task.
Note: In real development, we tend to use the following elegant approach
defer rows.Close()
Copy the codetips
Recently was originally looking at gorM source code, but also wanted to gorM applied to our project team, but because of some secondary development and performance problems, gorM plans to shelve first.
Then when I looked at the GORM code, I realized that database/ SQL was used directly in many places, especially the connection pool implementation.
When you look at this code, you also notice that some of the code in our project uses rows traversal, but we forgot to add defer rows.close (). This is generally fine, but if something unexpected causes an early exit from traversal, you may have a connection leak problem.
JackieZheng (JackieZheng
