Post reply

Performance problems due to Table Locking

  • April 21, 2004 at 3:25 am

    #163547

    I have a situation that presents a problem to certain users of our application.

    There are 2 business processes that occur :

    1 process updates records in a table based on the users selection,

    the other process bulk updates many (possibly hundreds of thousands) and inserts many records in the same table.

    These 2 processes will never overlap in the records that they update. This is due to the business rules that specify which records each process can update.

    The problem is that if the second process is running and updating its bulk set of records, if a user runs process 1, process 1 waits until process 2 is finished before continuing, due to the way that SQL locks this table once sufficient numbers of records have been updated by process 2.

    Does anyone know of any way that I can disable this feature for these 2 processes, as due to the fact that the processes can never update the same data, there is no need to lock the process 1 from updating records in the table.

    All the table modifications are made using UPDATE statements in process 1.

    Any help would be appreciated.

    Thanks

    Steve

     

     

  • April 21, 2004 at 5:29 am

    #503618

    You can use the "with (rowlock)" hint in your statements.

    The traceflag 1211 (DBCC traceon(1211)) prevents all lock escalation on the whole server.

    Joachim. 

  • April 21, 2004 at 6:10 am

    #503624

    Any possible way of doing this per database ?

    The production system is running many other apps not written by ourselves, and I don't want to affect any of the other databases.

     

  • April 21, 2004 at 6:48 am

    #503629

    (It seems my previous reply did not arrive.)

    No, you wil need hints.

    It is possible let a certain table always use table locks, but not to let it always use rowlocks

    BTW, the only negative result of the flag is that more memory is used.  If an application demands table locks it is in the statement and the flag does not overrule that.   You will get the memory problem anyway, no matter what method you use.

    You could also use much more memory and hope it is enough to prevent escalation.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply