Sonar

Database deadlocks on Sonar 2.2 and MySQL

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 2.2
  • Fix Version/s: 2.14
  • Component/s: Database, DB Cleaner
  • Labels:
    None
  • Environment:
    Windows server, MySQL 5.1.40
  • Number of attachments :
    4

Description

Sonar in a corp environment and running 184 different projects for analyze.

The stacktrace below happens a lot. Not reproducable in the sense that we can say, and now we will have this, On avarage 5-10 times a day.

I fiddled with the innodb_lock_wait_timeout=50, and still the exception. Occasionally we see a similar stacktrace when the cleanup is executed.

The database has 10M records. Should not be an problem for MySQL but still it happens.

Embedded error: org.hibernate.exception.LockAcquisitionException: could not insert: [org.sonar.api.database.model.Snapshot]
Deadlock found when trying to get lock; try restarting transaction
[INFO] ------------------------------------------------------------------------
[INFO] Trace
org.apache.maven.lifecycle.LifecycleExecutionException: Can not execute Sonar
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:719)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeStandaloneGoal(DefaultLifecycleExecutor.java:569)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoal(DefaultLifecycleExecutor.java:539)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalAndHandleFailures(DefaultLifecycleExecutor.java:387)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeTaskSegments(DefaultLifecycleExecutor.java:284)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.execute(DefaultLifecycleExecutor.java:180)
at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:328)
at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:138)
at org.apache.maven.cli.MavenCli.main(MavenCli.java:362)
at org.apache.maven.cli.compat.CompatibleMain.main(CompatibleMain.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:599)
at org.codehaus.classworlds.Launcher.launchEnhanced(Launcher.java:315)
at org.codehaus.classworlds.Launcher.launch(Launcher.java:255)
at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:430)
at org.codehaus.classworlds.Launcher.main(Launcher.java:375)
Caused by: org.apache.maven.plugin.MojoExecutionException: Can not execute Sonar
at org.codehaus.mojo.sonar.Bootstraper.executeMojo(Bootstraper.java:103)
at org.codehaus.mojo.sonar.Bootstraper.start(Bootstraper.java:79)
at org.codehaus.mojo.sonar.SonarMojo.execute(SonarMojo.java:88)
at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490)
at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:694)
... 17 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: could not insert: [org.sonar.api.database.model.Snapshot]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:226)
at org.sonar.api.database.DatabaseSession.internalSave(DatabaseSession.java:118)
at org.sonar.api.database.DatabaseSession.save(DatabaseSession.java:95)
at org.sonar.batch.indexer.ResourcePersister.persist(ResourcePersister.java:78)
at org.sonar.batch.indexer.DefaultSonarIndex.persist(DefaultSonarIndex.java:201)
at org.sonar.batch.indexer.DefaultSonarIndex.getOrCreateBucket(DefaultSonarIndex.java:192)
at org.sonar.batch.indexer.DefaultSonarIndex.setSource(DefaultSonarIndex.java:217)
at org.sonar.batch.DefaultSensorContext.saveSource(DefaultSensorContext.java:129)
at org.sonar.api.batch.AbstractSourceImporter.parseDirs(AbstractSourceImporter.java:86)
at org.sonar.api.batch.AbstractSourceImporter.analyse(AbstractSourceImporter.java:77)
at org.sonar.api.batch.AbstractSourceImporter.analyse(AbstractSourceImporter.java:72)
at org.sonar.batch.SensorsExecutor.execute(SensorsExecutor.java:58)
at org.sonar.batch.ProjectBatch.execute(ProjectBatch.java:53)
at org.sonar.batch.Batch.analyzeProject(Batch.java:112)
at org.sonar.batch.Batch.analyzeProjects(Batch.java:81)
at org.sonar.batch.Batch.execute(Batch.java:57)
at org.sonar.maven.SonarMojo.executeBatch(SonarMojo.java:142)
at org.sonar.maven.SonarMojo.execute(SonarMojo.java:133)
at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490)
at org.codehaus.mojo.sonar.Bootstraper.executeMojo(Bootstraper.java:98)
... 21 more
Caused by: org.hibernate.exception.LockAcquisitionException: could not insert: [org.sonar.api.database.model.Snapshot]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2176)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130)
at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:49)
at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:154)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:110)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:61)
at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:646)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:620)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:624)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:220)
... 40 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:45)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:39)
at java.lang.reflect.Constructor.newInstance(Constructor.java:515)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)
... 55 more

  1. Text File
    deadlock_101216.txt
    16/Dec/10 4:30 AM
    6 kB
    Pieter van der Meer
  2. Text File
    deadlock_log.txt
    16/Nov/10 8:27 AM
    5 kB
    Pieter van der Meer
  3. Text File
    logging.txt
    16/Nov/10 3:05 AM
    3 kB
    Pieter van der Meer
  4. HTML File
    sonar_table_status.html
    09/Nov/10 10:09 AM
    9 kB
    Pieter van der Meer

