Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL database implementation of Update jam problem how to solve the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone after reading this MySQL database implementation of Update jam problem how to solve the article will have a harvest, let's take a look.

Analysis of problems

Generally, the database transaction is not committed, resulting in update or delete jamming.

Solution.

Remember to commit the transaction to commit after performing the update or deletion

Locate the database client and perform the commit operation.

If not. Then it should be that the database locks the sql statements that need to be executed after the data operation fails and the or transaction is not committed.

Process recurrence and solution

Check the auto-commit status of the database with the following command

Show variables like 'autocommit'

Set database auto-commit to shutdown via SQL

-- on is on, off is off set autocommit=off;--, 1 is on, 0 is off set autocommit=0

The data in the table is as follows:

Open two windows to perform the update operation

Update car set color = 'silver' where id = 1 * update car set color = 'red' where id = 1

Query the transaction in progress:

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 1084 (update is waiting for a lock) and the other is 1089 (update is executing an uncommitted 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 this period: the second update statement prompts you to wait for the lock to time out.

Related commands:

-- View the transaction being locked SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;-- View the transaction waiting for lock SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;-- query the process select * from information_ schema.`PROCESSLIST` (show processlist;) extension in the mysql database

How to operate oracle:

Query locked record

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 record

This is the end of ALTER system KILL session 'SID,serial#'; 's article on "how to solve the Update jam problem in MySQL database execution". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to solve the problem of Update jam in MySQL database". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.