Is there a way to flush current session transaction in 1.7.x or 1.8 to avoid locks

Topics: Troubleshooting, Writing modules
Mar 20, 2014 at 7:13 PM
Edited Mar 24, 2014 at 8:41 AM
I have a session taking 6 seconds to create several parts, but 2 second after I created the first part, another session wants to read it, then another one.
The last one get a timeout and the second succeed as in the same time the first session has finished and closed.

May be I will try to use SNAPSHOT features of SQL 2012

But before I would prefer to understand if there is a programatic way in Orchard to flush the actual session before its end, freing the lock on written objects, and continue updating/adding records in the flushed session (NH should be able to start a new transaction after the flush).
Mar 20, 2014 at 7:40 PM
Edited Mar 20, 2014 at 8:45 PM
It appears that this query is creating the lock

                .Where(r => (r.nvc_CloudServiceName == cloudServiceName && r.nvc_ResourceType == resourceType && r.nvc_ResourceName == resourceName && (includeDeleted || (r.ResourceStatus != (int)ResourceStatus.Deleted))))
                .Where (r => string.Equals(r.AzureStoreSubscription.nvc_SubscriptionId ,subscriptionId,StringComparison.InvariantCultureIgnoreCase) )
it generates this

[ SELECT this_.Id as Id405_3_, this_.Number as Number405_3_, this_.Published as Published405_3_, this_.Latest as Latest405_3_, this_.Data as Data405_3_, this_.ContentItemRecord_id as ContentI6_405_3_, contentite1_.Id as Id402_0_, contentite1_.Data as Data402_0_, contentite1_.ContentType_id as ContentT3_402_0_, azurestore2_.Id as Id512_1_, azurestore2_.AzureStoreResourceId as AzureSto2_512_1_, azurestore2_.nvc_CloudServiceName as nvc3_512_1_, azurestore2_.nvc_ResourceType as nvc4_512_1_, azurestore2_.nvc_ResourceName as nvc5_512_1_, azurestore2_.nvc_ETag as nvc6_512_1_, azurestore2_.nvc_Region as nvc7_512_1_, azurestore2_.nvc_Plan as nvc8_512_1_, azurestore2_.nvc_PromotionCode as nvc9_512_1_, azurestore2_.AllocatedResourceId as Allocat10_512_1_, azurestore2_.nvc_RawData as nvc11_512_1_, azurestore2_.dt_CreatedTime as dt12_512_1_, azurestore2_.dt_ChangedTime as dt13_512_1_, azurestore2_.dt_DeletedTime as dt14_512_1_, azurestore2_.ResourceStatus as Resourc15_512_1_, azurestore2_.AzureStoreSubscription_id as AzureSt16_512_1_, azurestore2_.AzureStorePlan_id as AzureSt17_512_1_, contenttyp6_.Id as Id407_2_, contenttyp6_.Name as Name407_2_ FROM Orchard_Framework_ContentItemVersionRecord this_ inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id inner join Datwendo_AzureMarket_AzureStoreResourcePartRecord azurestore2_ on contentite1_.Id=azurestore2_.Id left outer join Orchard_Framework_ContentTypeRecord contenttyp6_ on contentite1_.ContentType_id=contenttyp6_.Id WHERE (((azurestore2_.nvc_CloudServiceName = @p0 and azurestore2_.nvc_ResourceType = @p1) and azurestore2_.nvc_ResourceName = @p2) and not (azurestore2_.ResourceStatus = @p3)) and this_.Published = @p4 ]

When we see this kind of request we understand that there are so many reasons to lock. If we get a page or table lock on Orchard_Framework_ContentItemRecord or Orchard_Framework_ContentItemVersionRecord it's clear that all Orchard current sessions have to wait its end because 99% of Orchard requests rely on these tables.

Ideally I would use a LOCK optimizer on the insert request but NH doesn't know how to add simply 'WITH NOLOCK'

Is there any way to optimize my query ?
Problem is not 'fatal' as the first transaction is OK and so the result, but I get many traces....