Issue Links

depends upon

Bug - A problem which impairs or prevents the functions of the product. SONAR-2174 A SQL request in DBCleaner plugin generates two locks on the table SNAPSHOTS

  • Critical - Crashes, loss of data, severe memory leak.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

New Feature - A new feature of the product, which has yet to be developed. SONAR-2757 Refactor the purge mechanisms to prevent any fullscan SQL requests on snapshots table

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.
is related to

Bug - A problem which impairs or prevents the functions of the product. SONAR-1447 Database deadlocks while purging database on Sonar 1.11 and SQL Server 2008

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Bug - A problem which impairs or prevents the functions of the product. SONAR-2211 Database deadlocks while purging database on Sonar 2.3 and SQL Server 2005

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Improvement - An improvement or enhancement to an existing feature or task. SONAR-2961 Fail silently when deadlock happens during a purge

  • Critical - Crashes, loss of data, severe memory leak.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Pieter van der Meer added a comment -

Just for completeness a dump of the table status

Show
Pieter van der Meer added a comment - Just for completeness a dump of the table status
Hide
Permalink
Freddy Mallet added a comment -

Hi Pieter, how many Sonar analysis can run simultaneously ? Can two analysis on the same project can run simultaneously ?

Show
Freddy Mallet added a comment - Hi Pieter, how many Sonar analysis can run simultaneously ? Can two analysis on the same project can run simultaneously ?
Hide
Permalink
Pieter van der Meer added a comment -

Freddy,
I can not tell you the number of analysis that run simultaneously. But i'm certain that they run do. Basic setting on the CI (hudson) server is to poll the source repo every 15 mins and execute a build and analysis. With 184 projects working 8 hours a day, i'm pretty sure they run at the same time.

Just recently asked all administrators to inform me of the events structurally to see if i'm possible to prove the assumption that analysis are running simultaneously.

Show
Pieter van der Meer added a comment - Freddy, I can not tell you the number of analysis that run simultaneously. But i'm certain that they run do. Basic setting on the CI (hudson) server is to poll the source repo every 15 mins and execute a build and analysis. With 184 projects working 8 hours a day, i'm pretty sure they run at the same time. Just recently asked all administrators to inform me of the events structurally to see if i'm possible to prove the assumption that analysis are running simultaneously.
Hide
Permalink
Gayathri Muralidharan added a comment -

Hi
We are using hudson 1.341 and sonar 2.1.1 , sql and when many projects are getting built simultaneously, this deadlock happens and sonar analysis fails.
Has this issue been resolved in any other version?

Gayathri

Show
Gayathri Muralidharan added a comment - Hi We are using hudson 1.341 and sonar 2.1.1 , sql and when many projects are getting built simultaneously, this deadlock happens and sonar analysis fails. Has this issue been resolved in any other version? Gayathri
Hide
Permalink
Freddy Mallet added a comment -

Hi Gayathri, the problem is that we're not yet able to understand the issue. Indeed, there aren't any foreign key or constraint in the Sonar DB and when rows are deleted, the transaction is committed before switching to a new table. Any complementary information is welcome.
Thanks
Freddy

Show
Freddy Mallet added a comment - Hi Gayathri, the problem is that we're not yet able to understand the issue. Indeed, there aren't any foreign key or constraint in the Sonar DB and when rows are deleted, the transaction is committed before switching to a new table. Any complementary information is welcome. Thanks Freddy
Hide
Permalink
Pieter van der Meer added a comment - - edited

Just trying to provide more information on what happens.
For you info, this is not a build that runs concurrently (at least this project does not).

Show
Pieter van der Meer added a comment - - edited Just trying to provide more information on what happens. For you info, this is not a build that runs concurrently (at least this project does not).
Hide
Permalink
Freddy Mallet added a comment -

Thanks Pieter, the following MySQL web page might help us find solution to solve this issue : http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html

  • Use the 'SHOW ENGINE INNODB STATUS' command to determine the cause of the deadlock
  • We could plan to update the Sonar core to be able to re-issue a transaction
  • Set innodb_support_xa to 0
