NAS and locking - SQL vs. Native

thebo

Hi there,

I've experienced the following behaviour with an external application we integrated into a customer's 5.0 solution using NAS:

-while running on native DB, the NAS session waits when a table it needs to write into is currently locked by one of the users using the regular client. Alas, everything's fine.

-running the same code with SQL DB, the NAS service crashes when trying to write into a table that is currently locked by somebody else. The service stops and there is an error in the event log (same as if you use locktable in native with wait parameter set to false) so the service has to be restarted.

To narrow the problem I tried the same code, but run with the client (instead of NAS), and then it worked fine both on native and SQL.

I don't think it matters, but in case you wonder: it's SQL 2005.

The only possible conclusion to me would be that NAS handles locked tables different from the way the client does, but only when using SQL.

It seems like with NAS on SQL, locktable(true, false) just won't wait for the record or page to be released but end up in an error all the time.

Can anybody confirm this or does anybody have some experience or advice that could be helpful? Any idea would be appreciated

Thanks in advance!

ara3n

You can try to get the 5.0 update or wait for 5.0 sp1

stryk

With the "native" DB a LOCKTABLE is indeed locking the table, causing plenty of blocks.

With SQL Server a LOCKTABLE sets the TRANSACTION ISOLATION LEVEL to SERIALIZABLE; and the following queries flag the hint UPDATELOCK.
Mostly this also results in a block, if another process tries to access the data from the seriealized transaction.

But have in mind, that with SQL Server you have a LOCKTIMEOUT feature in NAV. If the NAS crashes, it probably got a timeout - then the process is killed and rolled back - and has a problem to resume; same could happen in case of a deadlock situation.
I know that there is a support case, dealing with problem of NAS to recover in case of a "crash". This should be solved in SP1 for NAV 5.0; but - if I don't err - also a hotfix for NAV 4.00 SP3 should be provided.

If indeed the LOCKTIMEOUT is the problem here, then you could disable it from C/AL code; e.g. explicitly for the NAS:

LOCKTIMEOUT(GUIALLOWED);

or

IF NOT GUIALLOWED THEN
  LOCKTIMEOUT(FALSE);

Hope this helps you a little!

lyot

Is this also a know issue on 4 SP2?