Sonarqube 4.5.4:MySQL死锁 [英] Sonarqube 4.5.4 : Mysql deadlock
本文介绍了Sonarqube 4.5.4:MySQL死锁的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在将sonarqube 4.5.4与mysql数据库一起使用.
I am using sonarqube 4.5.4 with mysql database.
在我的项目上运行分析时遇到此异常:
I got this exception when running analysis on my projects :
INFO o.d.m.sonar.runner.RunSonarVisitor - ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2016-02-23 04:05:40.091Z INFO o.d.m.sonar.runner.RunSonarVisitor - ### The error may involve org.sonar.core.user.RoleMapper.insertGroupRole-Inline
2016-02-23 04:05:40.091Z INFO o.d.m.sonar.runner.RunSonarVisitor - ### The error occurred while setting parameters
2016-02-23 04:05:40.091Z INFO o.d.m.sonar.runner.RunSonarVisitor - ### SQL: INSERT INTO group_roles (group_id, resource_id, role) VALUES (?, ?, ?)
这是SHOW ENGINE INNODB STATUS的输出:
=====================================
2016-02-23 11:54:18 2b4ff4306700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1238940 srv_active, 0 srv_shutdown, 970355 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13330
OS WAIT ARRAY INFO: signal count 13521
Mutex spin waits 1600127, rounds 719929, OS waits 5121
RW-shared spins 6726, rounds 123969, OS waits 2815
RW-excl spins 37472, rounds 641436, OS waits 2190
Spin rounds per wait: 0.45 mutex, 18.43 RW-shared, 17.12 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-02-23 05:33:42 2b5258087700
*** (1) TRANSACTION:
TRANSACTION 21846827, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 376, 3 row lock(s), undo log entries 1
MySQL thread id 48914, OS thread handle 0x2b4ff2fbf700, query id 74518517 10.166.12.78 root update
INSERT INTO group_roles (group_id, resource_id, role)
VALUES (1, 209803, 'admin')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 279 page no 410 n bits 1 index `group_roles_resource` of table `sonar`.`group_roles` trx id 21846827 lock_mode X locks gap before rec 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 21846828, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 376, 3 row lock(s), undo log entries 1
MySQL thread id 48921, OS thread handle 0x2b4ff4347700, query id 74518515 10.166.11.36 root update
INSERT INTO group_roles (group_id, resource_id, role)
VALUES (1, 209804, 'admin')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 279 page no 410 n bits 1 index `group_roles_resource` of table `sonar`.`group_roles` trx id 21846828 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 279 page no 410 n bits 1 index `group_roles_resource` of table `sonar`.`group_roles` trx id 21846828 lock_mode X locks gap before rec 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;;
------------
TRANSACTIONS
------------
Trx id counter 22243718
Purge done for trx's n:o < 22161699 undo n:o < 0 state: running but idle
History list length 771
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 49478, OS thread handle 0x2b4ff4306700, query id 75735427 10.166.1.176 root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 22243294, not started
MySQL thread id 49477, OS thread handle 0x2b4ff2fbf700, query id 75735410 10.166.1.176 root cleaned up
---TRANSACTION 22242581, not started
MySQL thread id 49474, OS thread handle 0x2b4ff4306700, query id 75735009 10.166.1.98 root delayed send ok done
---TRANSACTION 17172416, not started
MySQL thread id 42774, OS thread handle 0x2b4ff2efc700, query id 60565077 10.166.1.98 root cleaned up
---TRANSACTION 22243715, not started
MySQL thread id 10, OS thread handle 0x2b4fef6dd700, query id 75735423 localhost rdsadmin delayed send ok done
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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
2704 OS file reads, 2704 OS file writes, 0 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 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Adaptive hash table 0: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 1: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 2: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 3: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 4: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 5: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 6: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 7: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 8: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 9: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 10: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 11: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 12: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 13: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 14: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 15: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 16: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 17: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 18: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 19: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 20: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 21: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 22: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 23: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 24: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 25: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 26: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 27: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 28: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 29: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 30: Hash table size 495359, node heap has 0 buffer(s)
Adaptive hash table 31: Hash table size 495359, node heap has 0 buffer(s)
0.00 hash searches/s, 44.91 non-hash searches/s
---
LOG
---
Log sequence number 8204
Log flushed up to 8204
Pages flushed up to 8204
Last checkpoint at 8192
0 pending log writes, 0 pending chkp writes
0 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 0; in additional pool allocated 0
Dictionary memory allocated 1951054
Buffer pool size 488518
Free buffers 79640
Database pages 408878
Old database pages 150893
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0 single page 0
Pages made young 38024, not young 16
0.00 youngs/s, 0.00 non-youngs/s
Pages read 200, created 428488, written 0
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, Random read ahead 0.00/s
LRU len: 408878, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 244259
Free buffers 40396
Database pages 203863
Old database pages 75234
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0 single page 0
Pages made young 19914, not young 12
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106, created 212739, written 0
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, Random read ahead 0.00/s
LRU len: 203863, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 244259
Free buffers 39244
Database pages 205015
Old database pages 75659
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0 single page 0
Pages made young 18110, not young 4
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94, created 215749, written 0
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, Random read ahead 0.00/s
LRU len: 205015, 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
0 read views open inside InnoDB
Main thread process no. 4380, id 47632657958656, state: sleeping
Number of rows inserted 18537127, updated 1761791, deleted 1114846, read 1897702042
0.00 inserts/s, 0.50 updates/s, 0.00 deletes/s, 4.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
你能帮我吗?
推荐答案
您可以在配置中尝试innodb_locks_unsafe_for_binlog = 1吗?
Can you try innodb_locks_unsafe_for_binlog = 1 in your config?
此处是文档:实际上是表示select for update使表锁定.有关锁定innodb 的更多信息.
Here is documentation: effectively that means select for update makes table lock. More information about locks in innodb.
这里仍然有一些问号，但这是我的第一个想法.如果仍然有问题...让我们从该二进制日志设置开始.
Still there are some question marks here, but that is my first idea. If that is still a problem... let's start from that binlog settings.
这篇关于Sonarqube 4.5.4:MySQL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
