mysql 1305_系统问题经常停顿，报1305 timeout 错误信息

最新推荐文章于 2024-03-10 01:17:30 发布

weixin_39595085

最新推荐文章于 2024-03-10 01:17:30 发布

阅读量108

点赞数

文章标签： mysql 1305

本文链接：https://blog.csdn.net/weixin_39595085/article/details/114742904

版权

mysql 版本： 5.0.27-standard-log

错误信息： 1305 Lock wait timeout exceeded; try restarting transaction

通过 show innodb status 有如下内容，请看看到底什么原因呢？

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

070725 9:22:26 INNODB MONITOR OUTPUT

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

Per second averages calculated from the last 2 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 1235, signal count 1235

Mutex spin waits 1914, rounds 21169, OS waits 35

RW-shared spins 1110, OS waits 555; RW-excl spins 646, OS waits 636

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

LATEST DETECTED DEADLOCK

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

070724 16:23:22

*** (1) TRANSACTION:

TRANSACTION 0 78344589, ACTIVE 26 sec, process no 20023, OS thread id 2716969904 starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 320

MySQL thread id 46, query id 106464 192.168.2.31 foxhis updating

delete from selected_account where pc_id = NAME_CONST('var_pc_id',_latin1'0005')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 10779 n bits 80 index `index1` of table `smart/selected_account` trx id 0 78344589 lock_mode X waiting

Record lock, heap no 9 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

0: len 1; hex 32; asc 2;; 1: len 4; hex 30303031; asc 0001;; 2: len 4; hex 80000002; asc ;; 3: len 10; hex 46373037323330303034; asc F707230004;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 000004ab6fe1; asc o ;; 6: len 7; hex 8000000f510110; asc Q ;;

*** (2) TRANSACTION:

TRANSACTION 0 78344161, ACTIVE 35 sec, process no 20023, OS thread id 2706172848 starting index read, thread declared inside InnoDB 499

mysql tables in use 1, locked 1

73 lock struct(s), heap size 5504, undo log entries 111

MySQL thread id 41, query id 108756 192.168.2.32 foxhis updating

delete from selected_account where type ='2' and pc_id ='0001' and ( mdi_id =2 or mdi_id =- 2 )

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 10779 n bits 80 index `index1` of table `smart/selected_account` trx id 0 78344161 lock_mode X locks rec but not gap

Record lock, heap no 9 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

Record lock, heap no 10 PHYSICAL RECORD: n_fields 7; compact format; info bits 32

0: len 1; hex 32; asc 2;; 1: len 4; hex 30303031; asc 0001;; 2: len 4; hex 80000004; asc ;; 3: len 10; hex 46373037323330303034; asc F707230004;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 000004ab6fe1; asc o ;; 6: len 7; hex 000000238f2139; asc # !9;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 10779 n bits 80 index `index1` of table `smart/selected_account` trx id 0 78344161 lock_mode X waiting

Record lock, heap no 9 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

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

TRANSACTIONS

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

Trx id counter 0 78466505

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

History list length 5

Total number of lock structs in row lock hash table 22

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 78466504, not started, process no 20023, OS thread id 2717170608

MySQL thread id 1300, query id 1159522 192.168.2.37 foxhis

---TRANSACTION 0 78463440, not started, process no 20023, OS thread id 2706172848

MySQL thread id 1291, query id 1145545 192.168.2.39 foxhis

---TRANSACTION 0 0, not started, process no 20023, OS thread id 2652896176

MySQL thread id 1281, query id 1109713 192.168.2.43 foxhis

---TRANSACTION 0 78463299, not started, process no 20023, OS thread id 2717772720

MySQL thread id 1264, query id 1144288 192.168.2.45 foxhis

---TRANSACTION 0 78463395, not started, process no 20023, OS thread id 2716969904

MySQL thread id 1253, query id 1150796 192.168.2.32 foxhis

---TRANSACTION 0 0, not started, process no 20023, OS thread id 2652695472

MySQL thread id 1229, query id 912349 192.168.2.44 foxhis

---TRANSACTION 0 78466487, ACTIVE 14 sec, process no 20023, OS thread id 2704276400 starting index read

mysql tables in use 3, locked 3

LOCK WAIT 2 lock struct(s), heap size 320

MySQL thread id 1307, query id 1158522 192.168.2.31 foxhis Sending data

call p_gds_after_login ('02', 'FOX', '1', 0)

