Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

bulk upserts within a sql transaction in golang

Tags:

I've been messing around with golang's sql package with transactions, and I'm trying to understand how to do bulk upserts without the "per insert" round trip communication for each row. The examples here don't really show how any bulk queries would be done.

updateMoney, err := db.Prepare("INSERT INTO balance set money=?, id=? ON DUPLICATE UPDATE balance SET money=money+? WHERE id=?")
...
tx, err := db.Begin()
...
res, err := tx.Stmt(updateMoney).Exec(123.45, 1, 123.45, 1)
res, err := tx.Stmt(updateMoney).Exec(67.89, 2, 67.89, 2)
res, err := tx.Stmt(updateMoney).Exec(10.23, 3, 10.23, 3)
...
tx.Commit()

Ideally, I'd be able to take a prepared query, and build up a list of upserts to be sent at the same time... but here, we get a result back from the database after each execution. Any suggestions on how to go about this?

Edit: My coworker found this open ticket that describes the problem... it looks to be a larger concern than strictly within the context of a transaction.

like image 222
tbischel Avatar asked Aug 13 '14 15:08

tbischel


People also ask

How to optimize INSERT query in sql?

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

Is bulk insert a single transaction?

The bulk insert operation is broken into batches, each batch is treated in its own transaction so the whole operation isn't treated under a single transaction.


1 Answers

It depends on which driver you are using, some drivers / databases don't support transactions at all.

For example go-sql-driver/mysql supports transactions just fine.

Your code should work, or you could change it a little to:

tx, err := db.Begin()
...
stmt, err := tx.Prepare(`INSERT INTO balance set money=?, id=? ON DUPLICATE UPDATE balance SET money=money+? WHERE id=?`)
res, err := stmt.Exec(123.45, 1, 123.45, 1)
res, err := stmt.Exec(67.89, 2, 67.89, 2)
res, err := stmt.Exec(10.23, 3, 10.23, 3)
...
tx.Commit()

Also check this answer which goes into a lot of details about transactions.

like image 60
OneOfOne Avatar answered Oct 12 '22 15:10

OneOfOne