Dive into Golang database/sql (3)

This is a creation in Article, where the information may have evolved or changed.

In the previous chapter, we discussed database/sql how to get a real database connection in the Golang package. When we get a database connection, we can start a real database operation. This chapter continues to delve into how the underlying database operations are performed.

In the previous chapter we said:

db.Query()

is actually divided into two steps:

• Get database connection
• Actual DB operation with driver on this connection

func (db *DB) query(query string, args []interface{}, strategy connReuseStrategy) (*Rows, error) {    ci, err := db.conn(strategy)    if err != nil {        return nil, err    }    return db.queryConn(ci, ci.releaseConn, query, args)}

Well, let's see it together.db.queryConn

In fact, the core of the SQL package is to maintain the connection pool, for the actual operation, are the use of driver to complete. So the code implementation is the same, adhere to a principle:

Assemble driver required parameters, execute driver method

db.queryConnThe pseudo code is as follows:

Func (db *db) Queryconn (DC *driverconn, releaseconn func (Error), query string, args []interface{}) (*rows, error) {if Queryer, OK: = Dc.ci. (Driver. Queryer); OK {Dargs, err: = Driverargs (nil, args) if err! = Nil {releaseconn (err) return nil, ERR} DC. Lock () rowsi, err: = Queryer. Query (query, Dargs) DC. Unlock () If err! = driver.            Errskip {if err! = Nil {releaseconn (err) return nil, err}            Note:ownership of DC passes to the *rows, to is freed//with Releaseconn. Rows: = &rows{dc:dc, Releaseconn:releaseconn, Rowsi:ro WSI,} return rows, nil}} DC. Lock () si, err: = Dc.ci.Prepare (query) DC. Unlock () if err! = Nil {releaseconn (err) return nil, err} ds: = Driverstmt{dc, si} rowsi, err : = RowsifromStatement (ds, args ...) If err! = Nil {DC. Lock () Si. Close () DC.    Unlock () Releaseconn (ERR) return nil, err}//Note:ownership of CI passes to the *rows, to be freed    With Releaseconn.   Rows: = &rows{dc:dc, Releaseconn:releaseconn, Rowsi:rowsi, closestmt: Si,} return rows, nil}

The implementation of Queryconn can be divided into two parts:

• Driver implements the Queryer interface
• Driver does not implement the interface, goStmt三部曲

Queryer

QueryerInterface can reflect the style of Golang internal naming interface, such as, and Reader Writer so on, Queryer requires a method to implement Query . If driver implements this Query method, the SQL package simply needs to prepare the parameters it requires and then pass it on.

driverArgsTo prepare Query the required parameters, we actually convert the various types of values using reflection 它所在类型的最大类型 . This sentence is a bit difficult to understand, simple point is to convert to, convert to int int8 uint uint16 int16 int64 floatX float64 . Eventually, driverArgs all types are converted into the following

• []byte
• bool
• float64
• int64
• string
• time.Time

Thinking ①:

Why do you want to convert data

After you have prepared the parameters, call driver to implement a good query method.

dc.Lock()rowsi, err := queryer.Query(query, dargs)dc.Unlock()

The final request is simple, because the workload is driver, but the problem is coming.

Question ②:

Why do you lock it here?

Each Query will get a connection and then query, if the connection pool is thread-safe, for the subsequent behavior of fetching the connection need to lock?

Calling driver's Query method executes the query request and gets the ROWSI ( Driver.Rows ), wrapping it in a layer sql.Rows to return to caller.

// Note: ownership of dc passes to the *Rows, to be freed// with releaseConn.rows := &Rows{    dc:          dc,    releaseConn: releaseConn,    rowsi:       rowsi,}return rows, nil

So far, a real request has been processed. In fact, the SQL package is very simple, the workload is in a variety of different driver.

Stmt

As the document says, the Queryer interface is optional:

Queryer is an optional interface, may be implemented by a Conn.

If a Conn does not implement Queryer, the SQL package ' s DB. Query would first prepare a query, execute the statement, and then close the statement.

So for those lazy driver, executing a query request is a good thing Stmt .

dc.Lock()si, err := dc.ci.Prepare(query)dc.Unlock()

PrepareMethod produces a Stmt . Of course there is the same problem here, you need to think about it, whether it is necessary to lock. You can first look at Stmt the definition:

STMT is a prepared statement. It is bound to a Conn and not//used by multiple goroutines Concurrently.type Stmt interface {//Close closes the stat    Ement.    As of Go 1.1, a Stmt'll is not being closed if it's in use/per any queries.    Close () error//Numinput returns the number of placeholder parameters. If Numinput returns >= 0, the SQL package would sanity check//argument counts from callers and return Erro    RS to the caller//Before the statement ' s Exec or Query methods is called. Numinput may also return-1, if the driver doesn ' t know//its number of placeholders.    In this case, the SQL Package//would not sanity check Exec or Query argument counts.    Numinput () int//EXEC executes a query that doesn ' t return rows, such//as an INSERT or UPDATE.    Exec (args []value) (Result, error)//query executes a query that may return rows, such as a//SELECT. Query (args []value) (Rows, error)}

The methods you can see are Stmt also simple, Exec and are the methods that you Query will need to implement the final request. NumInputused to count the number of placeholders in an SQL statement.

A lot of people may be confused about what to Stmt do before, see here should understand. In fact, Stmt a template for a SQL statement, the template is fixed, but the parameters are changing, this scenario is particularly suitable Stmt , you no longer need to copy the SQL statement several times.

StmtOnce you get it, Stmt Query you can get the result rows by executing the method. The buildparams is also required before query and the placeholder of the parameters and SQL statements are checked, so a simple package is made:

ds := driverStmt{dc, si}rowsi, err := rowsiFromStatement(ds, args...)

Si is Stmt why the package driverStmt , but driverStmt what is it? In fact, mainly in order to execute in the rowsiFromStatement method Query is locking. The Queryer code in the reference, the execution Query is required to lock, this lock is provided by the DC, so the packaging driverStmt of a disguised let the Stmt locking method:

// driverStmt associates a driver.Stmt with the// *driverConn from which it came, so the driverConn's lock can be// held during calls.type driverStmt struct {    sync.Locker // the *driverConn    si          driver.Stmt}

rowsiFromStatementAfter the internal execution of the query has also got Driver.Rows , as before as the package sql.Rows to return to the caller good.

So far, we've explored how Golang's SQL package handles query requests. But there is one problem that has been running through the whole process:

Why to Lock

If you just look at the query method can not understand, but after looking at the stmt should be able to understand. stmt can be used multiple times, each stmt contains conn, can be a stmt as a database connection. With the concept of database connection, if users use this stmt in multiple goroutine, there will be concurrency problems, so query via stmt or exec needs to be locked.

However, for driver, which implements the Queryer interface, the user invokes db.Query a new connection each time after the call and then the query, and finally returns a rows. The whole process of direct query to the user has no concept of connection, so I personally feel safe. There needs to be no lock-in. If you feel the need to lock the welcome message and I discuss

Tx

TxIn fact, it is the same as above, the main is to create the first request a conn, and then based on this conn to wrap a TX object. Subsequent operations are dependent on the underlying database.

TX requires special attention to:

If the backend database proxy, the database transaction cannot be used

This has nothing to do with Golang, all languages are the same. Because we cannot guarantee that our request for a transaction falls to the same machine.

About Golang's SQL package, it will be over here too. In fact, its core is:

• Database connection pooling maintained
• Defines a series of interface specifications so that driver can be developed for interfaces

Then there is time, I write an article to analyze go-sql-driver/mysql , but the bottom of the implementation is relatively boring, mainly mysql通信协议 to achieve the norms, according to the norms to send and receive messages.

There are many new interfaces in the golang1.8 SQL package, which makes it much easier for us to use the database and to make some advanced encapsulation without the need for layers of reflection. but the support of the driver is a big problem at the moment .