Locktable with Navision 3.70 under Microsoft SQL Server 2000

oarnaudon · 2006-01-10

Hi,

i've a big problem using Navision 3.70 and SQL Server 2000.

OnAfterGetRecord of my report :

IF NOT "Buffer Stock Keys".GET(FORMAT(Mysess."ID connexion"),FORMAT(i),USERID) THEN BEGIN
  "Buffer stock".INIT;
  "Buffer stock"."Code 1" := FORMAT(Mysess."ID connexion");
  "Buffer stock"."Code 2" := FORMAT(i);
  "Buffer stock"."Code utilisateur" := USERID;
  "Buffer stock".INSERT;
END;

Object : i try to insert 2000 records in a table ; before every insert, i check if the record exist or not. I do the same think for the both session, and the key wich contain "USERID" is different for each session.

When this report is running into 2 different sessions, while the insert as not finished in the first session, the second session don't start, because the record "Buffer Stock" is locked.

When IF NOT "Buffer Stock Keys".GET is disabled, it works great.
I think, the GET locked the table.

Did someone have an issue to this problem ?

Thanks a lot !

Best regards,

Oliver[/i]

ara3n · 2006-01-10

You don't need the get statement.

just do this
if "Buffer stock".INSERT then;

Also did you look at TransactionType property for the report?

oarnaudon · 2006-01-10

Thanks for your speed-response, but, it's not possible not to use the .GET !

It works on several temporary tables, and i should use .GET !

An another idea ?

Thanks !

Best regards,

Oliver !

ara3n · 2006-01-10

well if "Buffer stock".INSERT then returns a true or false if it exists or not.

Also did you look at TransactionType property for the report?

oarnaudon · 2006-01-10

Hi,

the report was only to do some tests.

In the customer database, it's a function in a codeunit.

On the same table we have to use SETRANGE and FIND function.

Sorry if the first post wasn't precise.

We have also post an message in MBS Support, but at this time, no response.

Thanks !

Oliver !

ara3n · 2006-01-10

Try using another variable to insert and instead of the get/find variable
"Buffer Stock Keys 2"

IF NOT "Buffer Stock Keys 2".GET(FORMAT(Mysess."ID connexion"),FORMAT(i),USERID) THEN BEGIN
"Buffer stock".INIT;
"Buffer stock"."Code 1" := FORMAT(Mysess."ID connexion");
"Buffer stock"."Code 2" := FORMAT(i);
"Buffer stock"."Code utilisateur" := USERID;
"Buffer stock".INSERT;
END;

here are the rules for locking on sql

SQL Server:
• LOCKTABLE does not lock the table
• GET locks the record until the end of the process if LOCKTABLE, INSERT, MODIFY or DELETE are called first
• FIND, NEXT locks the recordset (+ 1 record above and 1 record below) at the beginning of the loop until the end of the process if LOCKTABLE, INSERT, MODIFY or DELETE are called first
• CALCSUMS locks the SIFT records until the end of the process if LOCKTABLE is called first
• INSERT, MODIFY, DELETE lock the record until the end of the process

oarnaudon · 2006-01-10

I also tried to use another variable, but when the second session begins, i've the same problem !

ara3n · 2006-01-10

• FIND, NEXT locks the recordset (+ 1 record above and 1 record below) at the beginning of the loop until the end of the process if LOCKTABLE, INSERT, MODIFY or DELETE are called first
• INSERT, MODIFY, DELETE lock the record until the end of the process

So it looks like one record above your find first get locked and one bellow?

oarnaudon · 2006-01-11

Thanks, i know that the record above and below are locked !

But, is there an issue to this problem ?

With one session there is not a problem, it works, but with two sessions or more the record are blocked !

Thanks !

ara3n · 2006-01-11

are you doing anything else beside this in the routine.
SQL uses record level locking first, if it become too complicated to maintain Serializability it changes to Table level locking.

What do you mean with

With one session there is not a problem, it works, but with two sessions or more the record are blocked

Do you mean the record or the table?

oarnaudon · 2006-01-11

what means Serializability ?

ara3n · 2006-01-11

http://www.cs.unc.edu/~dewan/242/s99/notes/trans/node3.html

kine · 2006-01-12

oarnaudon wrote:

Thanks, i know that the record above and below are locked !

Since Navision 4.0SP1 it is not true! It is on SQL what will lock - record, page, table etc...

I know, that you are talking about 3.70 but I want that you know... ;-)

And the problem of locking can be hidden in bad keys. Becouse if you are inserting something, the part of key which will be updated is locked. Try to disable all keys which are beginning with something other than USERID (you can set MaintainSQLIndex to No for the keys, not delete them). If you will have only key with this first field, only tha part for the one ID will be locked, but it is hard to assume, what will SQL do...

Locktable with Navision 3.70 under Microsoft SQL Server 2000

Comments

Categories