How to get rid off exclusive table locking in SQL

Anyone knows how to get rid off exclusive table locking in SQL because our users cannot work due to X TAB locks. The system is heavily loaded with transactions on SQL2000 SP3a. The memory usage on SQL seems to stay at around 100% from the 3Gb. The SQL server has DUAL processors The SQL is about 13Gb in 3Months, with 1.6 Million inventTranses. We have X TAB locks mostly on inventSum, inventTrans, salesline, inventJournalTrans,InventdimId,SalesTable. Mostly on Sales Table when our users trying for invoicing (Attach Sales Tax) at a time. This kills the user experience… Plese help.

Hi As far as I know, MS SQL has something like a “lock escalation” procedure. This means that when (from Axapta), some records are locked, the MS SQL first makes a record lock. If more records are to be locked, it makes a page lock. And if even more records are to be locke, it makes a table lock. I do not know if there’s anything you can do against it from the SQL side, coz I’m not really an SQL specialist (the wisdom above was told to me by one). In Axapta, however, you might consider activating the IMTS system. I think a description of it is available on the SP2 docs.

Hi Subha, What version of Axapta are you using? Is it 3-tier or 2-tier architecture? Also can you find out lock configuration details of your SQL Server please. You can do this by running - sp_configure locks Regards, Harish Mohanbabu

Hi Harish, We are using Axapta 3.0 IN(SP1). All clients are configured as Thin client(3Tier)LAN/VPN. We have not done any modification to SQL Server (2000 SP3a). Except turn off ‘Auto Create Statistic’ Sql Server lock statistic is as follows: Name Mimimum Maximum Config_Value Run_Value locks 5000 2147483647 0 0 The tables frequently locked are Salesline, SalesTable, Invent Journaltable, inventjournaltrans. (Update Lock, Deadlock, Exclusive lock) Thanks in advance

Hi Subha, we had the same experience with one of our customers; whenever someone wanted to post one or more invoices the number of locks in SQL Server would increase so quickly that within seconds all other users were blocked. After some investigation we found out that the query SalesUpdate was used to determine which lines could be invoiced and that all datasources in this query had their update-property set to ‘Yes’, which specifies that records in these datasources are updated when the cursor is moved, thereby locking these records. We decided that for the determination of lines to be invoiced the update-property should be set to false, so we added the following lines to the ‘chooselines’ method of the SalesFormLetter class (right after the inline function and before the ‘ttsbegin’): for(nr = 1; nr <= chooseLines.query().dataSourceCount(); nr++) { chooseLines.query().dataSourceNo(nr).update(false); } After we’ve implemented this, the locking problems were over. We decided not to change the SalesUpdate query, 'cause it’s probably used somewhere else where the update-property should be set to ‘Yes’. Hope this helps Hans Brederode