Status message

Maintenant, vous regardez: Laravel - Lock wait timeout exceeded

Laravel - Lock wait timeout exceeded

Problem description:
I have a transaction in my code ,if an error occurs in executing in this transactions that doesn't trigger commit or rollback,
then the database is locked and any subsequent attempts to access the database results in this:

  1. DB::beginTransaction();
  2. $db_data = Candidate::create([
  3. 'name' => $name,
  4. 'gender' => $gender,
  5. 'age' => $age,
  6. 'birth' => $birth,
  7. 'mobile' => $mobile,
  8. 'email' => $email,
  9. 'education' => $education,
  10. 'now_location' => $now_location,
  11. 'service_year' => $service_year,
  12. 'language' => $language
  13. ]);
  14.  
  15. $db_data->resume()->create([
  16. //'candidate_id' => $candidate_id,
  17. 'forward_location' => $forward_location,
  18. 'last_title' => $last_title,
  19. 'owner_id' => $owner_id,
  20. 'uploader_id' => $uploader_id,
  21. 'status' => $status,
  22. 'source' => $source,
  23. 'is_valid' => $is_valid,
  24. 'is_unique' => $is_unique,
  25. 'valid_unique' => $valid_unique,
  26. 'en_cv_flag' => $en_cv_flag,
  27. 'career_objective' => $career_objective,
  28. 'self_evaluations' => $self_evaluations,
  29. 'works_info' => $works_info,
  30. 'projects_info' => $projects_info,
  31. 'education_info' => $education_info,
  32. 'comprehensive_skills' => $comprehensive_skills,
  33. 'original_cv_attach' => $this->public_uri,
  34. 'ori_to_pdf_url' => $ori_to_pdf_url,
  35. 'en_cv_attach' => $en_cv_attach
  36. ]);
  37.  
  38. DB::commit();
  39.  
  40. } catch (\Exception $e) {
  41. DB::rollback();
  42. Event::dispatch(new ParseFilesEvent($this->current_user_id , $name . ' 的简历解析或者入库产生错误,已回滚到最初状态', 1));
  43. }
  1. production.ERROR: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction in /home/forge/default/vendor/laravel/framework/src/Illuminate/Database/Connection.php:390

Answer:
Trying to increate time of innodb_lock_wait_timeout, the default value is 50

  1. mysql> show variables like 'innodb_lock_wait_timeout';
  2. +--------------------------+-------+
  3. | Variable_name | Value |
  4. +--------------------------+-------+
  5. | innodb_lock_wait_timeout | 50 |
  6. +--------------------------+-------+
  7. 1 row in set (0.01 sec)

we can edit /etc/my.cnf to set a higher value:

  1. [mysqld]
  2. innodb_lock_wait_timeout=120

And then , restart mysql. or use the following commands to achieve this purpose:

  1. SET GLOBAL innodb_lock_wait_timeout = 120`;
  2. SET innodb_lock_wait_timeout = 120;