Why might this report be blocking clients?

headley27 · 2006-11-06

I am a little confused why users are sometimes being blocked by various reports in our system.

One example - We had a developer create some code for us and a report they wrote essentially does this:

1) Loop through all the Sales Headers (Filtered: Document Type := Order)

2) If a condition is met; loop through all the Sales Lines attached to the current Sales Order (Filtered: Type := Item)

3) If a condition is met; change a value, and modify the record using SalesLine.MODIFY

4) Return to the current loop

I know that there is a LOCKTABLE function in Navision but it is not called by this report that I can tell.

This report can take 5-10 minutes to run and if a user is blocked they remain blocked until the report has completed. This can cause further issues when blocked users subsequently block other users, etc. etc.

Can anyone shed any light as to why this may be happening?

Can any code be run to 'unblock' users at specific intervals; for example at each new Sales Header (Step 1)?

We are using Navision 3.70A with SQL Server 2000.

Any help would be greatly appreciated.

Thank you.

Captain_DX4 · 2006-11-06

As soon as you make a change to a record (MODIFY, INSERT, DELETE, RENAME), a LOCKTABLE is implied.

headley27 · 2006-11-06

Good to know. Thank you.

I also found this which seems to be what I'm experiencing here:

kine wrote:

'It is mainly wrong SQL indexes (created for keys with MaintainSQLIndex = yes) with low selectivity (for example key beginning with Document type etc.) - when you insert or modify one record, you will "lock" whole page in the index for the same "Document type" and nobody is able to insert or modify another record on same page... same problem is for SIFT tables, where are the records locked for the bucket... and if you optimize this, the possibility of the locking is much lower...'

http://www.mibuso.com/forum/viewtopic.php?t=8775

headley27 · 2006-11-06

It seems that the SalesLine.MODIFY code is in fact is causing the lock.

Is there a function/property that can free up this table in between SalesLine.MODIFY writes to the database, so that clients do not have to wait until the entire report has finished processing (i.e. after every record in the Sales Header loop has completed)?

headley27 · 2006-11-06

Does COMMIT handle this?

TonyH · 2006-11-07

I wouldn't use commit unless you really have too. Once you hit commit thats the last roll back point. So if there is an error after the commit the data will only roll back to that last commit.

Does the report show a progress bar?

how is the Sales line loop structured? is it a dataitem or a loop? (I know you put Salesline.modify but not sure if that was for ease of readin)

Is it a modify, or modifyall?

T

Captain_DX4 · 2006-11-07

Any way you can make the report post the records in smaller batches? If you can afford to lose rollback for any modifications, then you can use COMMIT in between iterations of the report. Be warned that choosing to use COMMIT will actually slow the processing of the report (takes longer to save and store the version after each COMMIT, as opposed to waiting for a bunch of transactions to occur before COMMIT), but it would definitely help for the user lockouts.

Also you can check into the command rec.RECORDLEVELLOCKING in the SQL version, which may allow you to do what you need in the table. I'm not sure if it would completely avoid the problem of lockouts, but it's worth a try.

headley27 · 2006-11-07

Thank you all.

TonyH

