Options

Lock and SQL server

giulio.cipogiulio.cipo Member Posts: 77
HI,

reading documentation about SQL server i read that navision make a record lock instead of table lock (standard option).

I develop a procedure that create 700 purchase line and this procedure need 2 minits but during this time other user are blocked and they wait that i finish. i check the code and modified locktable with code "if not recordlevellocking then locktable" so taht the sistem don't lock table, but i continue to have the same problem.
Any suggestion about the reason of this?

one more question how can i undenstand if SQL lock table or record because my dubt is that for a reason that i don't know the SQL sever change lock from record to table automaticaly.

Thank for Help
Giulio

Comments

  • Options
    lalailalai Member Posts: 9
    When you start writing to a table you automatically lock it.

    Isn't it possible to redesign your code? 2 mins. for 700 lines is much to long. Could a solution be to create all the lines in a temporary table and insert them in the destination table at the end (i guess you must be doing some calculations since the insert takes so long time).
  • Options
    kinekine Member Posts: 12,562
    There is still recordlock, no table lock on SQL, because in all SQL statements are recordlocking hints. But problem are INDEXES... they must be updated, and if you have index begining with for example Document type, and you update some order, all other users which want to modify order will be locked because are on same part of table... :-)

    For good performance on MS SQL you need optimalize the indexes on tables (standard objects are not optimized for MS SQL [-( ) Turn all MaintainSQLIndex on all keys on the table (exclude the primary key) and create new keys with good selectivity (fields with many possible values) - for example Document No. (you can create for each such a field new key - but you need only few keys for SQL - for only fields you are used for filtering.

    And turn off MaintainSift for work tables (sales header, sales lines etc.). It is not needed for MS SQL and these tables have only few records - MS SQL will fast calculate the value and inserting will not lock the SIFT tables...

    Read the SQL Server Resource Kit.pdf and w1w1PerfTGuide.pdf (from MBS Tools CD 4.0)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    giulio.cipogiulio.cipo Member Posts: 77
    Thank Kine,
    you answer help me a lot.
Sign In or Register to comment.