Database deadlocks while purging database on Sonar 2.3 and SQL Server 2005

We are experiencing the exact same error , its sonar 2.3 and sqlserver 2005. We run sonar nightly jobs from hudson for all our development and all run at the same time at midnight causing 2 jobs to error. The exact spot in the stacktrace below.
[INFO] Sensor SurefireSensor done: 21 ms
[INFO] Execute decorators...
[INFO] ANALYSIS SUCCESSFUL, you can browse
[INFO] Database optimization...
[WARN] SQL Error: 1205, SQLState: 40001
[ERROR] Transaction (Process ID 161) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Can not execute Sonar

Embedded error: org.hibernate.exception.LockAcquisitionException: could not execute query
Transaction (Process ID 161) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the 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)

deadlock with..................................

[INFO] Sensor VersionEventsSensor done: 91 ms
[INFO] Sensor SurefireSensor done: 23 ms
[INFO] Execute decorators...
[INFO] ANALYSIS SUCCESSFUL, you can browse
[INFO] Database optimization...
[WARN] SQL Error: 1205, SQLState: 40001
[ERROR] Transaction (Process ID 168) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Can not execute Sonar

Embedded error: org.hibernate.exception.LockAcquisitionException: could not execute query
Transaction (Process ID 168) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the 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)

Any ideas, why does it fail on optimization step? Whats the workaround if any.
Thanks

Could you give a try to Sonar 2.6 as with SONAR-2174 your problem might be solved ?

Freddy, We will try to updgrade. I am posting some more information from SQLserver log about the deadlock.

It looks like your contention is with the selects and updates on the dbo.project_measures table.