Show
Freddy Mallet added a comment - Thanks Pieter, the following MySQL web page might help us find solution to solve this issue : http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html Use the 'SHOW ENGINE INNODB STATUS' command to determine the cause of the deadlock We could plan to update the Sonar core to be able to re-issue a transaction Set innodb_support_xa to 0
Hide
Permalink
Freddy Mallet added a comment -

Thanks Pieter, this information is useful ... but strange : 

------------------------
LATEST DETECTED DEADLOCK
------------------------
101116 13:05:37
*** (1) TRANSACTION:
TRANSACTION 0 802428, ACTIVE 2 sec, OS thread id 3112 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1024, 2 row lock(s), undo log entries 6
MySQL thread id 8219, query id 23116247 hudson-server_a 145.xx.xxx.xx sonar update
insert into snapshots (created_at, depth, islast, parent_snapshot_id, path, qualifier, project_id, root_snapshot_id, root_project_id, scope, status, version) values ('2010-11-16 13:05:01', 0, 0, null, '', 'TRK', 6928, null, 6928, 'PRJ', 'P', '1.17-SNAPSHOT')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 9497 n bits 768 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802428 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 584 
*** (2) TRANSACTION:
TRANSACTION 0 802421, ACTIVE 15 sec, OS thread id 1660 fetching rows, thread declared inside InnoDB 344
mysql tables in use 2, locked 1
2410 lock struct(s), heap size 224576, 427825 row lock(s)
MySQL thread id 8216, query id 23116119 hudson_server_b xx.xx.xx.xx sonar Sending data
select snapshot0_.id as col_0_0_ from snapshots snapshot0_ where snapshot0_.islast=1 and (exists (select snapshot1_.id from snapshots snapshot1_ where snapshot1_.islast=1 and snapshot1_.project_id=snapshot0_.project_id and snapshot1_.created_at>snapshot0_.created_at))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 9497 n bits 768 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802421 lock mode S locks gap before rec
Record lock, heap no 584 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67473 n bits 1224 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802421 lock mode S waiting
Record lock, heap no 1157 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000229a; asc   " ;; 1: len 4; hex 80137efe; asc   ~ ;;

*** WE ROLL BACK TRANSACTION (1)

Indeed looks like, the nested select statement holds a lock on the 'snapshot_project_id' index whereas by default read are nonlocking (see http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html)

Show
Freddy Mallet added a comment - Thanks Pieter, this information is useful ... but strange : ------------------------ LATEST DETECTED DEADLOCK ------------------------ 101116 13:05:37 *** (1) TRANSACTION: TRANSACTION 0 802428, ACTIVE 2 sec, OS thread id 3112 inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1024, 2 row lock(s), undo log entries 6 MySQL thread id 8219, query id 23116247 hudson-server_a 145.xx.xxx.xx sonar update insert into snapshots (created_at, depth, islast, parent_snapshot_id, path, qualifier, project_id, root_snapshot_id, root_project_id, scope, status, version) values ('2010-11-16 13:05:01', 0, 0, null , '', 'TRK', 6928, null , 6928, 'PRJ', 'P', '1.17-SNAPSHOT') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 9497 n bits 768 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802428 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 584 *** (2) TRANSACTION: TRANSACTION 0 802421, ACTIVE 15 sec, OS thread id 1660 fetching rows, thread declared inside InnoDB 344 mysql tables in use 2, locked 1 2410 lock struct(s), heap size 224576, 427825 row lock(s) MySQL thread id 8216, query id 23116119 hudson_server_b xx.xx.xx.xx sonar Sending data select snapshot0_.id as col_0_0_ from snapshots snapshot0_ where snapshot0_.islast=1 and (exists (select snapshot1_.id from snapshots snapshot1_ where snapshot1_.islast=1 and snapshot1_.project_id=snapshot0_.project_id and snapshot1_.created_at>snapshot0_.created_at)) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 9497 n bits 768 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802421 lock mode S locks gap before rec Record lock, heap no 584 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 67473 n bits 1224 index `snapshot_project_id` of table `sonar`.`snapshots` trx id 0 802421 lock mode S waiting Record lock, heap no 1157 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000229a; asc " ;; 1: len 4; hex 80137efe; asc ~ ;; *** WE ROLL BACK TRANSACTION (1) Indeed looks like, the nested select statement holds a lock on the 'snapshot_project_id' index whereas by default read are nonlocking (see http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html )
Hide
Permalink
Freddy Mallet added a comment -

