Bulk insert with Golang and Gorm deadlock in concurrency goroutines

Question

I'm trying to bulk insert many records using Gorm, Golang and MySQL. My code looks like this:

package main

import (
    "fmt"
    "sync"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Article struct {
    gorm.Model
    Code string `gorm:"size:255;uniqueIndex"`
}

func main() {
    db, err := gorm.Open(mysql.Open("root@tcp(127.0.0.1:3306)/q_test"), nil)
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&Article{})

    // err = db.Exec("TRUNCATE articles").Error
    err = db.Exec("DELETE FROM articles").Error
    if err != nil {
        panic(err)
    }

    // Build some articles
    n := 10000
    var articles []Article
    for i := 0; i < n; i++ {
        article := Article{Code: fmt.Sprintf("code_%d", i)}
        articles = append(articles, article)
    }

    // // Save articles
    // err = db.Create(&articles).Error
    // if err != nil {
    //  panic(err)
    // }

    // Save articles with goroutines
    chunkSize := 100
    var wg sync.WaitGroup
    wg.Add(n / chunkSize)
    for i := 0; i < n; i += chunkSize {
        go func(i int) {
            defer wg.Done()
            chunk := articles[i:(i + chunkSize)]
            err := db.Create(&chunk).Error
            if err != nil {
                panic(err)
            }
        }(i)
    }
    wg.Wait()
}

When I run this code sometimes (about one in three times) I get this error:

panic: Error 1213: Deadlock found when trying to get lock; try restarting transaction

If I run the code without goroutines (commented lines), I get no deadlock. Also, I've noticed that if I remove the unique index on the code field the deadlock doesn't happen anymore. And if I replace the DELETE FROM articles statement with TRUNCATE articles the deadlock doesn't seem to happen anymore. I've also run the same code with Postgresql and it works, with no deadlocks.

Any idea why the deadlock happens only with the unique index on MySQL and how to avoid it?

this might help. https://stackoverflow.com/a/32502736/7409103 — Bharat Gadde, Aug 10 '22 at 04:53

score 0 · Answer 1 · answered Aug 16 '22 at 01:11

DELETE statement is executed using a row lock, each row in the table is locked for deletion.
TRUNCATE TABLE always locks the table and page but not each row.

source : https://stackoverflow.com/a/20559931/18012302

I think mysql need time to do DELETE query.

try add time.Sleep after query delete.

    err = db.Exec("DELETE FROM articles").Error
    if err != nil {
        panic(err)
    }
    time.Sleep(time.Second)

Bulk insert with Golang and Gorm deadlock in concurrency goroutines

1 Answers1