SQL Blocking Process ( CREATE function [Erp].[listEntry] )

We installed some SQL monitoring tools a few weeks ago and we’ve been learning all sorts of things about the Epicor DB.
Once thing we keep getting repeatedly is a Blocking Process usually triggered by a BAQ

What’s strange is that this function already exists but somehow Epicor BAQ process runs this create function and it causes some locking issues.

Any idea why this is and how to stop it ? @Rich

1 Like

I get alot of these, following proper SQL Permissions by not giving the Service Account full DB Access to dbo.

<Exception act="Ice:BO:SysConfig/SysConfigSvcContract/PassSessionValues" machine="GRECORAPP" pid="9104" tid="174"><![CDATA[System.Data.SqlClient.SqlException (0x80131904): Caller does not have permissions to execute the stored procedure.
   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.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   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.ExecuteReader()
   at Ice.Extensibility.AppserverStartupEvents.AppServerStartupEvent.ExecuteSQLQueryAndAppendToStringBuilder(IceContext ctx, StringBuilder sBuilder, Func`2 stringBuilderAction) in C:\_Releases\ICE\RL10.2.600.0FW\Source\Server\Internal\Extensibility\AppserverStartupEvents\AppServerStartupEvent.cs:line 158
ClientConnectionId:086ca7ff-8790-4ea4-acba-c049cf8c0c69
Error Number:27219,State:1,Class:16]]></Exception>

I narrowed it down, they are basically trying to capture the SQL Error Logs from the file and return it to the dll for whatever reason.

AppServerStartupEvent.cs

image
image

One again we are back here again 790 Record Locks in our DB right now

Nearly all of them are trying to “CREATE” the Erp.ListEntry function which doesn’t make a lick of sense since that function is always there.

We’ve been able to narrow down the issue to multiple people doing Invoice / Cash / AP Postings at once. But it is absurd, basically two or more people try to post a large Invoice / Cash group and I guess as part of that process somewhere SQL / or LINQ 2 SQL tries to re-create those functions which causes record locks.

It brings us down to our knees. I have to kill one of the transactions for the other one to process and even then sometimes it chains up to the next transaction in the batch.

@Edgar @Patrick.Ferrington @Rich can someone take a look and try and figure out why does Epicor / EntityFramework try to re-create this function?

Old thread about it:

Do you actually have steps how to repro it more precisely, than

"multiple people doing Invoice / Cash / AP Postings at once. "

No I do not, we can’t make it happen on demand, but we do know that it when it happens is because multiple (different users) tried to post AR Invoices / Cash Receipt at the same time.

Okay I think I have it narrowed down even more… though now I think its a bug of a different kind.

It looks like my user hit “POST” twice on the same transaction and it allowed her to do it. Submitted the job twice to Epicor and that caused a custer*fuck o locks.

however I still don’t understand why the hell it tries to re-create the function.

Those processes dont use much listEntry but they do use listLookup and the listLookup function makes use of listEntry.

Perhaps when calling a Function within a Function EF tries to re-create it?

Maybe but it wreaks havoc everywhere its so weird…