Gayathri, it would be very valuable if you could also launch the 'SHOW ENGINE INNODB STATUS' command on your side and attach the result to this ticket.
Thanks

Show
Freddy Mallet added a comment - Gayathri, it would be very valuable if you could also launch the 'SHOW ENGINE INNODB STATUS' command on your side and attach the result to this ticket. Thanks
Hide
Permalink
Gayathri Muralidharan added a comment -

Hi,
We are using MS SQL SERVER 2008 and not MySQL.
Thanks,
Gayathri

Show
Gayathri Muralidharan added a comment - Hi, We are using MS SQL SERVER 2008 and not MySQL. Thanks, Gayathri
Hide
Permalink
Gayathri Muralidharan added a comment -

Error message is as follows:

Caused by: org.apache.maven.plugin.MojoExecutionException: Can not analyze the project
at org.sonar.maven2.BatchMojo.executeBatch(BatchMojo.java:152)
at org.sonar.maven2.BatchMojo.execute(BatchMojo.java:131)
at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490)
at org.codehaus.mojo.sonar.Bootstraper.executeMojo(Bootstraper.java:82)
... 21 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: could not execute update query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:60)
at org.sonar.api.batch.AbstractPurge.executeQuery(AbstractPurge.java:114)
at org.sonar.api.batch.AbstractPurge.deleteMeasuresBySnapshotId(AbstractPurge.java:68)
at org.sonar.api.batch.AbstractPurge.deleteSnapshotData(AbstractPurge.java:51)
at org.sonar.plugins.core.purges.PurgeEntities.purge(PurgeEntities.java:51)
at org.sonar.batch.FinalizeSnapshotsJob.purge(FinalizeSnapshotsJob.java:109)
at org.sonar.batch.FinalizeSnapshotsJob.execute(FinalizeSnapshotsJob.java:53)
at org.sonar.batch.ProjectBatch.execute(ProjectBatch.java:53)
at org.sonar.batch.AggregatorBatch.analyzeProject(AggregatorBatch.java:109)
at org.sonar.batch.AggregatorBatch.analyzeProjects(AggregatorBatch.java:101)
at org.sonar.batch.AggregatorBatch.execute(AggregatorBatch.java:85)
at org.sonar.maven2.BatchMojo.executeBatch(BatchMojo.java:149)
... 24 more
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:110)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:421)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1169)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:51)
... 35 more
Caused by: java.sql.SQLException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:101)

Show
Gayathri Muralidharan added a comment - Error message is as follows: Caused by: org.apache.maven.plugin.MojoExecutionException: Can not analyze the project at org.sonar.maven2.BatchMojo.executeBatch(BatchMojo.java:152) at org.sonar.maven2.BatchMojo.execute(BatchMojo.java:131) at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490) at org.codehaus.mojo.sonar.Bootstraper.executeMojo(Bootstraper.java:82) ... 21 more Caused by: javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: could not execute update query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614) at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:60) at org.sonar.api.batch.AbstractPurge.executeQuery(AbstractPurge.java:114) at org.sonar.api.batch.AbstractPurge.deleteMeasuresBySnapshotId(AbstractPurge.java:68) at org.sonar.api.batch.AbstractPurge.deleteSnapshotData(AbstractPurge.java:51) at org.sonar.plugins.core.purges.PurgeEntities.purge(PurgeEntities.java:51) at org.sonar.batch.FinalizeSnapshotsJob.purge(FinalizeSnapshotsJob.java:109) at org.sonar.batch.FinalizeSnapshotsJob.execute(FinalizeSnapshotsJob.java:53) at org.sonar.batch.ProjectBatch.execute(ProjectBatch.java:53) at org.sonar.batch.AggregatorBatch.analyzeProject(AggregatorBatch.java:109) at org.sonar.batch.AggregatorBatch.analyzeProjects(AggregatorBatch.java:101) at org.sonar.batch.AggregatorBatch.execute(AggregatorBatch.java:85) at org.sonar.maven2.BatchMojo.executeBatch(BatchMojo.java:149) ... 24 more Caused by: org.hibernate.exception.LockAcquisitionException: could not execute update query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:110) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:421) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1169) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117) at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:51) ... 35 more Caused by: java.sql.SQLException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:101)
Hide
Permalink
Pieter van der Meer added a comment -

Freddy,
I do agree that a re-issue of the transaction could solve the problem. I'm not sure about the effects of setting the innodb_support_xa to false. What are the chances that the DB becomes corrupt? IF you are quite sure that the DB will not get corrupted We can try?

