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

Deadlock refers to the phenomenon of mutual waiting caused by the competition for resources between two or more processes in the process of execution. it can be considered that if a resource is locked, it will always be released at a later time. Deadlocks occur when multiple processes access the same database, where each process has locks needed by other processes, resulting in each process being unable to continue.

InnoDB's concurrent write operations trigger deadlocks. InnoDB also provides a deadlock detection mechanism, which can be turned on or off by setting the innodb_deadlock_detect parameter:

Innodb_deadlock_detect = on opens deadlock detection and rolls back automatically when a deadlock occurs in the database (default option)

Innodb_deadlock_detect = off turns off deadlock detection. When a deadlock occurs, it is handled with a lock timeout. The blocked transaction can be rolled back when the timeout occurs by setting the lock timeout parameter innodb_lock_wait_timeout

You can also further observe the lock conflict details by setting InnDB Monitors

Set the InnoDB Monitors method

Establish a test library

Mysql > create database test

Query OK, 1 row affected (0.20 sec)

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > create table innodb_monitor (an INT) engine=innodb

Query OK, 0 rows affected (1.04 sec)

Mysql > create table innodb_tablespace_monitor (an INT) engine=innodb

Query OK, 0 rows affected (0.70 sec)

Mysql > create table innodb_lock_monitor (an INT) engine=innodb

Query OK, 0 rows affected (0.36 sec)

Mysql > create table innodb_table_monitor (an INT) engine=innodb

Query OK, 0 rows affected (0.08 sec)

You can view deadlock information through the show engine innodb status command

Mysql > show engine innodb status\ G

* * 1. Row *

Type: InnoDB

Name:

Status:

= =

2018-05-10 09:17:10 0x7f1fbc21a700 INNODB MONITOR OUTPUT

= =

Per second averages calculated from the last 46 seconds

-

BACKGROUND THREAD

-

Srv_master_thread loops: 53 srv_active, 0 srv_shutdown, 240099 srv_idle

Srv_master_thread log flush and writes: 0

-

SEMAPHORES

-

OS WAIT ARRAY INFO: reservation count 2007

OS WAIT ARRAY INFO: signal count 1987

RW-shared spins 3878, rounds 5594, OS waits 1735

RW-excl spins 3, rounds 91, OS waits 4

RW-sx spins 1, rounds 30, OS waits 1

Spin rounds per wait: 1.44 RW-shared, 30.33 RW-excl, 30.00 RW-sx

-

TRANSACTIONS

-

Trx id counter 78405

Purge done for trx's undo < 78404 state < 10 state: running but idle

History list length 21

LIST OF TRANSACTIONS FOR EACH SESSION:

-TRANSACTION 421249967052640, not started

0 lock struct (s), heap size 1136, 0 row lock (s)

-

FILE I/O

-

I thread 0 state: waiting for completed aio requests (insert buffer thread)

I thread 1 state: waiting for completed aio requests (log thread)

I thread 2 state: waiting for completed aio requests (read thread)

....

....

....

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.