Removal of LOCKTABLE

Toddy_Boy

Hi all

Was Now
Objects 2.01 Objects 2.01
Client 3.7 Client 2009
Db Proprietary Db SQL 2008

A large amount of bespoke work has been done over many years on the proprietary database. Since moving to SQL 6 months ago I've eliminated a large amount of locking which is great. We still get locks and deadlocks so now my aim is to reduce this further (elimination is probably too much to aim for just yet).

So I am now addressing places where LOCKTABLE is used in the code in places where there is no need for it to be used. As I understand it, LOCKTABLE by itself does nothing in SQL unless we want to insert SERIALIZABLE transactions (I assume this is transactions that need to follow a numeric sequence e.g. posted Journal lines, please correct me if this is inaccurate), therefore;

I have two tables A (primary key Code [20]) table B (primary key Code [20] - used to join to Table A, and Code [10] (for uniqueness).

So, when inserting into Table A, if the primary key is derived from the number series, is my assumption correct in that there is no need to issue a LOCKTABLE on Table A. :-k

Additionally, when inserting into Table B there is also no need for a LOCKTABLE on Table B as the first part of the key is taken from Table A and the second part is deduced from some other processing. :-k

We work in an environment where it would be very highly improbable that 2 users would work on the same Table A record at the same time I would think that the LOCKTABLE could be removed from the code for both Table A and B without too much risk.

Any comments as always are very welcome.

Steve

David_Singleton

Toddy Boy wrote:

We work in an environment where it would be very highly improbable that 2 users would work on the same Table A record at the same time I would think that the LOCKTABLE could be removed from the code for both Table A and B without too much risk.

That's an immediate contraindication of it self. If two users are not going to access the same table, them the lock table will not matter, so leave it there.

Toddy_Boy

Table A record

rhpnt

Well Steve you started to cut a cake here which I like very much. In my opinion and please do consider that I'm a radical kind of guy I would delete all LOCKTABLE occurences from the SQL Client. Since the locking is done implicitly by SQL Server itself, I really don't see any need for this function to reside in NAV code. Still there are some people out there who believe that in some parts LOCKTABLE should be preserved with arguments that not quite comply to standards of modern ERP systems. Fact is, that not even MS took its time to do a code clean up and therefore we can still enjoy the benefits of coding like:
IF FIND(-) THEN
REPEAT
UNTIL...
or
REPEAT
.DELETE(TRUE)
UNTIL...

and yes LOCKTABLE.

Toddy_Boy

Thanks .. I like cake I've gone through the pain of replacing FIND('-') REPEAT with FINDSET(..) REPEAT, also replacing FIND('+') with FINDLAST which have had very good results.

I'm not really concerned with the standard MS stuff, it's more of our bespoke work which is very questionable in places. I think that if the tables concerned have no reason to be locked because they don't rely on a sequence then why lock .. two users should be able to insert into a table at the same time if the primary key is not required to be sequential.

In my scenario, only the No. Series table needs to be locked, once a new number is generated and written to the database, issue a commit to release the No. Series table and carry on processing ...... or am I way off the mark?

rhpnt

Toddy Boy wrote:

two users should be able to insert into a table at the same time if the primary key is not required to be sequential.

That's right but then again in an SQL Server environment two users never insert at the same time. SQL Server queues them and thus locks the table for insertion implicitly.

Toddy Boy wrote:

In my scenario, only the No. Series table needs to be locked, once a new number is generated and written to the database, issue a commit to release the No. Series table and carry on processing ...... or am I way off the mark?

Why lock the table when the number series function is wrapped in a transaction? If you lock the table in a too early stage of the e.g. posting sequence no other number will be issued (e.g. sales quote) until this transaction completes. Is that OK?

Toddy_Boy

Good points, well made. =D>

David_Singleton

Toddy Boy wrote:

it's more of our bespoke work which is very questionable in places. I think that if the tables concerned have no reason to be locked because they don't rely on a sequence then why lock .. two users should be able to insert into a table at the same time if the primary key is not required to be sequential.

In my scenario, only the No. Series table needs to be locked, once a new number is generated and written to the database, issue a commit to release the No. Series table and carry on processing ...... or am I way off the mark?

Yes if its your own code and you have not developed any custom posting routines you should be fine. Though in that case, probably even with Native database you didn't need the Locktables. But modifying things like CU11 and 22 and 80 can't be done just by removing the locks, many of them have evolved in strange ways, and require a rewrite of the underlying code.

As to No Series, there are many different solutions. One is to get the next number, save it in the record then commit. The lock is only really needed if multiple users are creating sequences in the same table.

Also be careful not to introduce deadlocks, which can happen the way you are doing this.

Lastly though, think about why you are doing it. If it is to clean up old bespoke code that you know was written poorly from the get go, then this makes sense. But if you are doing it for performance reasons, then your time would be better spent on a more methodical approach to improving overall performance rather than setting out on a quest to find The Silver Bullet.

Marije_Brummel

LOCKTABLE does not lock the table.

This is a confusing statement.

On Native it locks the table.

On SQL it sets the isolation level for SQL to lock whatever it reads to prevent others from reading the data your are about to change.

If you remove this you might double the reads on your database.

For example:

LOCKTABLE;
x.FINDFIRST;
x.MODIFY;

Will read the record with a lock and do an update.

x.FINDFIRST
x.MODIFY
Will read the record without a lock, then reread the record since you want to change it, then do the update.

Please. Don't let LOCKTABLE confuse you, it does not lock a table.

Toddy_Boy

Good explanation, better than a lot I've read. =D>

Also be careful not to introduce deadlocks, which can happen the way you are doing this.

David; if the process of creating records in Table A and Table B in this example, if you don't mind, can you elaborate on why simply getting the next No. Sequence, updating and committing potentially cause a deadlock?

David_Singleton

Toddy Boy wrote:

Good explanation, better than a lot I've read. =D>

Also be careful not to introduce deadlocks, which can happen the way you are doing this.

David; if the process of creating records in Table A and Table B in this example, if you don't mind, can you elaborate on why simply getting the next No. Sequence, updating and committing potentially cause a deadlock?

That's not really what I said. What I am referring to is that you are assuming that any locktable that is not specifically in place for serialization purposes can be removed, and thus you are removing them. The way you are doing this process could easily lead to dead locks if you remove a lock that was there for the purposes of eliminating a dead lock.

I agree with your logic that there might be LOCKTABLES that don't need to be there, but I would like to see the numbers that show the performance improvements by removing them, compared to spending the time on areas that could give real benefits. As I said if you are simply correcting errors in the original bespoke work it makes sense, but even then if the original developers wrote bad code in one place, you can be 99.99% certain that they did a lot worse than just the locktables.

Toddy_Boy

Ok, I understand where you're coming from now, and you're assumption of 99.99% is pretty accurate.

David_Singleton

Toddy Boy wrote:

Ok, I understand where you're coming from now, and you're assumption of 99.99% is pretty accurate.

:thumbsup:

OK so we are on the same page then.

I initially thought you were doing this for performance reasons, but its clear now. Generally on forums like this it is better to explain WHY you want to do something rather than WHAT you want to do. By explaining the why's you can often get a lot more advise beyond code help.

David_Singleton

BY the way, (and semi off topic) as mentioned by rhpnt, there are a lot of areas in the Base App where the use of locktable is not ideal, and a lot of questions arise as to why it has not been "fixed".

Basically there are two core issues, one is support of Native, second is the support of 80,000 or so installed systems, with almost 1.5 million users. and a ton of custom code, add-ons and verticals.

Once Navision is SQL only, most of the technical reasons will be gone, and a lot of improvements will be possible, but even then, Microsoft can not just abandon all those users by over night rewriting the posting routines. Most installs of Navision have substantial customizations in those areas, and many of those would require complete rewrites.