Table lock exceeded sometimes

3 posts, 0 answers
  1. Kevin Cabritit
    Kevin Cabritit avatar
    17 posts
    Member since:
    Sep 2009

    Posted 19 Sep 2016 Link to this post

    I have a webform application that allow concurrent customers to book tickets online. Sometimes i get an period during the day when all transactions get a lock request timeout exceptions on my webservers.

    It seems there is an long running request that stops all other transaction from processing and timeouts.

    What is the best solution to solve this?

    I know I can increase the timeout period to greater than 120 sec in the web.config file  but its not ideal as I don't want to have long requests running on the server and taking up resources.

    Event code: 3005 
    Event message: An unhandled exception has occurred. 
    Event time: 20/09/2016 11:04:22 AM 
    Event time (UTC): 20/09/2016 1:04:22 AM 
    Event ID: 486eb515d35948caa8b41093eb3460e0 
    Event sequence: 5 
    Event occurrence: 1 
    Event detail code: 0 
     
    Application information: 
        Application domain: /LM/W3SVC/35/ROOT-3-131187658382069450 
        Trust level: Full 
        Application Virtual Path: / 
        Application Path: \\KICSFILES\KICSWebsites\www.cameocinemas.com.au\ 
        Machine name: WEBFARMSERVER2 
     
    Process information: 
        Process ID: 13352 
        Process name: w3wp.exe 
        Account name: WEBFARMSERVER2\KinesisUser 
     
    Exception information: 
        Exception type: LockNotGrantedException 
        Exception message: Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Lock request time out period exceeded. ---> System.Data.SqlClient.SqlException: Lock request time out period exceeded.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       at OpenAccessRuntime.CommandWrapper.ExecuteReader(CommandBehavior behavior)
       at Telerik.OpenAccess.Runtime.Logging.LoggingDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader()
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.CommandImp.ExecuteReader()
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       --- End of inner exception stack trace ---
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery(Nullable`1 commandTimeout)
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
    SQL:
    SELECT [BookingDateTime] AS COL1, [CinemaId] AS COL2, [CinemaId] AS COL3, [CompanyCreditCardTypeId] AS COL4, [CompanyCreditCardTypeId] AS COL5, [CreditCardExpiryMonth] AS COL6, [CreditCardExpiryYear] AS COL7, [CreditCardNumber] AS COL8, [CreditCardNumberCVV] AS COL9, [CreditCardPaymentAmount] AS COL10, [CustomerEmail] AS COL11, [CustomerIPAddress] AS COL12, [CustomerPhone] AS COL13, [CustomerPostCode] AS COL14, [DefaultBookingFeePerTicket] AS COL15, [ErrorText] AS COL16, [IsAllocatedSeating] AS COL17, [IsCommitted] AS COL18, [IsError] AS COL19, [IsJoinMailingList] AS COL20, [IsReceiptSent] AS COL21, [IsRedeemVoucher] AS COL22, [IsSendSMS] AS COL23, [IsStoredValueCardCharged] AS COL24, [IsStoredValueCardValid] AS COL25, [MemberId] AS COL26, [MembershipNumber] AS COL27, [MovieTitle] AS COL28, [NameOnCreditCard] AS COL29, [PaymentGatewayTransactionId] AS COL30, [PaymentGatewayTransactionId] AS COL31, [PriceGroup] AS COL32, [SMSCost] AS COL33, [SMSLogId] AS COL34, [ScreenId] AS COL35, [ScreenId] AS COL36, [SessionId] AS COL37, [SessionDateTime] AS COL38, [SessionId] AS COL39, [SessionPointOfSaleId] AS COL40, [SMSLogId] AS COL41, [Source] AS COL42, [StoredValueCardClosingBalance] AS COL43, [StoredValueCardNumber] AS COL44, [StoredValueCardPIN] AS COL45, [StoredValueCardPaymentAmount] AS COL46, [StoredValueCardStartingBalance] AS COL47, [TotalCost] AS COL48, [TotalCostOfBookingFees] AS COL49, [TotalCostOfTickets] AS COL50, [VIFAlternateKey] AS COL51, [VIFBookingIndex] AS COL52, [VIFBookingKey] AS COL53, [VIFBookingPIN] AS COL54, [VIFTransactionNumber] AS COL55, [VenueMasterTicketingPromoID] AS COL56, [VenueMasterTicketingSettingVIFDataId] AS COL57, [VenueMasterTicketingPromoID] AS COL58, [VenueMasterTicketingSettingVIFDataId] AS COL59, [WorkstationId] AS COL60 FROM [VENUEMASTERTICKETINGBOOKING] WHERE [VenueMasterTicketingBookingId] = ?                                        
     
    Request information: 
        Request URL: https://www.cameocinemas.com.au:443/VenueTicketing/OrderSummary.aspx 
        Request path: /VenueTicketing/OrderSummary.aspx 
        User host address: 122.102.111.244 
        User:  
        Is authenticated: False 
        Authentication Type:  
        Thread account name: WEBFARMSERVER2\KinesisUser 
     
    Thread information: 
        Thread ID: 10 
        Thread account name: WEBFARMSERVER2\KinesisUser 
        Is impersonating: False 
        Stack trace:    at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.PCStateMan.GetField[T](PersistenceCapable pc, Int32 field, T currentValue)
       at ORM.Venuemasterticketingbooking.OpenAccessEnhancedGetisSendSMS(Venuemasterticketingbooking owner)
       at ORM.Venuemasterticketingbooking.get_IsSendSMS()
       at VenueTicketing_OrderSummary.ContinuePushButton_Click(Object sender, EventArgs e)
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
     
     

  2. Kevin Cabritit
    Kevin Cabritit avatar
    17 posts
    Member since:
    Sep 2009

    Posted 27 Mar in reply to Kevin Cabritit Link to this post

    Hi Team,

    We are still experiencing this recently when requests from different websites access the same table to get data. I seemed to narrowed it down to a common function in the application.

    The GetNextSession method is a simple linq query.

      public static ORM.Session GetNextSession(Guid cinemaId)
            {
                IObjectScope scope = ORM.ScopeFactory.GetPerRequestScope(HttpContext.Current);

                DateTime now = DAL.TimeZoneConversion.GetNow(cinemaId);

                var result = from o in scope.Extent<ORM.Session>()
                             where o.CinemaId == cinemaId
                             && o.DateTime > now
                             && o.Movieinstance.IsActive == true
                             && o.Movieinstance.IsDeleted == false
                             orderby o.DateTime
                             select o;

                return result.FirstOrDefault();
            }

     

    Application is now running on .NET 4.5 instead of 3.5

    The httpruntime on the web.config is as follows.

    <httpRuntime maxRequestLength="4096" executionTimeout="360" />

    I do not want to keep increasing the executiontimeout value.

    Would it be best of these queries that are accessing a shared table in the database accross all 30 websites be a stored procedure call with the no lock setting instead of a linq?

    Or is there a better way to do this?

    Please see exception below
    Event code: 3005 
    Event message: An unhandled exception has occurred. 
    Event time: 28/03/2017 3:28:10 PM 
    Event time (UTC): 28/03/2017 4:28:10 AM 
    Event ID: e3133eebe2a24a169a445d7b94b77c5a 
    Event sequence: 71 
    Event occurrence: 62 
    Event detail code: 0 
     
    Application information: 
        Application domain: /LM/W3SVC/34/ROOT-1-131350705005564087 
        Trust level: Full 
        Application Virtual Path: / 
        Application Path: \\KICSFILES\KICSWebsites\www.communitycinemas.com.au\ 
        Machine name: WEBFARMSERVER1 
     
    Process information: 
        Process ID: 15744 
        Process name: w3wp.exe 
        Account name: WEBFARMSERVER1\KinesisUser 
     
    Exception information: 
        Exception type: LockNotGrantedException 
        Exception message: Telerik.OpenAccess.RT.sql.SQLException: Lock request time out period exceeded. ---> System.Data.SqlClient.SqlException: Lock request time out period exceeded.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.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 OpenAccessRuntime.Intellitrace.Read(DbDataReader reader, String id)
       at Telerik.OpenAccess.Runtime.Logging.LoggingDbDataReader.Read()
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()
       --- End of inner exception stack trace ---
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext()
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext()
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.executeQueryAll(ApplicationContext context, ImmutableQueryDetails query, CompiledQuery compiledQuery, QueryParameters parameters, Int32 skip, Int32 take)
       at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getAllQueryResults(CompiledQuery cq, QueryParameters parameters, Int32 skip, Int32 take)
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.Resolve()
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.Initialize()
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam)
       at OpenAccessRuntime.ListEnumerator.setCurrent(Int32 _pos)
       at OpenAccessRuntime.ListEnumerator.Move(Int32 relative)
       at OpenAccessRuntime.ListEnumerator.MoveNext()
       at Telerik.OpenAccess.Query.TypedEnumerator`1.MoveNext()
       at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQuerySingle[TResult,T](ChainedContext context, Expression expression, QueryableCategory before, Int32& found, Int32 elemAt, Boolean single, Boolean diffType)
       at Telerik.OpenAccess.Query.ExpressionExecution.PerformQuerySingle[T,TResult](ExpressionCutter cutter, MethodCallExpression mce, ChainedContext piece, QueryOptions options)
       at Telerik.OpenAccess.Query.Piece`1.ExecuteSingle[TResult](Expression expression)
       at Telerik.OpenAccess.Query.Piece`1.System.Linq.IQueryProvider.Execute[TResult](Expression expr)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
       at DAL.DataClasses.Session.GetNextSession(Guid cinemaId)
       at Responsive_UserControls_MainInteractive_Burswood.ConfigureNextSession(Guid cinemaId)
       at Responsive_UserControls_MainInteractive_Burswood.Page_Load(Object sender, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

     
     
    Request information: 
        Request URL: https://www.communitycinemas.com.au:443/Page/Home 
        Request path: /Page/Home 
        User host address: 122.102.111.243 
        User:  
        Is authenticated: False 
        Authentication Type:  
        Thread account name: WEBFARMSERVER1\KinesisUser 
     
    Thread information: 
        Thread ID: 28 
        Thread account name: WEBFARMSERVER1\KinesisUser 
        Is impersonating: False 
        Stack trace:    at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.executeQueryAll(ApplicationContext context, ImmutableQueryDetails query, CompiledQuery compiledQuery, QueryParameters parameters, Int32 skip, Int32 take)
       at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getAllQueryResults(CompiledQuery cq, QueryParameters parameters, Int32 skip, Int32 take)
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.Resolve()
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.Initialize()
       at OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam)
       at OpenAccessRuntime.ListEnumerator.setCurrent(Int32 _pos)
       at OpenAccessRuntime.ListEnumerator.Move(Int32 relative)
       at OpenAccessRuntime.ListEnumerator.MoveNext()
       at Telerik.OpenAccess.Query.TypedEnumerator`1.MoveNext()
       at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQuerySingle[TResult,T](ChainedContext context, Expression expression, QueryableCategory before, Int32& found, Int32 elemAt, Boolean single, Boolean diffType)
       at Telerik.OpenAccess.Query.ExpressionExecution.PerformQuerySingle[T,TResult](ExpressionCutter cutter, MethodCallExpression mce, ChainedContext piece, QueryOptions options)
       at Telerik.OpenAccess.Query.Piece`1.ExecuteSingle[TResult](Expression expression)
       at Telerik.OpenAccess.Query.Piece`1.System.Linq.IQueryProvider.Execute[TResult](Expression expr)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
       at DAL.DataClasses.Session.GetNextSession(Guid cinemaId)
       at Responsive_UserControls_MainInteractive_Burswood.ConfigureNextSession(Guid cinemaId)
       at Responsive_UserControls_MainInteractive_Burswood.Page_Load(Object sender, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
     

  3. Kevin Cabritit
    Kevin Cabritit avatar
    17 posts
    Member since:
    Sep 2009

    Posted 29 Mar in reply to Kevin Cabritit Link to this post

    Just wondering if anyone had any thoughts on this? Its seems to be occurring more and would love to resolve this asap.
Back to Top