Pieter

Show
Pieter van der Meer added a comment - Freddy, I do agree that a re-issue of the transaction could solve the problem. I'm not sure about the effects of setting the innodb_support_xa to false. What are the chances that the DB becomes corrupt? IF you are quite sure that the DB will not get corrupted We can try? Pieter
Hide
Permalink
Freddy Mallet added a comment -

Pieter, could you first try to set the transaction-isolation to "READ_UNCOMMITED" (see http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_read-committed). Indeed with this mode, the select statements should not lock the 'snapshot_project_id' index and there should not be any side effect on Sonar.
Thanks
Freddy

Show
Freddy Mallet added a comment - Pieter, could you first try to set the transaction-isolation to "READ_UNCOMMITED" (see http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_read-committed ). Indeed with this mode, the select statements should not lock the 'snapshot_project_id' index and there should not be any side effect on Sonar. Thanks Freddy
Hide
Permalink
Freddy Mallet added a comment -

Thanks Pieter !

Show
Freddy Mallet added a comment - Thanks Pieter !
Hide
Permalink
Pieter van der Meer added a comment -

Freddy,

the implementation of the read-uncommitted seems to solve the problems we have. It this the solution that is going to be implemented in a next release or are you going for the re-execute of the transaction. Or maybe a combination of both?

Pieter

Show
Pieter van der Meer added a comment - Freddy, the implementation of the read-uncommitted seems to solve the problems we have. It this the solution that is going to be implemented in a next release or are you going for the re-execute of the transaction. Or maybe a combination of both? Pieter
Hide
Permalink
Freddy Mallet added a comment -

Thanks a lot Pieter, that's a valuable information. I guess the first step will be to suppress this SELECT statement which generates two different locks :

select snapshot0_.id as col_0_0_ from snapshots snapshot0_ where snapshot0_.islast=1 and (exists (select snapshot1_.id from snapshots snapshot1_ where snapshot1_.islast=1 and snapshot1_.project_id=snapshot0_.project_id and snapshot1_.created_at>snapshot0_.created_at))
Show
Freddy Mallet added a comment - Thanks a lot Pieter, that's a valuable information. I guess the first step will be to suppress this SELECT statement which generates two different locks : select snapshot0_.id as col_0_0_ from snapshots snapshot0_ where snapshot0_.islast=1 and (exists (select snapshot1_.id from snapshots snapshot1_ where snapshot1_.islast=1 and snapshot1_.project_id=snapshot0_.project_id and snapshot1_.created_at>snapshot0_.created_at))
Hide
Permalink
Pieter van der Meer added a comment -

Freddy,

Sorry to say that we thought the problem was solved. But see the attached log of the latest deadlock. Two things i must say, i performed maintenance on DB, Catalogs -> Sonar -> Maintenance -> Optimize this due to the fact that where running sort of low on disk space. Could the lack of disk space have any influence on this?

Pieter

Show
Pieter van der Meer added a comment - Freddy, Sorry to say that we thought the problem was solved. But see the attached log of the latest deadlock. Two things i must say, i performed maintenance on DB, Catalogs -> Sonar -> Maintenance -> Optimize this due to the fact that where running sort of low on disk space. Could the lack of disk space have any influence on this? Pieter
Hide
Permalink
Gayathri Muralidharan added a comment -

Hi,

Does Setting the Transaction isolation level to READ UNCOMMITTED (in sql server 2008) on the snapshots table solve the deadlock related issue?

Thanks,
Gayathri

Show
Gayathri Muralidharan added a comment - Hi, Does Setting the Transaction isolation level to READ UNCOMMITTED (in sql server 2008) on the snapshots table solve the deadlock related issue? Thanks, Gayathri
Hide
Permalink
Simon Brandhof added a comment -

SONAR-2174 (fixed in v2.6) removes the SQL request from the DBCleaner plugin which generates 2 locks on SNAPSHOTS.

Show
Simon Brandhof added a comment - SONAR-2174 (fixed in v2.6) removes the SQL request from the DBCleaner plugin which generates 2 locks on SNAPSHOTS.
Hide
Permalink
Simon Brandhof added a comment -

It should be resolved now thanks to SONAR-2757

Show
Simon Brandhof added a comment - It should be resolved now thanks to SONAR-2757

People

  • Assignee:
    Freddy Mallet
    Reporter:
    Pieter van der Meer
Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved: