Inside a custom Epicor Function, lock reading of table until transaction is done

Kinetic ERP
1

inside a custom Epicor Function, lock reading of table until transaction is done

basically this is a simple function: Look at table UD05, grab the record with column Key1 == “ErpID”
then increment that record’s column value in Character01
save the new value to the db and return the original value.

the problem is multiple calls happening simultaneously (multi threading from client side), both calls are able to read the value before the first updates it. thus resulting in both calls returning the same value instead of waiting their turn.

This is a shorthand of the code

using(var txScope = IceContext.CreateDefaultTransactionScope()) 
{
	//TODO: Determine the proper locking mode (i.e. UpdLock, HoldLock, etc.)
	//Retrieve the ERP ID row from the UD05 table and temporarily lock the record
	var r = this.Db.UD05.With(LockHint.HoldLock)// where Key1 == UD05_KEY1_VALUE //.FirstOrDefault(x => x.Key1 == UD05_KEY1_VALUE);

	var char01 = Convert.ToInt32(r.Character01);

	this.outputval = char01;

	char01 = char01 + 1;
	r.Character01 = char01.ToString();
	this.Db.SaveChanges();
	txScope.Complete();
}

I wish to essentially use tablockx but that is not a LockHint option

2

Why not use the sequence library which will always give you a unique value?

6 Likes
3

I am not familiar with that, can you point me to some info on it?

4
7 Likes
5

I tried importing the library ERP-Utilities_v3 from klincecum
with GetNextSequence and SetNextSequence

I reference the imported library in my other library and call the functions but it just times out after 30sec instead of doing anything.

trying to replicate what is in that library, under references-> assemblies I cannot add/find Erp.Internal.Lib.Shared.dll or Ice.Lib.NextValue.dll

that might be why it is not working as intended

easy to paste the bit of the GetSeq here for visibility

//These sequences are GLOBAL (All Companies)


//No empty keys
if(String.IsNullOrEmpty(sequenceKey)      == true) return;
if(String.IsNullOrWhiteSpace(sequenceKey) == true) return;

//Careful here..
using (ErpContext dbContext = Ice.Services.ContextFactory.CreateContext<ErpContext>()) //Erp Context
{
    //Get NextValue Object
    Ice.Lib.NextValue nextVal = new Ice.Lib.NextValue(dbContext);    

    //Get next "sequence" for "sequenceKey"
    sequence = nextVal.GetNextSequence(sequenceKey);

};
6

Yeah don’t use a 3rd party library just use the built DLL in sequence as you show. That Sequence DLL does show up in select assembly you just have to wait a bit

2 Likes
7

Why are you messing with Db.SaveChange when you can just use the UD05 service? It will handle updating and returning the changes as a tableset.

8

wow, I had to wait around 2min for that first dll to show up, thanks

when I make a call to this new function (internal to the library) or even when I just used the code above without making it it’s own function I get this error:
The underlying provider failed on EnlistTransaction.
…
Implicit distributed transactions have not been enabled. If you’re intentionally starting a distributed transaction, set TransactionManager.ImplicitDistributedTransactions to true.

9

You need to check the require transaction checkbox in your function (or uncheck it if its checked)

1 Like
10

I tried flipping the Requires transaction on and off and it does not seem to stop this error. This function is a couple layers deep and tried different combinations of the functions and their reqTran bit but to no avail.

current function structure

//GetNewAndExistingErpIDsByBomItem 
//(this gets called by the client by: 
var resp = await EpicorRest.EfxPostAsync(libraryName, "GetNewAndExistingErpIDsByBomItem" ,jsonData);

//that can call  GetNextSequence

I couldn’t seem to call the initial function GetNewAndExistingErpIDsByBomItem from the client if I turn ReqTran on for that one.
(edited to reflect simpler function call setup)

11

I think there is a ton of context missing around your requirements. Saying this is a few layers deep makes me raise an eyebrow.

The error you are getting is because you are trying to do a transaction inside another transaction in a remote setting and that generally won’t work

I suspect some of the “layers” have require transaction or are creating a transaction context themselves. You are going to have to peel back this onion for us if you want us to try and help further, we need more explicit context.

3 Likes
12

Seems that way…

13

ok, I have figured out my issue, thank you guys for all your help!

In the parent function, I was already in a transaction and it seems like the GetNextSequence call is not something that can be in a transaction as it can’t be rolled back.

I was able to move this logic out of the transaction and successfully call it!

I just have one follow up question, is there a way to see what the current value is without incrementing it? just for visibility outside the functions? like a table I can query from SSMS