Golang's package and transaction processing operation steps

Transaction

A transaction is a logical unit in a database operation and consists of a series of database operations. This series of operations is either executed and submitted or rolled back all to ensure the consistency and integrity of the data. Transactions have the following four main characteristics, commonly known as ACID characteristics:

Atomicity: As a whole, all operations are either executed or not, and there will be no partial execution.
Consistency: At the beginning and end of the transaction, the state of the database is consistent and meets the predetermined constraints.
Isolation: The execution of one transaction does not affect the execution of another transaction unless another transaction waits for the first transaction to complete.
Durability: Once the transaction is committed, the modifications made will be permanently stored in the database and will not be lost due to system failure or crash.

In Go, usedatabase/sqlThe basic steps for a package to perform transactions are as follows:

Start a transaction:use()Method starts a transaction and returns a transaction objecttx。
Perform SQL operations: Use transaction objectstxPerform multiple SQL operations such as()、()wait.
Commit or roll back transactions: If all SQL operations are executed successfully, use()Submit transaction; if an error occurs, use()Roll back the transaction.

Transaction usage scenarios

Transfer operations: The transfer operation involves deductions from one account and additional payments from another account. These two operations must succeed at the same time, otherwise the data will be inconsistent.
Order processing: When submitting an order, it may be necessary to reduce inventory and increase order records, and these operations need to be guaranteed to be successful simultaneously.
Batch operation: When batch inserting, updating, or deleting data, make sure that all operations are either executed or rolled back.

Things to note

Minimize transaction granularity: Long-term transactions will occupy database resources, which may cause other operations to be blocked. Try to ensure that operations in transactions only involve necessary database access.
Avoid long-term operations in transactions: If you perform long-term operations such as file IO and network requests in a transaction, it may cause database connections to be occupied for a long time, affecting system performance.
Correctly handle transaction commits and rollbacks: Ensure that the transaction can be rolled back correctly in all possible error cases and the relevant resources are freed.

Database connection method

dsn := "username:password@tcp(127.0.0.1:3306)/dbname"

Method name	describe	Example
`()`	Open a database connection	`db, err := ("mysql", dsn)`
`()`	Test whether the database connection is valid	`err = ()`
`()`	Close the database connection	`defer ()`

Transaction method

Method name	describe	Example
`()`	Start a transaction	`tx, err := ()`
`()`	Roll back transactions	`()`
`()`	Submit transactions	`err = ()`

Query and execution methods

Method name	describe	Example
`()`	Execute SQL statements that do not return results, used for CREATE, INSERT, UPDATE, DELETE and other operations	`("create table ...")`
`()`	Execute SQL query that returns multiple rows of results	`rows, err := ("select ...")`
`()`	Execute SQL query that returns single-line results	`("select ...")`
`()`	Execute SQL statements using preprocessing statements	`("f", "g")`

Preprocessing statements

Method name	describe	Example
`()`	Create a preprocessing statement	`stmt, err := (...)`
`()`	Close the preprocessing statement	`defer ()`

Query result processing

Method name	describe	Example
`()`	Iterative query results	`()`
`()`	Assign the column value of the current row to a variable	`(&s1, &s2)`
`()`	Check for errors in query and iteration	`()`
`()`	Close the result set and release relevant resources	`defer ()`

Prepared Statements

Preprocessing statements refer to SQL statement templates that are compiled and optimized in advance in the database and can be reused many times later. The main advantages of preprocessing statements are as follows:

Improve efficiency: The database can compile and optimize preprocessing statements in advance, reducing the parsing time when SQL is executed, especially when the same SQL statement needs to be executed multiple times.
Prevent SQL injection: Through parameterized SQL statements, the data entered by the user will not be directly embedded into the SQL statements, reducing the risk of SQL injection.
Reduce network overhead: When the same SQL statement needs to be executed multiple times, the client only needs to send parameters, and does not need to send complete SQL statements every time, reducing the amount of data for network communication.

In Go, the basic steps of using preprocessing statements are as follows:

Prepare preprocessing statements:use()Method creates a preprocessing statement objectstmt。
Execute preprocessing statements:use()The method executes preprocessing statements and passes parameters.
Close the preprocessing statement: After the execution is completed, use()Method to release relevant resources.

Here is an example using a preprocessing statement:

stmt, err := ("INSERT INTO table1 (column1, column2) VALUES(?, ?)")  
if err != nil {  
    ("Preparation preprocessing statement failed: %v\n", err)  
    return  
}  
defer ()  
// First execution_, err = ("f", "g")  
if err != nil {  
    ()  
    ("First failure to execute preprocessing statement: %v\n", err)  
    return  
}  
// Second execution_, err = ("h", "i")  
if err != nil {  
    ()  
    ("Execution of preprocessing statement failed for the second time: %v\n", err)  
    return  
}

In this example, preprocessing statements are created at one time and executed multiple times, improving efficiency and reducing the risk of SQL injection.

