Login/Register
Sonarqube 4.5.4 : MySQL deadlock
Asked Answered
mysqlsonarqubedeadlocksonar-runnersonarqube-4.5
N

1

17

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 (?, ?, ?)

Here is the output of 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
 ============================

Could you help me please.

Negron answered 23/2, 2016 at 12:53 Comment(4)
Already asked on SO : #34361913 – Constrictive
yep but not the same case as I am not upgrading sonar-qube version and there was no answer for that question. – Negron
Please provide SHOW CREATE TABLE group_roles and explain where the INSERT comes from. – Postmeridian
Reference in SonarQube's backlog : jira.sonarsource.com/browse/SONAR-6613 – Marlee
P
0

Can you try innodb_locks_unsafe_for_binlog = 1 in your config?

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.

Parathyroid answered 22/4, 2016 at 20:55 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.