mysql锁等待超时查询_如何调试MySQL上的锁定等待超时？

给出这个的是交易这个词。该陈述显而易见，该查询试图改变一个或多个InnoDB表中的至少一行。

既然你知道了查询，那么所有被访问的表都是罪魁祸首的候选者。

从那里，你应该能够跑 SHOW ENGINE INNODB STATUS\G

您应该能够看到受影响的表格

您可以获得各种额外的锁定和互斥信息。

以下是我的一位客户的样本：

mysql> show engine innodb status\G

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

110514 19:44:14 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 4 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 9014315, signal count 7805377

Mutex spin waits 0, rounds 11487096053, OS waits 7756855

RW-shared spins 722142, OS waits 211221; RW-excl spins 787046, OS waits 39353

------------------------

LATEST FOREIGN KEY ERROR

------------------------

110507 21:41:35 Transaction:

TRANSACTION 0 606162814, ACTIVE 0 sec, process no 29956, OS thread id 1223895360 updating or deleting, thread declared inside InnoDB 499

mysql tables in use 1, locked 1

14 lock struct(s), heap size 3024, 8 row lock(s), undo log entries 1

MySQL thread id 3686635, query id 124164167 10.64.89.145 viget updating

DELETE FROM file WHERE file_id in ('6dbafa39-7f00-0001-51f2-412a450be5cc' )

Foreign key constraint fails for table `backoffice`.`attachment`:

,

CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`file_id`) REFERENCES `file` (`file_id`)

Trying to delete or update in parent table, in index `PRIMARY` tuple:

DATA TUPLE: 17 fields;

0: len 36; hex 36646261666133392d376630302d303030312d353166322d343132613435306265356363; asc 6dbafa39-7f00-0001-51f2-412a450be5cc;; 1: len 6; hex 000024214f7e; asc   $!O~;; 2: len 7; hex 000000400217bc; asc    @   ;; 3: len 2; hex 03e9; asc   ;; 4: len 2; hex 03e8; asc   ;; 5: len 36; hex 65666635323863622d376630302d303030312d336632662d353239626433653361333032; asc eff528cb-7f00-0001-3f2f-529bd3e3a302;; 6: len 40; hex 36646234376337652d376630302d303030312d353166322d3431326132346664656366352e6d7033; asc 6db47c7e-7f00-0001-51f2-412a24fdecf5.mp3;; 7: len 21; hex 416e67656c73204e6f7720436f6e666572656e6365; asc Angels Now Conference;; 8: len 34; hex 416e67656c73204e6f7720436f6e666572656e6365204a756c7920392c2032303131; asc Angels Now Conference July 9, 2011;; 9: len 1; hex 80; asc  ;; 10: len 8; hex 8000124a5262bdf4; asc    JRb  ;; 11: len 8; hex 8000124a57669dc3; asc    JWf  ;; 12: SQL NULL; 13: len 5; hex 8000012200; asc    " ;; 14: len 1; hex 80; asc  ;; 15: len 2; hex 83e8; asc   ;; 16: len 4; hex 8000000a; asc     ;;

But in child table `backoffice`.`attachment`, in index `PRIMARY`, there is a record:

PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 30; hex 36646261666133392d376630302d303030312d353166322d343132613435; asc 6dbafa39-7f00-0001-51f2-412a45;...(truncated); 1: len 30; hex 38666164663561652d376630302d303030312d326436612d636164326361; asc 8fadf5ae-7f00-0001-2d6a-cad2ca;...(truncated); 2: len 6; hex 00002297b3ff; asc   "   ;; 3: len 7; hex 80000040070110; asc    @   ;; 4: len 2; hex 0000; asc   ;; 5: len 30; hex 416e67656c73204e6f7720436f6e666572656e636520446f63756d656e74; asc Angels Now Conference Document;;

------------

TRANSACTIONS

------------

Trx id counter 0 620783814

Purge done for trx's n:o < 0 620783800 undo n:o < 0 0

History list length 35

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 29956, OS thread id 1192212800

MySQL thread id 5341758, query id 189708501 127.0.0.1 lwdba

show innodb status

---TRANSACTION 0 620783788, not started, process no 29956, OS thread id 1196472640

MySQL thread id 5341773, query id 189708353 10.64.89.143 viget

---TRANSACTION 0 0, not started, process no 29956, OS thread id 1223895360

MySQL thread id 5341667, query id 189706152 10.64.89.145 viget

---TRANSACTION 0 0, not started, process no 29956, OS thread id 1227888960

MySQL thread id 5341556, query id 189699857 172.16.135.63 lwdba

---TRANSACTION 0 620781112, not started, process no 29956, OS thread id 1222297920

MySQL thread id 5341511, query id 189696265 10.64.89.143 viget

---TRANSACTION 0 620783736, not started, process no 29956, OS thread id 1229752640

MySQL thread id 5339005, query id 189707998 10.64.89.144 viget

---TRANSACTION 0 620783785, not started, process no 29956, OS thread id 1198602560

MySQL thread id 5337583, query id 189708349 10.64.89.145 viget

---TRANSACTION 0 620783469, not started, process no 29956, OS thread id 1224161600

MySQL thread id 5333500, query id 189708478 10.64.89.144 viget

---TRANSACTION 0 620781240, not started, process no 29956, OS thread id 1198336320

MySQL thread id 5324256, query id 189708493 10.64.89.145 viget

---TRANSACTION 0 617458223, not started, process no 29956, OS thread id 1195141440

MySQL thread id 736, query id 175038790 Has read all relay log; waiting for the slave I/O thread to update it

--------

FILE I/O

--------

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (write thread)

Pending normal aio reads: 0, aio writes: 0,

ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

519878 OS file reads, 18962880 OS file writes, 13349046 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 6.25 writes/s, 4.50 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 1190, seg size 1192,

174800 inserts, 174800 merged recs, 54439 merges

Hash table size 35401603, node heap has 35160 buffer(s)

0.50 hash searches/s, 11.75 non-hash searches/s

---

LOG

---

Log sequence number 28 1235093534

Log flushed up to   28 1235093534

Last checkpoint at  28 1235091275

0 pending log writes, 0 pending chkp writes

12262564 log i/o's done, 3.25 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 18909316674; in additional pool allocated 1048576

Dictionary memory allocated 2019632

Buffer pool size   1048576

Free buffers       175763

Database pages     837653

Modified db pages  6

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 770138, created 108485, written 7795318

0.00 reads/s, 0.00 creates/s, 4.25 writes/s

Buffer pool hit rate 1000 / 1000

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

1 read views open inside InnoDB

Main thread process no. 29956, id 1185823040, state: sleeping

Number of rows inserted 6453767, updated 4602534, deleted 3638793, read 388349505551

0.25 inserts/s, 1.25 updates/s, 0.00 deletes/s, 2.75 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set, 1 warning (0.00 sec)

您应该考虑通过设置innodb_lock_wait_timeout来增加InnoDB的锁定等待超时值，默认为50秒

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50    |

+--------------------------+-------+

1 row in set (0.01 sec)

您可以/etc/my.cnf使用此行永久性地将其设置为更高的值

[mysqld]

innodb_lock_wait_timeout=120

并重启mysql。如果此时无法重启mysql，请运行以下命令：

SET GLOBAL innodb_lock_wait_timeout = 120;

您也可以在会话期间设置它

SET innodb_lock_wait_timeout = 120;

然后是您的查询