Filters and Find/Next Loop Question

Dakkon

Assume we have a code snippet such as what follows:

SomeTable.SETRANGE(field1,<somevalue>);
IF SomeTable.FIND('-') THEN
REPEAT
   <do some processing>
UNTIL SomeTable.NEXT = 0;

What happens if during the <do some processing> portion it changes the field1 value of the current record. I can't remember, and I was hoping someone might help me out without having to run some tests.
Will 'NEXT' still proceed to the next record correctly or will some records get skipped? Oh and to complicate things more, what happens if you have 2 or more processes all running this same logic. What happens when one of the other processes updates the value of field1 in a record that the current process hasn't reached yet? Will it get filtered out? Will the Next loop work correctly? (I should clarify that when I say "hasn't reached yet", I mean the current process has already begun looping through the records before the other process updated the specified record)
Thanks in advance everyone.

kriki

Well, if you update while you are looping, you can get into an endless loop, or skip records, so the correct way to do it is this (In some cases, you don't run the risk, but I don't take the risk anyway also because of good programming practices):

TheTable.SETRANGE("The Field",'The Value');
IF TheTable.FIND('-') then
  REPEAT
    TheTable1 := TheTable;
    TheTable1."Field X" := 'New Value';
    TheTable1.MODIFY(TRUE);
  UNTIL TheTable.NEXT = 0;

If another process is doing the same, on Navision it will be blocked by current session because of tablelocks.
On SQL, a session can get blocked until the first is freed or the second has waited too long (see "Timeout Duraction" in tab advanced when creating a new DB or altering the DB). It is also possible one of both has a deadlock, EVEN IF THE RECORDS THEY CHANGE ARE COMPLETELY DIFFERENT! This last because in 1 page are records of both sets and SQL can also lock pages in stead of records.

Dakkon

What if there is a COMMIT statement among the processing code, so that each iteration commits it's changes immediately? This would solve locking issues for the most part wouldn't it (assuming operations before the commit only take milliseconds).
How would this particular operation deadlock? Since they are locking the same table it shouldn't be a possibility should it (or do you mean if each process is also using another table in common)?
Oh and thanks:)

Alex_Chow

Yes, if you use the COMMIT command, it will change the value immedately even if the process breaks during the middle.

Regarding locking, the COMMIT function alone does not lock the table. The function LOCKTABLE locks the table. If you modified a record during a process while another person is on the same record, if they try to modify something they'll get a "Another User has modified...." message.

Dakkon

Right, I wasn't implying that commit would lock a table. The lock was implied by modification of the table. I was referring to quick commits to avoid multiple processes from locking each other while working on the same table.

bbrown

Avoid fixing problems by using sloppy programming techniques. Imbedding explicit commits inside of a process loop is a very bad practice (sometimes unavoidable). Commits must be placed in a way that will leave the database in a known state in the event of a failed transaction.

If you do place a commit within the loop, then you must also be able to recover from a partially completed loop.

Dakkon

I completely agree. In the case of what I'm designing though I'm afraid it will be inevitable. Without getting too thick in details it involves multiple NAS's reading from a central processing queue to perform load balancing of incoming requests. Luckily using commits in this case will cause minimal disturbance. I'm just trying to make sure each of my load balancing request handlers won't collide with each other. The commits will only be after updating the state property of each queue item, so anything after will still get rolled back in case of an error.

kriki

A COMMIT after each record should avoid deadlocks but will increase the time needed to process N records. This because each COMMIT generates some overhead.

You talked multiple NAS for loadbalancing. Loadbalancing what? The performance of the DB? This will not be resolved by multiple NAS's. In this case better 1 NAS... and this risolves also the locking problems between NAS's.

deadlizard wrote:

The function LOCKTABLE locks the table.

Important to note here is that the table is not locked after the LOCKTABLE statement! :shock: This because Navision buffers the write commands on the client and ONLY when the buffer is full or an immediate action is expected from the DB (like a FIND or GET) the LOCKTABLE is send to the DB.
If on the other hand you use the debugger, Navision does NOT buffer the write commands!
Conclusion: if you REALLY want to lock the table at the moment of the LOCKTABLE-command, put a FIND or a GET behind it (even if you know the record you FIND or GET does not exist).

Dakkon

When I said load balancing I specifically mean for response. Some of the requests may unavoidably take long periods of time (I consider even 30 seconds long). During this time the NAS doing processing is effectively locked, leading to failure to respond to other requests. So, the plan is to run multiple NAS's thus balancing requests, so that if one is busy others will pick up the slack. I'm building a MSMQ oriented request/reply system for real time data exchange, and it's being designed to handle N NAS servers.
Incidentally an issue that could impact this, is another thread I posted about SQL NAS and multithreading. Someone told me that SQL NAS is multithreaded, which if this is true I wouldn't have to use multiple NAS's to balance requests for response times. However, at this point I've been unable to substantiate this claim.
I should also note another thing about that code sample: When I used FIND('-'), I would of course be using FINDFIRST in SQL. I do appreciate everyone's feedback, I'm hoping to avoid problems others have already discovered:) Why re-invent the wheel and all that ...

