How to solve the problem of MySQL database stuck when executing Update

Problem Analysis

Generally the database transaction is not submitted, causing update or delete to get stuck.

Solution

1. After executing the update or delete, remember to commit the transaction;

2. Find the database client , perform commit operation.

If it still doesn’t work. When the database fails to perform a data operation or the transaction is not committed, it locks the SQL statement that needs to be executed.

Process Reproduction and Solution

Check the automatic submission status of the database through the following command

show variables like 'autocommit';

Set the database automatic submission to off through SQL

-- on为开启，off为关闭
set autocommit=off;
-- 或者1为开启，0为关闭
set autocommit=0;

Table The data is as follows:

Open two windows to perform update operations respectively

update car set color ='银色' where id = 1;
update car set color ='红色' where id = 1;

Query the transaction being executed:

SELECT * FROM information_schema.INNODB_TRX;

According to the thread ID (trx_mysql_thread_id) of the transaction in the figure, we can see the corresponding mysql thread: one is 1084 (update is waiting for the lock) and the other is 1089 (update is executing without committing the transaction)

You can use the mysql command to kill the thread: kill thread id

kill 1089;

If the thread holding the lock is not killed during the period: then the second update The statement will prompt the wait lock timeout.

Related commands:

-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查询mysql数据库中存在的进程
select * from information_schema.`PROCESSLIST`(show processlist;)

Extension

Oracle operation mode:

Query locked records

SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid;

Delete locked records

ALTER system KILL session 'SID,serial#';

The above is the detailed content of How to solve the problem of MySQL database stuck when executing Update. For more information, please follow other related articles on the PHP Chinese website!