Table lock exceeded sometimes

[Kevin Cabritit]

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)
 
 

[Kevin Cabritit]

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)
 

[Kevin Cabritit]

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