bbrown

You would not use FINDFIRST with a REPEAT..UNTIL loop.

DenSter

Dakkon, please don't take this as a smartalick remark, but do read the new app designer's guide about the new SQL commands. When I first saw the FINDFIRST command, I thought it was a replacement of FIND('-'), just like it seems that you do. It works very differently though, so please do yourself a favor and read about it. There is also a moviefile in the download section that explains the new commands.

By the way, to answer your question...

Don't ever modify any fields that are used for filtering in the rec variable that you are looping in. Instead, use a second variable for the modifying part:

MyFirstRec.SETRANGE(SomeField,SomeValue);
IF MyFirstRec.FIND('-') THEN REPEAT
  MySecondRec := MyFirstRec;
  MySecondRec.SomeField := SomeOtherValue;
  MySecondRec.MODIFY;  
UNTIL MyFirstRec.NEXT = 0;

If you do MODIFY on MyFirstRec, Navision will lose its place in the set of records, because it will retrieve a new set, and it will not include the modified record. If yuo use MySecondRec, it will write the new value to the database, but will leave MyFirstRec undisturbed in memory and it will move to the next one without a problem.

Dakkon

Thanks, that is very good to know. Yes, from the pdf I had found, it was described as though findfirst and findlast were to be used as a replacement to find. Strangely that pdf is one of the ones microsoft released. I think they should clarify better in their docs. If you hadn't mentioned this I would have kept on believing this until I found out the hard way. The doc I had found said that FIND('-') should not be used in Navision with SQL since it resulted in 'SELECT *' a great deal of the time instead of 'SELECT TOP 1'.
Thanks for that breakdown Denster, I always wondered about the specifics behind how the Navision find worked, and what happened when you did 'Rec1 := Rec2', but I never found any clear docs that explained it. Knowing these things can only make me a better programmer:) (as long as I apply the knowledge correctly )
Anyone know by chance whether that Nas SQL multithreaded thing is true or false?

DenSter

The * in SELECT * refers to the columns (it means 'all columns'), not to the number of records that are retrieved.

FIND('-') was meant as 'get the set of records and point to the first one', and was also used for various other purposes. On SQL Server there's not enough information to know what you are going to do with it. Are you going to loop? do you just want the first record? Do you want to know if records exist?

FINDFIRST/FINDLAST basically means 'get the first/last record, but only the one record. You intend to only use that first/last record, and you do not intend to loop through any set of records. You would for instance typically use FINDLAST to determine the last used Line number in a journal table.

If you only want to know if records exist, you should use ISEMPTY. FINDFIRST and FINDLAST return a whole record, ISEMPTY returns only a bit (yes/no) and is therefore quicker.

Now if you want to get a set of records, with the intent of looping through, you use FINDSET, and you can read online help to find out what the parameters are (there are two of them).

So:
- if you want to know if records exist: ISEMPTY
- if you want one record: FINDFIRST/FINDLAST
- if you want to loop: FINDSET(boolean,boolean)

kine

Another "trick" for the loop with modify of the filtered value:

TheTable.SETRANGE("The Field",'The Value');
IF TheTable.FIND('-') then
  REPEAT
    TheTable := TheTable;
    TheTable."The Field" := 'New Value';
    TheTable.MODIFY(TRUE);
    TheTable."The Field" := 'The Value';  //will restore the "position" in the set
  UNTIL TheTable.NEXT = 0;

kriki

kine wrote:

Another "trick" for the loop with modify of the filtered value:

TheTable.SETRANGE("The Field",'The Value');
IF TheTable.FIND('-') then
  REPEAT
    TheTable := TheTable;
    TheTable."The Field" := 'New Value';
    TheTable.MODIFY(TRUE);
    TheTable."The Field" := 'The Value';  //will restore the "position" in the set
  UNTIL TheTable.NEXT = 0;

But I think the way with the extra record-variable is a lot clearer. This is also better if another programmer later has to change it. With the second record-variable it is very clear. With your piece of code the programmer has to think about the reason you put it (which, of course, you can comment [but as some say/write:real programmers don't comment there code,it was hard to write, it should be hard to read ]) the code but then another problem still remains: if someone adds a field to be modified, it might be necessary to also restore that field.
So better use an extra record-variable.

kine

Of course, you are correct. But it is good to know that this possibility is there, because you can find this in some code... :-)

The second variable is the best solution...