MySQL and GORM concurrent writes cause errors

The concurrent writing problem between MySQL and GORM has always been a headache for developers. In high concurrency situations, multiple threads writing to the database simultaneously may lead to data inconsistency or erroneous results. PHP editor Baicao will analyze the causes and solutions of this problem for you to help you avoid the trouble caused by concurrent writing. Through reasonable design and optimization, the concurrent writing problem of MySQL and GORM can be effectively solved to ensure data consistency and accuracy.

Question content

I implemented a complex csv import script in golang. I use workerpool implementation. In this worker pool, workers run 1,000 small csv files that categorize, label, and brand products. They all write to the same database table. So far so good.

The problem I am facing is that if I select more than 2 workers, the process randomly crashes with the following message

The workflow is

foreach (csv) {
 workerPool.submit(csv)
}

func worker(csv) {
 foreach (line) {
   import(line)
 }
}

import(line) {
 product = get(line)
 product.category = determine_category(product)
 product.brand = determine_brand(product)
 save(brand)
 product.tags = determine_tags(product)
 //and after all
 save(product)
}

I tried wrapping the save() call in a transaction but it didn't help.

Now I have the following questions:

1. Is mysql suitable for saving to 1 table at the same time?
2. If a transaction is required to accomplish this task, where should it be set?
Is the
3. go sql driver (the error always occurs in packet.go:1102) suitable for doing this?
4. Can anyone help me (maybe hire a few hours)?

I'm totally stuck. I can also share the source code if it helps. But first I want to know if you guess this is my code or a general problem.

Workaround

Open a new database connection in each goroutine (or thread, for languages ​​that use threads).

MySQL's protocol is stateful, which means that if multiple goroutines try to use the same connection, requests and responses can become very confusing.

You will also encounter the same problem when trying to share any other type of stateful protocol connection between goroutines.

For example, ftp is also a stateful protocol, which may be easier to understand. The client goroutine might send a message like "Get file x" and the response should be a series of messages containing the contents of the file. If another goroutine tries to use the same connection while the request/response is in progress, both clients will get confused. The second goroutine will read packets belonging to a file it did not request. The first goroutine to request the file will find that some of the packets it expected have been read.

Similarly, MySQL's protocol does not support multiple client goroutines sharing a single connection.

The above is the detailed content of MySQL and GORM concurrent writes cause errors. For more information, please follow other related articles on the PHP Chinese website!