Go Basics-operation MySQL (iii)

Transactions are a very important feature of the database, especially for banks, payment systems, and so on.
Database/sql provides the functionality of transaction processing. Implemented by the TX object. Db. Begin creates the TX object, which exec and query performs a transactional database operation, and finally completes the commit and rollback of the database transaction in TX commit and rollback, releasing the connection at the same time.

Tx Object

We used to query and manipulate the database as a DB object, while the transaction was using a different object.
Use DB. The Begin method can create a TX object, and the TX object can also interact with the database Query,exec method
The usage is basically the same as we did before, but we need to execute the commit commit of the TX object or rollback the rollback method after the query or operation is complete.

Once the TX object is created, the transaction relies on the TX object, which pulls out an idle connection from the connection pool, and the next SQL execution is based on the connection, knowing that a commit or Roolback call will not release the connection to the connection pool.

In transaction processing, you cannot use the DB Query method, of course, if you can also execute the statement success, but this and your transaction will not be a transaction, will not accept the commit and rollback changes, such as the following operation:

Tx,err: = db.begin () db.exec () Tx. Exec () tx.commit ()

In this pseudo-code, when calling the Db.exec method, and TX execution Exec method is different, only the TX will be bound to the transaction, the DB is an additional connection, the two are not the same transaction.

Transactions and connections

When a TX object is created, the connection is removed from the connection pool and the associated exec method is called, and the connection is still bound to the transaction.
The connection life cycle of a transaction starts from the Beigin function call until the invocation of the commit and the rollback function ends.

transactional concurrency

For SQL. The TX object, because the transaction procedure has only one connection, the operations within the transaction are executed sequentially, and the previous database interaction must be completed before the next database interaction begins.

Rows, _: = db. Query ("SELECT id from User") for rows. Next () {    int    rows. Scan (&mid)    db. Queryrow ("SELECT ID from detail_user WHERE master =?", mid). Scan (& did)}

After invoking the query method, when the result is fetched in the next method, rows maintains a connection, and when the Queryrow is called again, the DB pulls out a new connection from the connection pool. Rows and DB connections can coexist and do not affect each other.

But if the logic is invalidated in the transaction, the following code:

Rows, _: = Tx. Query ("SELECT id from User") for rows. Next () {   int   rows. Scan (&mid)   TX. Queryrow ("SELECT ID from detail_user WHERE master =?", mid). Scan (& did)}

After TX executes the query method, the connection is transferred to rows, and in the next method, TX. Queryrow will attempt to obtain the connection for the database operation. Because the rows have not been called yet. Close, so the underlying connection is in the busy state, and TX cannot be queried.

A complete summary

The following is a complete example of a better understanding of the line:

func dosomething () {Panic ("A Panic Running Error")}func Cleartransaction (TX*SQL. TX) {err:=TX. Rollback ()ifErr! = SQL. Errtxdone && Err! =nil{Log. Fatalln (Err)}}func main () {db, err:= SQL. Open ("MySQL", "Root: @tcp (127.0.0.1:3306)/test?parsetime=true")    ifErr! =Nil {log. Fatalln (ERR)} defer db. Close () TX, err:=db. Begin ()ifErr! =Nil {log. Fatalln (ERR)} defer cleartransaction (TX) RS, err:= Tx. Exec ("UPDATE user SET gold=50 WHERE real_name= ' Vanyarpy '")    ifErr! =Nil {log. Fatalln (Err)} rowaffected, err:=Rs. Rowsaffected ()ifErr! =Nil {log. Fatalln (Err)} FMT. Println (rowaffected) RS, err= Tx. Exec ("UPDATE user SET gold=150 WHERE real_name= ' Noldorpy '")    ifErr! =Nil {log. Fatalln (Err)} rowaffected, err=Rs. Rowsaffected ()ifErr! =Nil {log. Fatalln (Err)} FMT. Println (rowaffected) dosomething ()ifERR: = Tx.commit (); Err! =Nil {//TX. Rollback () handles the error at this time, ignoring the dosomthing Exception log. Fatalln (Err)}}

This defines a cleartransaction (TX) function that performs the rollback operation. Because any error in our transaction process causes the main function to exit, the main function exits the rollback operation that performs defer, rolls back the transaction, and releases the connection.

If you do not add defer, only after the last Commit check error after err and then rollback, then when the dosomething exception occurs, the function exits, at this time has not been executed to tx.commit. This causes the connection for the transaction to not close and the transaction to not be rolled back.

In a TX transaction environment, there is only one database connection, and the Eexc within a transaction is executed sequentially, and the transaction can be queried using DB, but the process of DB query creates a new connection, and the operation of the connection does not belong to that transaction.

Go Basics-operation MySQL (iii)