We are using Orchard CMS 1.7.2. we recently getting many dead locks, timeouts and assertion failure issue.
Below are my observations:
- dead lock and time outs are mainly happening at the below query:
select userrolesp0_.Id as Id26_, userrolesp0_.UserId as UserId26_, userrolesp0_.Role_id as Role3_26_ from Orchard_Roles_UserRolesPartRecord userrolesp0_ where userrolesp0_userrolesp0_.UserId=@p0
- The Orchard_Roles_UserRolesPartRecord table is having index on USerId column even though no change in number of timeouts and dead locks
- Along with this table, dead locks/timeouts/concurrency has been noticed on the following tables:
Orchard_Framework_ContentItemRecord and Orchard_Framework_ContentItemVersionRecord (Note : These tables also having indexes)
- N-hibernate is not able to fetch records when table having more than 1+ million records (in Orchard_Framework_ContentItemVersionRecord) and throwing assertion failure
Because of this, we are getting many complaints from client/end users saying that they are not able to use the site well.
We are not sure how to control/fix them as these are coming from orchard framework level. We are looking at database isolation level. Some of the orchard articles recommending to change the database isolation level to snapshot (but no concrete justification
Please share your thoughts which are helpful for to us to resolve these issues at the earliest.