I’m trying to understand the use of the LockTable command[B)]. We’re having difficulties with CU 333 because it does: SALESORDERLINE.LOCKTABLE SALESORDERLINE.FIND code… code…code… update fields on SalesOrderLine code…code…code… SALESORDERLINE.MODIFY; Is there ANY reason why I cannot FIND my record, update the fields, then do the LOCKTABLE immediately before my MODIFY - so as not to have such a time gap between the LOCKTABLE & MODIFY? If I understand LOCKTABLE correctly, in the absence of a parm, the WAIT parm is TRUE, and the help text says “the system will wait until the table is unlocked, if another application already has locked the table”. I’m [:I]assuming[:I] that it’s because during the time frame between LOCKTABLE and MODIFY, another user is writing to the table. Yet, I’m still getting that big fat error “The Sales Line table cannot be locked or changed because it is already locked by the user with USERID blahblah. Wait till the user is finished and then try again.” I’m running this codeunit in a batch process - building many PO’s from many Purch. Req. lines. I’d love to just WAIT till the user is done…BUT when I answer to the message…my job rolls over and dies anyway. [:(] Assistance appreciated, and greatly needed.[:)]
Hello Faithie, To understand the purpose of LOCKTABLE, consider the following scenario, where new records are to be created with increasing sequence numbers (which, for example, is the case in the G/L posting routines): SomeRec.FIND('+'); SomeNewRec."No." := SomeRec."No." + 1; SomeNewRec.INSERT; // rest of code omitted for clarity Now let’s assume that two users execute this code simultaneously. If the last record currently stored in the table has “No.” 17, you would expect one user to get new record 18 and the other one 19. However, if the sequence of events is such that first user A executes the FIND, then user B, then user A executes the “+1” and the INSERT, and then the same for user B, both will end up with a new record numbered 18. This is because nothing prevents the system from executing any number of FIND commands in any order, returning the same record multiple times to multiple users. If, however, there is a LOCKTABLE immediately preceding the FIND, then user B and any other consecutive users will block at the LOCKTABLE, thus waiting until user A (who came first) has finished execution of the entire transaction holding a lock on the table. User B’s FIND statement will then find the new record previously inserted by user A, and the proper sequence of numbers will be generated. That’s the reason why doing the LOCKTABLE immediately before the MODIFY, after the FIND and updating of the fields, will not work. (Besides, MODIFY will by default automatically execute an implicit LOCKTABLE, so preceding a MODIFY with a LOCKTABLE is redundant.) The purpose of the LOCKTABLE is to ensure that once a process executes the LOCKTABLE statement, no modifications will be performed upon the table by any other process until the lock is released by the first process. In addition, read access will also be blocked for other processes, as long as these processes “guard” their read operations with a preceding LOCKTABLE. A simple FIND will not honor any locks placed by other processes. The exact locking behavior depends on the database (SQL vs. C/SIDE) and the transaction type of the current transaction, but this is the general idea of how things are supposed to work. Does this explanation make sense? [:D]
quote:
Originally posted by xorph
Does this explanation make sense? [:D]
Wow, yes, it makes sense. I showed that explanation to one of our developers who is not that clever. And even he is not THAT clever in english either, he understood. Is Friday today? Everyboby seems to have a lot time (and is mentally out for the weekend already…).
quote:
Originally posted by walter@kirz
quote:
Originally posted by xorph
Does this explanation make sense? [:D]
Wow, yes, it makes sense.
Thanks for the flowers [8D]
quote:
[…]one of our developers who is not that clever.
I hope you’re not going to tell that to your customers… [:0][:D]
YES! Now I understand the LockTable, and I see why the LockTable right before the modify won’t work. However, is there a way I can code the LockTable so that I cannot get into the situation I’m in? Is there a way to avoid continuing in the code until you can successfully lock the table? Would something like this work: IF NOT SALESLINE.LOCKTABLE(TRUE) THEN REPEAT SLEEP(000050); UNTIL SALESLINE.LOCKTABLE(TRUE); I’m new at this, so please don’t laugh [:I] Thanks.
Hi, I think the error message is due to a deadlock problem. This means that you have two separate processes that locks the tables in different order. What you need to do is lock the tables in the same order, which you can use the locktable command for. The locktable command in itself actually does what you want, if it isn’t able to lock then it waits until the other process is commited. This is default behavior with locktable. If you use Locktable(false) then instead of waiting it returns a error message to the user. But as I said, if you get the error message that you get and aren’t using locktable(false) then this is because a deadlock occurs. Here is a deadlock situation: Process A locks table 1 and then after a while locks table 2. Process B locks table 2 and then after a while locks table 1. The problem here is that since they will wait for the table to be unlocked, the two processes will wait forever. But Navision recognizes this and issue a error message instead so that one of the processes can continue. Hope its been helpful Daniel
Daniel, That’s very interesting![:)] Sort of what I was thinking might be going on. But how do I determine which operation/object is locking my against my program? I get the USERID, but unfortunately, those are Customer Service Agents who are entering orders, processing quotes, creating drop shipments, and a multitude of other processes. Is there any way to pinpoint which is doing this, and whether it’s the form, the table, etc… Guess I’m just grasping at straws now[8)]. I’m use to working on mainframes where you can simply refer to a screen that shows you the locking program and all down to the record level. This system is so interfaced between applications (not complaining[;)]) that its difficult to see the connections. But if there is a way, I really need to know how. Thanks.
Unfortnutaley there is no good program or help in order to resolve this (at least what I know of). Basically you need to do a lot of the work yourself. I have faced this problem a couple of times and I used the developer toolkit and performance guide to solve this (which uses the code coverage and client monitor inside NAvision). Basically what you need to do is import these objects, start code coverage and client monitor and then perform the tasks that you are using (but do this one at the time). Then you can see which locking order the process is using which means that when you do the same thing for the next process you should be able to see a difference in locking order. There is also another object where you specify the wanted locking order and then run the processes to see where/if they are breaking the locking order. This is good once you determined which locking order you want to have. You should also look in the code for any unwanted commit commands. This unlocks everything so this command should almost never be used. Sometimes programmers use this in order to run a form in modal mode, but unless you really know that its safe to use it, you shouldn’t… This is basically a pain in the ass… But do it methodology and eventually you will get through it. Mostly its customization that creates the problem, but there are a lot of issues also in standard Navision (even if you are on 3.70a, and definitively if you are using any previous version). Good luck 
Daniel
Heinz, Faithie’s doing a MODIFY, not INSERT, so the No. issue shouldn’t be a problem for her. But even then, wouldn’t something like this avoid locking the table for so long a time: SomeRec.FIND(’+’); … update fields of SomeNewRec … code omitted SomeRec.LOCKTABLE; SomeRec.FIND(’+’); SomeNewRec.“No.” := SomeRec.“No.” + 1; SomeNewRec.INSERT; Of course, if you’re inserting 100 new records, then this doesn’t make much difference, as the table remains locked after the first LOCKTABLE (it would be great to have a way of unlocking a table without causing either a rollback or COMMIT; I now realise that this would have to be some sort of TEMPORARY_COMMIT i.e. it could still be rollbacked, but this would then create other problems). Alastair
Alastair, MODIFY, INSERT, DELETE etc. all behave in the same way with respect to table locking, so this does not make any difference. Once the table is actually changed in any way, a lock is placed on it. My example was for illustration purposes only and does not claim to solve Faithie’s specific problem [;)]. Your code example suffers from the problem that the second FIND(’+’) will most likely cancel out all the modifications done right before it, because the original record will be fetched a second time from the table. Unless, of course, you do a MODIFY in the omitted code, which will place a lock on the table and eliminate the need for the explicit LOCKTABLE. In any case, your observation is correct that doing many modifications to a table within a single transaction will keep the table locked continuosly throughout the entire transaction after the first actual table update is executed, and there is no way to avoid this except for an explicit COMMIT, which, as we all know, is to be avoided at all costs [;)].
Well, after long search, I may have found my culprit! I decided to run codecoverage on the actual process that we’ve began to see the locks occur in. Just makes sense that if you haven’t been getting them, and suddenly you do - and you’ve just started using this new codeunit…well, duh! So I ran code coverage on it, then found out what I wish I’d known all along on code coverage! I ran it, pulled up the object list, clicked code on one of the MANY objects, then once on the code itself, clicked SHOWALL to remove the object filter from the form. Then I modified FORM 566 to show all the fields of the code coverage table (to get the object name and number that the line of code was in). Then I did a filter for LOCKTABLE in the LINE field, and wala! I’ve got a list of all the locks that are set in the job! And guess what - we’ve got an archive process that locks the sales header, and never the sales line! There’s my deadlock![8D] So, little by little, we learn, and we fix… Thanks for all your help Friends! Dunno what I’d do without this forum some days![:)]