In my small web service, I get a deadlock when trying to insert a new row in logs table - Error 1213: Deadlock found when trying to get lock; try restarting transaction.
When I execute method Test many times, I get error. How can I avoid deadlock?
func main() {
var err error
db, err = sql.Open("mysql", dbUser+":"+dbPassword+"@tcp("+dbAddr+")/"+dbDatabase)
if err != nil {
panic(err.Error())
}
defer db.Close()
err = db.Ping()
if err != nil {
panic(err.Error())
}
defer db.Close()
router := mux.NewRouter()
router.HandleFunc("/test/", Test).Methods("POST")
log.Error(http.ListenAndServe(":8080", router))
}
func Test(w http.ResponseWriter, r *http.Request) {
w.Header().Set("Content-Type", "application/json")
w.Header().Set("Access-Control-Allow-Origin", "*")
w.Write(body)
_ = insertLog("Test")
}
func insertLog(msg string) error {
var sqlString string
sqlString = "insert into logs (date_time, msg) values(now(),?)"
stmt, err := db.Prepare(sqlString)
if err != nil {
log.Error(err)
return err
}
defer stmt.Close()
_, err = stmt.Exec(msg)
if err != nil {
log.Error("Msg: %s, Func: %s", err, function)
return err
}
defer stmt.Close()
return err
}
Schema (from Comment):
CREATE TABLE IF NOT EXISTS logs (
date_time datetime NOT NULL,
msg varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
You have no PRIMARY KEY; that is naughty.
Add
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
to the table definition. (See ALTER TABLE.)