Assign Lot Number X41LOT Record lock timings. Duplicate LOTNs

[johndanter]

Hi List

I have an interesting situation in that we are encountering duplicate LOTNS at server midnight. (which are actually the 0001 LOTN for the previous day!!!)
I think this is because our server is using US CST timing yet we are a global company and users are active in the Poland and Singapore warehouse when the server scrolls through midnight and the BSFN X41LOT gets the LOTN and the F0002 reference resetting to 0001.

The key to F4108 is LOTN ITM MCU so a duplicate LOTN is allowed if the MCU is different.

X41LOT does 2 things when it gives you back the LOTN.
1) go to F0002 SY = 40 N0008 = MDDnnnn format
(M month DD day of month nnnn = increment NN that resets each day if the MDD used in the call is DIFFERENT to the F0002 value

1a) increment N0008

2) Check newly formatted LOTN of YYMMDDNNNN already exists on F4108
If it does try again +1 until happy

Usually steps 1 and 2 and very quick as the MDDnnnn stored in F0002 is the last known LOTN for that day anyway.

BUT......

If something Calls B4100500 > X41LOTN > B4000620 with yesterdays date as midnight has scrolled passed AND F0002 has already been reset to todays actual new date it will cause a reset.
So for today example is 0420 (yesterday) and (today) 0520
This will then cause X41LOTN to loop through ALL the LOTNs YYYYMMDDnnnn +1 all the way through yesterdays lots to find the next available LOTN....

Now then, when it's doing this loop (which may take 12 seconds to complete) can anyone tell me if the LOCK on F0002 is a one lock until it finds the next free LOTN (so 12 seconds long lock)
OR
Is it a load of small millisecond locks that is freed up each time as it finds and checks the LOTN for use. So multiple 0.045 second (for example) locks


Not sure if that makes sense, but it's to put into words

Please help

 

[nkuebelbeck]

Try it on a fat client. Set the clock and run the bsfn. my 2c

 

[BOster]

At a quick glance your analysis sounds correct. As nkublebeck said try to replicate on a dev client. If that's the way the logic works to determine the next LOTN and you want to stick with that logic then I think I would mod the logic that if the system clock is +/- 1 second from midnight to sleep for 1.1 seconds then try again.

 

[johndanter]

Thanks
Yeah I've tested the midnight flip a few times now. It does do it. Also version of P4312 will use the date you started not the date you click ok to form the LOTN

The trouble is we have DSI scripts and a .NET BSSV wrapped versions all doing the same thing apart from the fact they get the LOTN first and then pass it in.
P4312 doesn't, you get it when you click ok based on the date you started.

I guess I just need to flip to to a long running and ask the DBAs what they see

P4312 calls X41LOT and then X41DUP to check duplicates using F4009. If you call via DCLInk (ND3N4216) it doesn't check system constants

 

[johndanter]

What I think maybe happening is....

The midnight flips are resetting to 0001. If this happens twice, especially with system vs cached dates, it can give 2 consumers of LOTNs the SAME reset 0001 value

But.... E1 BSFNs check that the LOTN is unique using X41DUP.
IF at that moment in time the LOTNS have not been committed to F4108 proper yet, then both will pass the test at that moment in time and space and allow the validation to pass.

Then later on they are both committed to the database and that's ok as F4108 is not unique on just LOTN

What do you guys think will happen if I suggest to put a unique key constraint on F4108? Probably a right mess right? As I'll have cardex records and F41021s here there and everywhere with no F4108

Does anyone have any other ideas

 

[jolly]

Hard to get one's head around this without looking at the code, but... if the X41LOT is the first call in all cases and if all cases are interactive users not batch processes so can live with a little delay, then I would have thought the "if within a second of midnight then wait a second" (or maybe fail the call if handled elegantly) would be workable. But not sure from your subsequent comments if there's a reason why that won't do?

 

[johndanter]

Hard to get one's head around this without looking at the code, but... if the X41LOT is the first call in all cases and if all cases are interactive users not batch processes so can live with a little delay, then I would have thought the "if within a second of midnight then wait a second" (or maybe fail the call if handled elegantly) would be workable. But not sure from your subsequent comments if there's a reason why that won't do?

Yeah that may solve stuff, but would still pass in the day before

The issue is poor coding on E1s part to allow the date to flip to a lesser date. It needs to do this for the 1231 > 101 flip on new years, but that should be the ONLY reason
Or if the year part of the inbound date is less than. Not on daily midnight changes.

Total bug in E1

 

[jolly]

Yeah that may solve stuff, but would still pass in the day before

The issue is poor coding on E1s part to allow the date to flip to a lesser date. It needs to do this for the 1231 > 101 flip on new years, but that should be the ONLY reason
Or if the year part of the inbound date is less than. Not on daily midnight changes.

Total bug in E1

Raise a SAR and then lets take some bets on it being accepted... and resolved!

 

[johndanter]

Oracle told me to get an enterprise server for each time zone

I don't think they understand the issue. Timezones exaggerate the issue yes, but it's not the reason it happens. You can do this yourself using 2 or 3 sessions of P4312 and using the same LOTN all in the same time zone