Shulou(Shulou.com)06/01 Report--

Today Xiaobian to share with you mysql database deadlock Deadlock found when trying to get lock how to solve the relevant knowledge points, detailed content, clear logic, I believe most people are still too familiar with this knowledge, so share this article for everyone to refer to, I hope you have read this article after harvest, let's learn about it together.

View mysql deadlock logs

show engine innodb status

Find the line LATEST DETECTED DEADLOCK in the message, and you can see the details of mysql deadlock information.

------------------------LATEST DETECTED DEADLOCK------------------------2021-08-25 14:13:37 0x7facac6b8700*** (1) TRANSACTION:TRANSACTION 1589867098, ACTIVE 0 sec fetching rowsmysql tables in use 3, locked 3LOCK WAIT 508 lock struct(s), heap size 57552, 4 row lock(s)MySQL thread id 201608808, OS thread handle 140379228206848, query id 3088485657 172.18.119.16 root updatingUPDATE web_viewlog SET viewcount=viewcount+1,lasttime='2021-08-25 14:13:37' WHERE uid=2150174 and kename='21es1mmi'*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 564 page no 16179 n bits 208 index PRIMARY of table `w3cschool`.` web_viewlog` trx id 1589867098 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 16; compact format; info bits 0

MySQL has three lock levels: page level, table level, and row level.

Table-level locking: low overhead, fast locking; no deadlock; large locking granularity, highest probability of lock conflict, lowest concurrency.

Row-level locking: high overhead, slow locking; deadlocks occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Page locks: overhead and locking time bound between table locks and row locks; deadlocks occur; locking granularity bound between table locks and row locks, concurrency is average.

row-level locks

Row-level locks are not used to lock the row directly, but to lock the index.

If a sql uses a primary key index (mysql has a primary key index),mysql will lock the primary key index;

If a sql operation locks a non-primary index,mysql locks the non-primary index first and then the primary index.

Under what circumstances will deadlock occur

Deadlock: refers to two or more processes in the process of execution,

A waiting phenomenon caused by competition for resources, if there is no external force, they will not be able to advance.

At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always equal to each other are called deadlock processes.

Table-level locks do not create deadlocks. Therefore, deadlock resolution is mainly aimed at the most commonly used InnoDB.

Collect deadlock information:

Use the command SHOW ENGINE INNODB STATUS to view the cause of the deadlock.

During debugging, innodb_print_all_deadlocks are turned on to collect all deadlock logs.

Deadlock reduction:

Use transactions instead of lock tables.

Guaranteed no long transactions.

Commit the transaction immediately after the operation, especially on the interactive command line.

If you are using (SELECT)... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), trying to lower the isolation level.

When modifying multiple tables or rows, keep the order of modification consistent.

Creating indexes allows fewer locks to be created.

It is best not to use (SELECT... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。

If none of the above solves the problem, try locking multiple tables using lock tables t1, t2, t3

solution

First, query mysql transaction table with sql

select * from information_schema.INNODB_TRX;

Normally the state is RUNNING, but after being locked it becomes LOCK WAIT.

Once this happens, you have to kill the process, if the process can not kill you can only restart Mysql service.

kill the process

kill process ID above is "mysql database deadlock found when trying to get lock how to solve" all the contents of this article, thank you for reading! I believe everyone has a great harvest after reading this article. Xiaobian will update different knowledge for everyone every day. If you want to learn more knowledge, please pay attention to 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.