LATEST DETECTED DEADLOCK mysql 死锁问题

樊明幸 发布于 2014/07/04 15:32
阅读 769
收藏 0
mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
140704 15:00:32 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 280332 1_second, 280331 sleeps, 25635 10_second, 24144 background, 24144 flush
srv_master_thread log flush and writes: 280405
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5541020, signal count 7218947
Mutex spin waits 257544116, rounds 1608620966, OS waits 2649402
RW-shared spins 4921057, rounds 50912237, OS waits 1153652
RW-excl spins 393999, rounds 10761640, OS waits 151964
Spin rounds per wait: 6.25 mutex, 10.35 RW-shared, 27.31 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
140704 11:53:47
*** (1) TRANSACTION:
TRANSACTION A30468D, ACTIVE 0 sec, process no 23288, OS thread id 140176462317312 inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 3112, 8 row lock(s), undo log entries 4
MySQL thread id 1671781, query id 76010237 10.59.76.12 root update
insert into package (id, uid, item_id, 
      item_name, item_type, item_number, 
      status, update_time, create_time)
    values (806611, 888888888, 2, 
      '金豆', 2, 500, 
      1, '2014-07-04 11:54:18', 
      '2014-07-04 11:54:18')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2470 page no 14 n bits 440 index `i_uid_itemtype_itemid_status` of table `payment`.`package` trx id A30468D lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION A30467C, ACTIVE 1 sec, process no 23288, OS thread id 140177500468992 inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 3
MySQL thread id 1671764, query id 76010257 10.59.76.12 root update
INSERT INTO package_for_unique
            (uid, item_type, item_id)
        VALUES (888888888, 2, 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2470 page no 14 n bits 440 index `i_uid_itemtype_itemid_status` of table `payment`.`package` trx id A30467C lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2469 page no 13 n bits 320 index `uid_itemType_itemId_uinque` of table `payment`.`package_for_unique` trx id A30467C lock mode S waiting
Record lock, heap no 252 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000034fb5e38; asc     4 ^8;;
 1: len 4; hex 80000002; asc     ;;
 2: len 8; hex 8000000000000002; asc         ;;
 3: len 6; hex 00000000237d; asc     #};;


*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter A3CC11A
Purge done for trx's n:o < A33EBD6 undo n:o < 0
History list length 149
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 23288, OS thread id 140176688858880
MySQL thread id 1673062, query id 80686422 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started, process no 23288, OS thread id 140178089486080
MySQL thread id 1672807, query id 78356069 10.6.212.56 root
---TRANSACTION A32A86C, not started, process no 23288, OS thread id 140176928007936
MySQL thread id 1672755, query id 80686310 10.59.76.12 root
---TRANSACTION A30E36B, not started, process no 23288, OS thread id 140176472823552
MySQL thread id 1672659, query id 80686322 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176485431040
MySQL thread id 1671595, query id 80686228 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177727006464
MySQL thread id 1671594, query id 80686226 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140175873046272
MySQL thread id 1670891, query id 80686269 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177731208960
MySQL thread id 1670890, query id 80686391 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176101689088
MySQL thread id 1669402, query id 80686225 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177680779008
MySQL thread id 1669414, query id 80686277 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177701791488
MySQL thread id 1669413, query id 80686400 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140175906666240
MySQL thread id 1669412, query id 80686393 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177525683968
MySQL thread id 1669411, query id 80686402 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176716175104
MySQL thread id 1669400, query id 80686241 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176118499072
MySQL thread id 1669422, query id 80686227 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176508544768
MySQL thread id 1669416, query id 80686259 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177494165248
MySQL thread id 1669415, query id 80686403 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140171821418240
MySQL thread id 1669403, query id 80686408 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177070499584
MySQL thread id 1669410, query id 80686399 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176294610688
MySQL thread id 1669407, query id 80686407 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176489633536
MySQL thread id 1669405, query id 80686223 10.59.76.12 root
---TRANSACTION A3CC119, not started, process no 23288, OS thread id 140176665745152
MySQL thread id 1669388, query id 80686396 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176078575360
MySQL thread id 1669409, query id 80686229 10.59.76.12 root
---TRANSACTION A3CC112, not started, process no 23288, OS thread id 140176711972608
MySQL thread id 1669390, query id 80686397 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177273927424
MySQL thread id 1669408, query id 80686406 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140177320154880
MySQL thread id 1669399, query id 80686321 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140171823519488
MySQL thread id 1669398, query id 80686420 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176290408192
MySQL thread id 1669397, query id 80686230 10.59.76.12 root
---TRANSACTION 0, not started, process no 23288, OS thread id 140176693061376
MySQL thread id 1669396, query id 80686421 10.59.76.12 root
---TRANSACTION A3CC0EF, not started, process no 23288, OS thread id 140177492064000
MySQL thread id 1669389, query id 80686401 10.59.76.12 root
---TRANSACTION 9CD3846, not started, process no 23288, OS thread id 140175896160000
MySQL thread id 1416359, query id 80686275 10.59.76.22 root
---TRANSACTION 9CD3845, not started, process no 23288, OS thread id 140176883881728
MySQL thread id 1416358, query id 80686388 10.59.76.22 root
---TRANSACTION 9CD3844, not started, process no 23288, OS thread id 140177682880256
MySQL thread id 1416357, query id 80686279 10.59.76.22 root
---TRANSACTION 9CD3848, not started, process no 23288, OS thread id 140175915071232
MySQL thread id 1415441, query id 80686398 10.59.76.22 root
---TRANSACTION 9CD3847, not started, process no 23288, OS thread id 140171798304512
MySQL thread id 1415436, query id 80686273 10.59.76.22 root
---TRANSACTION 9CD2B35, not started, process no 23288, OS thread id 140177106220800
MySQL thread id 1104378, query id 80686374 10.59.76.22 root
---TRANSACTION 9CD2D13, not started, process no 23288, OS thread id 140171626391296
MySQL thread id 1104380, query id 80686363 10.59.76.22 root
---TRANSACTION 9CD2D16, not started, process no 23288, OS thread id 140171800405760
MySQL thread id 1094136, query id 80686272 10.59.76.22 root
---TRANSACTION 9CD2D17, not started, process no 23288, OS thread id 140175885653760
MySQL thread id 1081313, query id 80686368 10.59.76.22 root
---TRANSACTION 9CD2B2E, not started, process no 23288, OS thread id 140177485760256
MySQL thread id 1081317, query id 80686366 10.59.76.22 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3507298 OS file reads, 237251 OS file writes, 42411 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 11408, seg size 11410, 46 merges
merged operations:
 insert 264, delete mark 14, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2212673, node heap has 25 buffer(s)
0.00 hash searches/s, 4.78 non-hash searches/s
---
LOG
---
Log sequence number 173232037555
Log flushed up to   173232037555
Last checkpoint at  173232037555
0 pending log writes, 0 pending chkp writes
20433 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 2057938
Buffer pool size   65535
Free buffers       3606
Database pages     61904
Old database pages 22831
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4414150, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3506919, created 66968, written 203194
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 61904, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 23288, id 140178106279680, state: sleeping
Number of rows inserted 5694749, updated 1146738, deleted 4, read 1904358670
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 568.21 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


1 row in set (0.00 sec)




以上是InnoDB的最后一个死锁信息  业务大概是这样的 package表插入数据之前先要往package_for_unique插入。package_for_unique表 根据用户和物品信息建立了唯一约束,如果package_for_unique插入成功再往package表里面插入数据。

并发压测的时候出现死锁。不明白出现原因。。求大神解答。

加载中
返回顶部
顶部