golang数据库事务：如果单个exec语句失败，则继续

Question

I am writing a Go app which should insert thousands of values from a file into a database. This works fine, as long as all values can be inserted into the database. If one of the queries fails, all queries afterwards fail because of pq: : current transaction is aborted, commands ignored until end of transaction block

I want to insert all elements and if the insert of an element fails, it should be skipped and the other elements should be inserted.

My Code:

func (db *Database) Insert(values []Value) (transerr error) {
    tx, err := db.Begin()
    if transerr != nil {
        return nil, err
    }
    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }
    stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
    if err != nil {
        return err
    }

    defer stmt.Close()

    for _, value : range values {
        _, err = stmt.Exec(value)
        if err != nil {
            log.Error(err)
        }
    }
    return nil
}

I tried to add a tx.Rollback() in case a stmt.Exec fails - however this results in sql: statement is closed.

Answer 1

My solution for the problem looks like this:

• Do not create a single transaction and add all statements into it, instead just run it without creating transactions.
• As the values are read in, spawn new go routines and let the transaction run parallelized (be careful with connection limits).
• Without parallelization, the performance dropped about 30% (from 20s for 25k values to 30s - we did not use parallelization before).
• With parallelization, the performance increased about 4 times (to 5 seconds) - just be careful you stay within the connection ranges