The developer wrote a blank report that essentially runs a function.
There is a page counter at the bottom.
It is not a DataItem, it is a Loop.
The code is SalesLine.MODIFY, not MODIFYALL (I'll have to look at what the difference is).
p.s. Don't mind the caps, I'm not yelling

Captain DX4

We are posting the records in batches that are as small as we can really get them.
I could commit at the bottom of every loop. That would be fine in this instance.
RECORDLEVELLOCKING would be perfect, if I can figure out how to implement it properly. I'll take a look and see what happens.

Thanks again.

bbrown · 2006-11-08

The command RECORDLEVELLOCKING does not implement record level locking. It only test whether record level locking is support. This is used to determine which database you are running on. If RECORDLEVELLOCKING returns TRUE, then you are on SQL.

SQL will only use record level locking if it is efficient. When processing large record sets, SQL will quickly escalate to a less granular locking level.

TonyH · 2006-11-08

Can you give us some more info as to what the code is supposed to be doing?

i.e. the criteria to modify and what it modifies it too?

Captain_DX4 · 2006-11-08

bbrown wrote:

The command RECORDLEVELLOCKING does not implement record level locking. It only test whether record level locking is support. This is used to determine which database you are running on. If RECORDLEVELLOCKING returns TRUE, then you are on SQL.

SQL will only use record level locking if it is efficient. When processing large record sets, SQL will quickly escalate to a less granular locking level.

Good point I'd forgotten that. I spend too much time in native databases! *wink*

Captain_DX4 · 2006-11-08

headley27 wrote:

The developer wrote a blank report that essentially runs a function.
There is a page counter at the bottom.
It is not a DataItem, it is a Loop.
The code is SalesLine.MODIFY, not MODIFYALL (I'll have to look at what the difference is).
p.s. Don't mind the caps, I'm not yelling

S'okay. We know commands when we see them! *wink*
If you can use a MODIFYALL, then that would absolutely be more efficient than looping through code and performing the MODIFY as you go, but only marginally.

headley27 wrote:

We are posting the records in batches that are as small as we can really get them.
I could commit at the bottom of every loop. That would be fine in this instance.
RECORDLEVELLOCKING would be perfect, if I can figure out how to implement it properly. I'll take a look and see what happens.

Yeah, forget the RECORDLEVELLOCKING... I was waaaay off on that. But if you can perform a COMMIT at the end of each MODIFY, or even set it up on a counter to perform the COMMIT after 100 loops, for example, then the database will remove the lock temporarily and other users will be able to have their posts go through. It'll start the blocking-train, but everyone will have a turn to get their posts through with smaller wait times in between. Obviously, as I stated, you take a hit on database performance in this case. But at least the users are less annoyed.

headley27 · 2006-11-08

bbrown wrote:

The command RECORDLEVELLOCKING does not implement record level locking. It only test whether record level locking is support.

Thank you for clearing that up.

TonyH wrote:

Can you give us some more info as to what the code is supposed to be doing?
i.e. the criteria to modify and what it modifies it too?

The code modifies a custom field in the Sales Line table when a set of conditions is found to be true. It is lengthy to explain but the code and comments below should give you an idea of the code's structure.

Basically, this has been put in place so our warehouse personnel can action orders as efficiently as possible based on a routine we have specified.

Captain DX4 wrote:

... if you can perform a COMMIT at the end of each MODIFY, or even set it up on a counter to perform the COMMIT after 100 loops, for example, then the database will remove the lock temporarily and other users will be able to have their posts go through. It'll start the blocking-train, but everyone will have a turn to get their posts through with smaller wait times in between. Obviously, as I stated, you take a hit on database performance in this case. But at least the users are less annoyed.

Thanks. This is exactly what I am trying to achieve ... I think.

I say 'I think' because more than just posting to the database is effected; select statements are blocked as well (i.e. viewing a Sales Order) .

I thought I had attempted this properly earlier (COMMIT after every MODIFY) but I guess not.

Perhaps I can elaborate a bit better.

Let's say a user is working in Order 10000 using the Sales Order form. The report is started by another user and the code modifies a Sales Line linked to Order 10000. The user working in Order 10000 is blocked (the screen appears to hang) until the report has finished running. Should the COMMIT function help in this instance?

There are multiple tables that this code modifies but if I can fix the issues caused by the SalesLine.MODIFY statement, I can fix the rest.

The report triggers a function that resides in the Sales Header Table. The function loops through all the Sales Lines linked to the current Sales Header and returns to the report. The report moves to the next Sales Header and repeats until all Sales Headers are processed. Again, the Sales Header is filtered so that only orders are processed. The report moves onto the next table, etc., etc.

The modified Sales Header function with the COMMIT statement included would have read as such:

IF SalesLine.FIND('-') = TRUE THEN BEGIN
  REPEAT
       //Additional Code Here
       IF Condition = TRUE THEN BEGIN
          SalesLine.MODIFY;
          COMMIT;
       END;
    END;
  UNTIL SalesLine.NEXT = 0;
END;

I made the above change to a test database and a user assisting with the test experienced a significant block, even with the COMMIT statement.

Am I implementing it incorrectly?

Thanks again for all your help.

bbrown · 2006-11-09

Let's say a user is working in Order 10000 using the Sales Order form. The report is started by another user and the code modifies a Sales Line linked to Order 10000. The user working in Order 10000 is blocked (the screen appears to hang) until the report has finished running. Should the COMMIT function help in this instance?

This will cause another issue. Taking the above example, the report modifies a sales line on the order another user is editing. Then the user attempts to change the same line. They will recieve the infamous error "Another user has modified the record..."

headley27 · 2006-11-09

Thank you bbrown.

I will try the test again. This scenario is much better than the one we are currently experiencing.

girish.joshi · 2006-11-09

What bbrown says is correct, but that issue is inherent with the design of your report.

Basically, the problem is that you are making decisions based on your sales lines, but sales lines are in general "works in progress", and a lot of people are working on them!

You shouldn't be making any decisions in your business flow until the sales order is released (better yet, try to use posted documents), and as such, no one should be working on it.

I see these sorts of issues a lot with a companies that haven't used warehouse managment, and are basically using the sales order as a kind of pick sheet.

Can you describe the business problem in more detail?

headley27 · 2006-11-09

BlackTiger wrote:

I STRONGLY recommend you to review your design. Your design is bad. Not wrong, but just bad.

girish.joshi wrote:

You shouldn't be making any decisions in your business flow until the sales order is released (better yet, try to use posted documents), and as such, no one should be working on it.

I agree. Trust me. I certainly will review other options.

I have concerns since we are in so deep with the developer's 'bad' code (all over our system).

It will take a lot of time to correct much of it (this is the tip of the iceberg), but at the very least this forum has us asking some very interesting questions.

Some great ideas have already come to mind as to how we can work towards fixing some of these issues and as a result, they should greatly improve efficiencies.

Although the problems we have been experiencing remain, the question I asked was answered thoroughly so I will mark this post 'Solved'. I knew that the developer did a poor job in many areas of our system, but it has opened up my eyes so much more. This has helped me to find many flaws I didn't know existed and will start me down the right path to correct them.

Thank you all your help!! =D>

Why might this report be blocking clients?

Answers

Categories