Use scenarios for preprocessing statements

Batch Insert: Using preprocessing statements can significantly improve efficiency when large amounts of data need to be inserted.
Execute the same SQL statement frequently: When you need to execute the same SQL statement multiple times, using preprocessing statements can reduce the database parsing overhead and improve execution speed.
Prevent SQL injection: When processing data entered by users, using preprocessing statements can effectively prevent SQL injection attacks.

Things to note

Close preprocessing statements in time: After using the preprocessing statement, remember to close it in time to release the database resources to avoid resource leakage.
Correctly handle parameters: Ensure that the type and number of parameters passed to the preprocessing statement match the placeholders in the preprocessing statement.
Avoid overuse: Although preprocessing statements have many advantages, creating preprocessing statements may bring additional overhead and affect performance without requiring the same SQL statement to be executed multiple times.

Total sample code:

package main  
import (  
    "database/sql"  
    "fmt"  
    _ "/go-sql-driver/mysql"  
)  
const (  
    dsn = "username:password@tcp(localhost:3306)/test"  
)  
func main() {  
    db, err := ("mysql", dsn)  
    if err != nil {  
        ("Doesn't open the database connection: %v\n", err)  
        return  
    }  
    defer ()  
    if err := (); err != nil {  
        ("Database connection is not available: %v\n", err)  
        return  
    }  
    transactionExample(db)  
}  
func transactionExample(db *) {  
    tx, err := ()  
    if err != nil {  
        ("Failed to start a transaction: %v\n", err)  
        return  
    }  
    defer func() {  
        if err != nil {  
            ("Transaction rolling back...")  
            rollbackErr := ()  
            if rollbackErr != nil &amp;&amp; rollbackErr !=  {  
                ("Transaction rollback failed: %v\n", rollbackErr)  
            }  
        }  
    }()  
    // Create table    ("Create table...")  
    err = createTable(tx)  
    if err != nil {  
        return  
    }  
    // Insert data    ("Insert data to table1...")  
    err = insertData(tx)  
    if err != nil {  
        return  
    }  
    // Update data    ("Update the value of table1...")  
    err = updateData(tx)  
    if err != nil {  
        return  
    }  
    // Delete data    ("Delete data...")  
    err = deleteData(tx)  
    if err != nil {  
        return  
    }  
    // Query multiple rows of data    ("Query multiple rows of data...")  
    err = queryMultiRows(tx)  
    if err != nil {  
        return  
    }  
    // Query single line data    ("Query single-line data...")  
    err = querySingleRow(tx)  
    if err != nil {  
        return  
    }  
    // Preprocessing statement inserts data    ("Insert data using preprocessing statements...")  
    err = insertWithPrepare(tx)  
    if err != nil {  
        return  
    }  
    // Submit transaction    ("Submit transaction...")  
    err = ()  
    if err != nil {  
        ("Submit transaction failed: %v\n", err)  
        return  
    }  
    ("The transaction was successfully processed.")  
}  
func createTable(tx *) error {  
    _, err := ("create table if not exists table1 (column1 nchar(10), column2 nchar(10))")  
    return err  
}  
func insertData(tx *) error {  
    _, err := ("insert into table1 (column1, column2) values ('a','b'), ('c','d'), ('e','f')")  
    return err  
}  
func updateData(tx *) error {  
    _, err := ("update table1 set column1 = 'c' where column1 = 'a'")  
    return err  
}  
func deleteData(tx *) error {  
    _, err := ("delete from table1 where column1 = 'b'")  
    return err  
}  
func queryMultiRows(tx *) error {  
    rows, err := ("select * from table1")  
    if err != nil {  
        return err  
    }  
    defer ()  
    for () {  
        var s1, s2 string  
        err := (&amp;s1, &amp;s2)  
        if err != nil {  
            return ("Scan failed: %v", err)  
        }  
        ("table1 data: %s, %s\n", s1, s2)  
    }  
    if err := (); err != nil {  
        return ("Transfer of table1 failed: %v", err)  
    }  
    return nil  
}  
func querySingleRow(tx *) error {  
    var c string  
    err := ("select column1 from table1 where column1 = 'e'").Scan(&amp;c)  
    if err != nil {  
        if err ==  {  
            ("No matching row was found.")  
        } else {  
            return ("Query single-line data failed: %v", err)  
        }  
        return nil  
    }  
    ("Single-line data: %s\n", c)  
    return nil  
}  
func insertWithPrepare(tx *) error {  
    stmt, err := ("insert into table1 (column1, column2) values(?, ?)")  
    if err != nil {  
        return ("Preparation of preprocessing statement failed: %v", err)  
    }  
    defer ()  
    _, err = ("f", "g")  
    if err != nil {  
        return ("Execution of preprocessing statement failed: %v", err)  
    }  
    return nil  
}

This is the end of this article about golang's package and transaction processing operation steps. For more related golang package content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!