Update Query Lock wait timeout exceeded

问题:

I want to update a table using the following query :

UPDATE `time_sheet_list_log` SET `entry_status` = 'History' WHERE `entry_status` = 'Active' AND `transaction_type` = 'Delete' AND `employee_id` = '77'

But I keep getting the following error :

A Database Error Occurred

Error Number: 1205

Lock wait timeout exceeded; try restarting transaction

UPDATE `time_sheet_list_log` SET `entry_status` = 'History' WHERE `entry_status` = 'Active' AND `transaction_type` = 'Delete' AND `employee_id` = '77'

Filename: C:\Xampp_1\htdocs\timesheet\system\database\DB_driver.php

Line Number: 330

I have to restart mysql DB for it accept. please advise what am I doing wrong?

回答1:

Does this help you? Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

If not, this is the query I would use.

$data['entry_status'] = 'History';

$this->db->where('entry_status', 'Active');
$this->db->where('transaction_type', 'Delete');
$this->db->where('employee_id', '77');
$query = $this->db->update('time_sheet_list_log', $data);

return ($this->db->affected_rows() > 0) ? true : false;

回答2:

Run show full processlist; query to figure out a possible operation/query which would have acquired a lock on the table. ( use KILL process id ; to remove the process if it is something not suppose to be running )