2011-02-18 02:03:50.62 spid20s deadlock-list
2011-02-18 02:03:50.62 spid20s deadlock victim=processa18b048
2011-02-18 02:03:50.62 spid20s process-list
2011-02-18 02:03:50.62 spid17s deadlock-list
2011-02-18 02:03:50.62 spid17s deadlock victim=process4b3f2e8
2011-02-18 02:03:50.62 spid17s process-list
2011-02-18 02:03:50.62 spid20s process id=process4b3f2e8 taskpriority=0 logused=0 waitresource=PAGE: 24:1:8134 waittime=2156 ownerId=20888041211 transactionname=implicit_transaction lasttranstarted=2011-02-18T02:03:48.383 XDES=0x93f90370 lockMode=S schedulerid=2 kpid=2152 status=suspended spid=161 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-02-18T02:03:48.430 lastbatchcompleted=2011-02-18T02:03:48.430 clientapp=jTDS hostname=JBVDEVP01A hostpid=123 loginname=PIDSONR1 isolationlevel=read committed (2) xactid=20888041211 currentdb=24 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2011-02-18 02:03:50.62 spid20s executionStack
2011-02-18 02:03:50.62 spid20s frame procname=adhoc line=1 stmtstart=34 sqlhandle=0x02000000d05677206db5c02c2881df59459e851e48f2f503
2011-02-18 02:03:50.62 spid17s process id=process4b3f2e8 taskpriority=0 logused=0 waitresource=PAGE: 24:1:8134 waittime=2156 ownerId=20888041211 transactionname=implicit_transaction lasttranstarted=2011-02-18T02:03:48.383 XDES=0x93f90370 lockMode=S schedulerid=2 kpid=2152 status=suspended spid=161 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-02-18T02:03:48.430 lastbatchcompleted=2011-02-18T02:03:48.430 clientapp=jTDS hostname=JBVDEVP01A hostpid=123 loginname=PIDSONR1 isolationlevel=read committed (2) xactid=20888041211 currentdb=24 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2011-02-18 02:03:50.62 spid17s executionStack
2011-02-18 02:03:50.62 spid17s frame procname=adhoc line=1 stmtstart=34 sqlhandle=0x02000000d05677206db5c02c2881df59459e851e48f2f503
2011-02-18 02:03:50.62 spid20s select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid20s inputbuf
2011-02-18 02:03:50.62 spid17s select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid17s inputbuf
2011-02-18 02:03:50.62 spid20s (@P0 int,@P1 int)select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid17s (@P0 int,@P1 int)select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid20s process id=processa18b048 taskpriority=0 logused=0 waitresource=PAGE: 24:1:8134 waittime=3500 ownerId=20888036236 transactionname=implicit_transaction lasttranstarted=2011-02-18T02:03:47.090 XDES=0x370399a30 lockMode=S schedulerid=11 kpid=4564 status=suspended spid=168 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-02-18T02:03:47.090 lastbatchcompleted=2011-02-18T02:03:47.090 clientapp=jTDS hostname=JBVDEVP01A hostpid=123 loginname=PIDSONR1 isolationlevel=read committed (2) xactid=20888036236 currentdb=24 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2011-02-18 02:03:50.62 spid20s executionStack
2011-02-18 02:03:50.62 spid20s frame procname=adhoc line=1 stmtstart=34 sqlhandle=0x02000000d05677206db5c02c2881df59459e851e48f2f503
2011-02-18 02:03:50.62 spid17s process id=processa9b6718 taskpriority=0 logused=196952 waitresource=PAGE: 24:1:8133 waittime=1625 ownerId=20888035679 transactionname=implicit_transaction lasttranstarted=2011-02-18T02:03:46.493 XDES=0x1ac5e0370 lockMode=IX schedulerid=13 kpid=4748 status=suspended spid=166 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-18T02:03:49.003 lastbatchcompleted=2011-02-18T02:03:49.003 clientapp=jTDS hostname=JBVDEVP01A hostpid=123 loginname=PIDSONR1 isolationlevel=read committed (2) xactid=20888035679 currentdb=24 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2011-02-18 02:03:50.62 spid17s executionStack
2011-02-18 02:03:50.62 spid17s frame procname=adhoc line=1 stmtstart=448 sqlhandle=0x0200000027aa1f1d26677cffa481ba8aad2bd760982b978b
2011-02-18 02:03:50.62 spid20s select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid20s inputbuf
2011-02-18 02:03:50.62 spid17s update project_measures set alert_status= @P0 , alert_text= @P1 , characteristic_id= @P2 , description= @P3 , diff_value_1= @P4 , diff_value_2= @P5 , diff_value_3= @P6 , measure_date= @P7 , metric_id= @P8 , project_id= @P9 , rule_id= @P10 , rules_category_id= @P11 , snapshot_id= @P12 , tendency= @P13 , text_value= @P14 , url= @P15 , value= @P16 where id= @P17
2011-02-18 02:03:50.62 spid20s (@P0 int,@P1 int)select measuremod0_.id as col_0_0_ from project_measures measuremod0_, snapshots snapshot1_ where snapshot1_.id=measuremod0_.snapshot_id and (snapshot1_.root_snapshot_id= @P0 or snapshot1_.id= @P1 ) and (measuremod0_.rule_id is not null or measuremod0_.rules_category_id is not null or measuremod0_.rule_priority is not null)
2011-02-18 02:03:50.62 spid17s inputbuf
2011-02-18 02:03:50.62 spid20s process id=processa9b6718 taskpriority=0 logused=196952 waitresource=PAGE: 24:1:8133 waittime=1625 ownerId=20888035679 transactionname=implicit_transaction lasttranstarted=2011-02-18T02:03:46.493 XDES=0x1ac5e0370 lockMode=IX schedulerid=13 kpid=4748 status=suspended spid=166 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-18T02:03:49.003 lastbatchcompleted=2011-02-18T02:03:49.003 clientapp=jTDS hostname=JBVDEVP01A hostpid=123 loginname=PIDSONR1 isolationlevel=read committed (2) xactid=20888035679 currentdb=24 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2011-02-18 02:03:50.62 spid17s (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 float,@P5 float,@P6 float,@P7 datetime,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 float,@P17 bigint)update project_measures set alert_status= @P0 , alert_text= @P1 , characteristic_id= @P2 , description= @P3 , diff_value_1= @P4 , diff_value_2= @P5 , diff_value_3= @P6 , measure_date= @P7 , metric_id= @P8 , project_id= @P9 , rule_id= @P10 , rules_category_id= @P11 , snapshot_id= @P12 , tendency= @P13 , text_value= @P14 , url= @P15 , value= @P16 where id= @P17
2011-02-18 02:03:50.62 spid20s executionStack
2011-02-18 02:03:50.62 spid17s resource-list
2011-02-18 02:03:50.62 spid20s frame procname=adhoc line=1 stmtstart=448 sqlhandle=0x0200000027aa1f1d26677cffa481ba8aad2bd760982b978b
2011-02-18 02:03:50.62 spid17s pagelock fileid=1 pageid=8134 dbid=24 objectname=SONARPRD.dbo.project_measures id=lock2242ca200 mode=IX associatedObjectId=72057594042580992
2011-02-18 02:03:50.62 spid17s owner-list
2011-02-18 02:03:50.62 spid17s owner id=processa9b6718 mode=IX
2011-02-18 02:03:50.62 spid17s waiter-list
2011-02-18 02:03:50.62 spid17s waiter id=process4b3f2e8 mode=S requestType=wait
2011-02-18 02:03:50.62 spid20s update project_measures set alert_status= @P0 , alert_text= @P1 , characteristic_id= @P2 , description= @P3 , diff_value_1= @P4 , diff_value_2= @P5 , diff_value_3= @P6 , measure_date= @P7 , metric_id= @P8 , project_id= @P9 , rule_id= @P10 , rules_category_id= @P11 , snapshot_id= @P12 , tendency= @P13 , text_value= @P14 , url= @P15 , value= @P16 where id= @P17
2011-02-18 02:03:50.62 spid17s pagelock fileid=1 pageid=8133 dbid=24 objectname=SONARPRD.dbo.project_measures id=lock40c494700 mode=SIU associatedObjectId=72057594042580992
2011-02-18 02:03:50.62 spid20s inputbuf
2011-02-18 02:03:50.62 spid17s owner-list
2011-02-18 02:03:50.62 spid17s owner id=process4b3f2e8 mode=S
2011-02-18 02:03:50.62 spid17s owner id=processa9b6718 mode=IU
2011-02-18 02:03:50.62 spid17s waiter-list
2011-02-18 02:03:50.62 spid17s waiter id=processa9b6718 mode=IX requestType=convert
2011-02-18 02:03:50.62 spid20s (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 float,@P5 float,@P6 float,@P7 datetime,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 float,@P17 bigint)update project_measures set alert_status= @P0 , alert_text= @P1 , characteristic_id= @P2 , description= @P3 , diff_value_1= @P4 , diff_value_2= @P5 , diff_value_3= @P6 , measure_date= @P7 , metric_id= @P8 , project_id= @P9 , rule_id= @P10 , rules_category_id= @P11 , snapshot_id= @P12 , tendency= @P13 , text_value= @P14 , url= @P15 , value= @P16 where id= @P17
2011-02-18 02:03:50.62 spid20s resource-list
2011-02-18 02:03:50.62 spid20s pagelock fileid=1 pageid=8134 dbid=24 objectname=SONARPRD.dbo.project_measures id=lock2242ca200 mode=IX associatedObjectId=72057594042580992
2011-02-18 02:03:50.62 spid20s owner-list
2011-02-18 02:03:50.62 spid20s owner id=processa9b6718 mode=IX
2011-02-18 02:03:50.62 spid20s waiter-list
2011-02-18 02:03:50.62 spid20s waiter id=processa18b048 mode=S requestType=wait
2011-02-18 02:03:50.62 spid20s waiter id=process4b3f2e8 mode=S requestType=wait
2011-02-18 02:03:50.62 spid20s pagelock fileid=1 pageid=8133 dbid=24 objectname=SONARPRD.dbo.project_measures id=lock40c494700 mode=SIU associatedObjectId=72057594042580992
2011-02-18 02:03:50.62 spid20s owner-list
2011-02-18 02:03:50.62 spid20s owner id=process4b3f2e8 mode=S
2011-02-18 02:03:50.62 spid20s waiter-list
2011-02-18 02:03:50.62 spid20s waiter id=processa9b6718 mode=IX requestType=convert

It should be resolved now thanks to SONAR-2757