current position:Home>Solve the problem of MySQL database executing Update stuck

Solve the problem of MySQL database executing Update stuck

2022-11-24 22:01:48share a drink no

The database is stuck when performing the update operation of the database

Problem analysis

Generally, database transactions are not committed, causing update or delete to be stuck.

Solution

  1. After updating or deleting, remember to submit the transaction to commit;
  2. Find the database client and execute the commit operation.

If not.Then it should be that the database locks the SQL statement that needs to be executed after the data operation fails or the transaction is not committed.

Process recurrence and resolution

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

show variables like 'autocommit';

Set the database auto-commit to off by SQL

-- on is open, off is closedset autocommit=off;-- or 1 for on, 0 for offset autocommit=0;

The data in the table are as follows:

Open two windows to perform the update operation separately

update car set color ='silver' where id = 1;update car set color ='red' 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, you can see the corresponding mysql thread: one is 1084 (update is waiting for a lock) and the other is 1089 (update is executing without committing a 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: the second update statement will prompt that the waiting for the lock has timed out.

Related commands:

-- View locked transactionsSELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;-- View transactions waiting for locksSELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;-- Query the processes that exist in the mysql databaseselect * from information_schema.`PROCESSLIST`(show processlist;) 

extension

The operation method of oracle:

  1. 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; 
  1. Delete locked records
ALTER system KILL session 'SID,serial#'; 

Woo la la la la if you like it, please like it, collect it or follow it

copyright notice
author[share a drink no],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/328/202211242155084169.html

Random recommended