------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 9031 n bits 72 index `bdate` of table `smart/sysdata` trx id 0 78466487 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 36; compact format; info bits 0

0: len 8; hex 80001241145096c0; asc A P ;; 1: len 6; hex 000004ad4913; asc I ;; 2: len 7; hex 0000002342159b; asc #B ;; 3: len 8; hex 80001241145096c0; asc A P ;; 4: len 1; hex 54; asc T;; 5: len 1; hex 54; asc T;; 6: len 8; hex 8000124114415480; asc A AT ;; 7: len 8; hex 8000124114415480; asc A AT ;; 8: len 1; hex 37; asc 7;; 9: len 3; hex 323033; asc 203;; 10: len 3; hex 363033; asc 603;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 4; hex 80000000; asc ;; 14: len 4; hex 800c3500; asc 5 ;; 15: len 4; hex 800e7ef0; asc ~ ;; 16: len 4; hex 805b8d99; asc [ ;; 17: len 4; hex 80000000; asc ;; 18: len 4; hex 80000000; asc ;; 19: len 4; hex 80000000; asc ;; 20: len 5; hex 802a277931; asc *'y1;; 21: len 5; hex 802a277931; asc *'y1;; 22: len 5; hex 802a277931; asc *'y1;; 23: len 5; hex 802a27793a; asc *'y:;; 24: len 5; hex 802a277931; asc *'y1;; 25: len 5; hex 802a277939; asc *'y9;; 26: len 5; hex 802a277931; asc *'y1;; 27: len 4; hex 80000000; asc ;; 28: len 4; hex 80000000; asc ;; 29: len 4; hex 80000000; asc ;; 30: len 4; hex 80000000; asc ;; 31: len 4; hex 80000000; asc ;; 32: len 5; hex 802a277931; asc *'y1;; 33: len 5; hex 802a277947; asc *'yG;; 34: len 5; hex 802a277931; asc *'y1;; 35: len 5; hex 802a277931; asc *'y1;;

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

---TRANSACTION 0 78466127, ACTIVE 17 sec, process no 20023, OS thread id 2652494768 starting index read

mysql tables in use 3, locked 3

LOCK WAIT 35 lock struct(s), heap size 2496, undo log entries 3

MySQL thread id 1309, query id 1159518 192.168.2.39 foxhis Sending data

call p_gds_after_login ('02', 'FOX', '1', 0)

Trx read view will not see trx with id >= 0 78466128, sees < 0 78465259

------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 9031 n bits 72 index `bdate` of table `smart/sysdata` trx id 0 78466127 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 36; compact format; info bits 0

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

---TRANSACTION 0 78465299, ACTIVE 23 sec, process no 20023, OS thread id 2716769200

2 lock struct(s), heap size 320, undo log entries 2

MySQL thread id 1203, query id 1159527 192.168.2.39 root

call p_gds_master_delete ('F707230004', 'FOX', 'F');

select @@autocommit;

show status;

show variables;

show innodb status

Trx read view will not see trx with id >= 0 78465300, sees < 0 78465259

---TRANSACTION 0 78465259, ACTIVE 31 sec, process no 20023, OS thread id 2716568496

MySQL thread id 1302, query id 1156047 192.168.2.32 foxhis

Trx read view will not see trx with id >= 0 78465260, sees < 0 78465260

--------

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

30952 OS file reads, 9299 OS file writes, 5947 OS fsyncs

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

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

INSERT BUFFER AND ADAPTIVE HASH INDEX

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

Ibuf: size 1, free list len 0, seg size 2,

0 inserts, 0 merged recs, 0 merges

Hash table size 34679, used cells 510, node heap has 2 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 0 761429355

Log flushed up to 0 761429355

Last checkpoint at 0 761429355

0 pending log writes, 0 pending chkp writes

4611 log i/o's done, 0.00 log i/o's/second

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

BUFFER POOL AND MEMORY

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

Total memory allocated 25270510; in additional pool allocated 1048576

Buffer pool size 512

Free buffers 0

Database pages 510

Modified db pages 0

Pending reads 0

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

Pages read 35056, created 670, written 5400

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

Buffer pool hit rate 1000 / 1000

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

ROW OPERATIONS

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

0 queries inside InnoDB, 0 queries in queue

4 read views open inside InnoDB

Main thread process no. 20023, id 2996902832, state: waiting for server activity

Number of rows inserted 28545, updated 2890, deleted 4881, read 2416550

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

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

END OF INNODB MONITOR OUTPUT

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

weixin_39595085

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