Locking Problem

myanilkumar

Hi,

I had designed one customized table & form. Users will key in some transactions and posts from that form. when they posts it moves to history table and also during posting we are scanning all the records for recalculate some fields and update. Simultaneousoly 4-5 users craetes the transactions at time. Sometimes if they post the transaction i am getting the locking problem like "Another user has modified the record" and they whole data they key in will lost.

I am using Navision SP3 with SQL Server 2005.

Please tell me how to avoid this problem. Can i want to change Lock time out or any other things?

Please let me know on this.

Thank u,
Anil

kriki

This sounds that you have to do a LOCKTABLE at the beginning of your transaction, then read the data and then change it. Only after that you should do a COMMIT.

BUT BE CAREFUL! Using a LOCKTABLE and scanning the whole table will lock ALL records until you COMMIT the data. This means that the other users will be blocked until the end of the transaction.

rthsw

Your error is in doing keyhandling or retreiving other records. For Example take a look at the salesheader/Saleslines. 100 Person in a company can enter entrys allways w/o interference. (as long als every users use an own Header) Every Header, and every single Line to every header is a own record, and Navision cheks only modifications of the single record (the timestamp) before storing in the database.

so you have to research the record which makes the errors or the structure of your Table. The Primary Key(s) should make it able to work with N users in N different primary Keys.
Maybe it's the Numbers-table, or it is a central controll-table for all users, or they all insert records in a unfiltered table?

myanilkumar

I had filtered by user ID during insertion of records.

But during refresing of the data there is no filters. we will scan all the records in the table. Is there any solution to avoid this problem?