Gold R6 Deadlock errors

[AbleMods]

We're seeing several deadlock errors per day and can't tell what is the cause. Can Able have a look and give us a direction to look for:

2014-06-01 20:53:10,887 ERROR AbleCommerce An error has occured at http://<website>/category.aspx?categoryid=112
System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> NHibernate.Exceptions.GenericADOException: could not execute query
[ SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (14085,12331,11102,11158,12056,12321,11622,13817,13171,14003,11579,12191,12381,12415,12421,11143,11124,12373,13230,12200,12036,12014,11717,13173,13408,12894,11988,13757,12187,11122,13840,13874,11669,11459,12138,13290,14020,13374,12058,10984,12034,12680,11127,13391,11111,11667,13843,11842,12124,12717,13812,13428,12406,13943,12671,12013,12670,13988,10937,11670,12890,10429,11662,10745,12021,11989,13768,10752,12426,11671,13707,13775,14000,14065,11453,12196,13692,13971,14005,12338,13303,11454,13972,12182,13781,12746,12220,13405,13689,13412,13673,12201,14072,13179,14075,12202,12203,12689,14013,13769,13414,11157,12156,12748,12451,10638,13982,13280,11673,11700,11130,13969,11330,14079,13975,12747,14083,13306,12744,12891) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC ]
[SQL: SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (14085,12331,11102,11158,12056,12321,11622,13817,13171,14003,11579,12191,12381,12415,12421,11143,11124,12373,13230,12200,12036,12014,11717,13173,13408,12894,11988,13757,12187,11122,13840,13874,11669,11459,12138,13290,14020,13374,12058,10984,12034,12680,11127,13391,11111,11667,13843,11842,12124,12717,13812,13428,12406,13943,12671,12013,12670,13988,10937,11670,12890,10429,11662,10745,12021,11989,13768,10752,12426,11671,13707,13775,14000,14065,11453,12196,13692,13971,14005,12338,13303,11454,13972,12182,13781,12746,12220,13405,13689,13412,13673,12201,14072,13179,14075,12202,12203,12689,14013,13769,13414,11157,12156,12748,12451,10638,13982,13280,11673,11700,11130,13969,11330,14079,13975,12747,14083,13306,12744,12891) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC] ---> System.Data.SqlClient.SqlException: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at NHibernate.Driver.NHybridDataReader.Read()
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List()
at AbleCommerce.ConLib.CategoryGridPage.GetSortedProducts(IList`1 products)
at AbleCommerce.ConLib.CategoryGridPage.BindProductList()
at AbleCommerce.ConLib.CategoryGridPage.BindSearchResultsPanel()
at AbleCommerce.ConLib.CategoryGridPage.Page_PreRender(Object sender, EventArgs e)
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

[ForumsAdmin]

It appears you have some custom code in CategoryGridPage which causes this. Check your 'GetSortedProducts' method in CategoryGridPage.ascx.cs.

[AbleMods]

I don't see how a SQL query would cause a deadlock here though. All that routine does is sort the result set of products by quantity sold. So the most popular products are displayed at the top when the products are rendered.

is it because I'm doing a straight query and not using fluent nHibernate??

This is that routine :

Code: Select all

        // BEGIN MOD: AbleMods.com
        // DATE:  02/15/2014
        protected IList<Product> GetSortedProducts(IList<Product> products)
        {
            List<Product> sortedProducts = new List<Product>();
            string productIds = "";
            foreach (Product product in products)
                productIds += product.ProductId.ToString() + ",";
            if (!string.IsNullOrEmpty(productIds))
            {
                productIds = productIds.Remove(productIds.Length - 1, 1);
                string selectCommand = string.Format("SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN ({0}) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC", productIds);

                var nhQuery = NHibernateHelper.CreateSQLQuery(selectCommand.ToString());
                var results = nhQuery.List();

                foreach (object[] resultRow in results)
                {
                    Product product = ProductDataSource.Load(AlwaysConvert.ToInt(resultRow[0]));
                    sortedProducts.Add(product);
                }
            }
            return sortedProducts;
        }
         
        // END MOD: AbleMods.com