最近修改于 4 个月前
0
0
文件
http://seanlook/2017/09/23/mysql-big-trx-lock-case : innodb_status.txt
innodb_status_long_lock_waits.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
=====================================
2017-09-16 10:25:49 7f24d8871700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 24550728 srv_active, 0 srv_shutdown, 152290 srv_idle
srv_master_thread log flush and writes: 24703018
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 185541892
OS WAIT ARRAY INFO: signal count 1150983458
Mutex spin waits 19381968360, rounds 23997664509, OS waits 25119164
RW-shared spins 670480185, rounds 10936376637, OS waits 45362830
RW-excl spins 739340940, rounds 16087249043, OS waits 114148932
Spin rounds per wait: -11.47 mutex, 16.31 RW-shared, 21.76 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-16 10:00:44 7f25b8fbe700
*** (1) TRANSACTION:
TRANSACTION 19704074358, ACTIVE 0.065 sec inserting
mysql tables in use 7, locked 1
LOCK WAIT 316 lock struct(s), heap size 46632, 315 row lock(s), undo log entries 313
LOCK BLOCKING MySQL thread id: 9480874 block 9480869
MySQL thread id 9480869, OS thread handle 0x7f25a5618700, query id 99362887509 10.xx.xx.179 db_user01 Sending data
INSERT INTO mydb.mytable01_group_custom (
......
------------
TRANSACTIONS
------------
Trx id counter 19706136089
Purge done for trx's n:o < 19706136089 undo n:o < 0 state: running but idle
History list length 2417
LIST OF TRANSACTIONS FOR EACH SESSION:
......
---TRANSACTION 421271328532584, not started
MySQL thread id 9057012, OS thread handle 0x7f25b3db6700, query id 99371625846 10.xx.xx.171 db_user01 cleaning up
---TRANSACTION 421273354733672, not started
MySQL thread id 7901472, OS thread handle 0x7f246e802700, query id 99371623253 10.xx.xx.95 ecdbsvr cleaning up

---TRANSACTION 19706125253, ACTIVE 7.913 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10050356
MySQL thread id 10050356, OS thread handle 0x7f25b8cb2700, query id 99371575683 10.xx.xx.171 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706125253 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

---TRANSACTION 19706124176, ACTIVE 8.663 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10061466
MySQL thread id 10061466, OS thread handle 0x7f25a5aeb700, query id 99371571429 10.xx.xx.122 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706124176 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

---TRANSACTION 19706124752, ACTIVE 8.296 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10044213
MySQL thread id 10044213, OS thread handle 0x7f25a5fbe700, query id 99371573390 10.xx.xx.76 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706124752 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

---TRANSACTION 19706118937, ACTIVE 12.100 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10063472
MySQL thread id 10063472, OS thread handle 0x7f29ab4b9700, query id 99371548194 10.xx.xx.118 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706118937 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

--------
---TRANSACTION 19706120900, ACTIVE 10.702 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10044208
MySQL thread id 10044208, OS thread handle 0x7f25f5cf3700, query id 99371557889 10.xx.xx.76 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706120900 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

---------
---TRANSACTION 19706124453, ACTIVE 8.460 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 9995432
MySQL thread id 9995432, OS thread handle 0x7f259b679700, query id 99371572453 10.xx.xx.76 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706124453 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

------------------
---TRANSACTION 19706118937, ACTIVE 12.100 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 9898630 block 10063472
MySQL thread id 10063472, OS thread handle 0x7f29ab4b9700, query id 99371548194 10.xx.xx.118 db_user01 updating
UPDATE mydb.mytable1 
SET f_discard = 1 
WHERE 
f_id in (5317885)
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12587 page no 5898 n bits 264 index `PRIMARY` of table `mydb`.`mytable1` trx id 19706118937 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 32
0: len 8; hex 80000000005124fd; asc Q$ ;;
1: len 6; hex 0004968ed2b8; asc ;;
2: len 7; hex 1d0000027416f3; asc t ;;
3: len 2; hex 8000; asc ;;
4: len 11; hex 3137363035313130323830; asc 17605110280;;
5: len 16; hex 373d3a8915904ef0709e80bd6417c993; asc 7=: N p d ;;
6: len 1; hex 04; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 999ce938c0; asc 8 ;;
9: len 5; hex 999cece39f; asc ;;
10: len 1; hex 00; asc ;;
11: len 4; hex 00000000; asc ;;
12: len 1; hex 00; asc ;;
13: len 0; hex ; asc ;;

------------------
---TRANSACTION 19705811640, ACTIVE 230.874 sec
14 lock struct(s), heap size 2936, 11 row lock(s), undo log entries 10
MySQL thread id 9898630, OS thread handle 0x7f24ef2bb700, query id 99370270600 10.xx.xx.76 db_user01 cleaning up
--------
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)