SQL Server Performance

sql server lock release issues

Discussion in 'T-SQL Performance Tuning for Developers' started by anmol_khanna, Mar 2, 2004.

  1. anmol_khanna New Member

    I am writing stuff in MSSQL 2K database and leaving database lock mangement to the database.
    When I do a performance test by having multiple client writing the same thing repeatedly to the database from different machines, then when 1 client is writing things then other client wait. Thats fine and as expected.

    The problem is that sometime when machine1, machine2.. machine20 are writing same thing in a loop (with a considerable delay). Sometimes machine 1 writes things multiple times, b4 machine 2 gets to write even once.

    What can be the reason for this. I would have thought that lock agcquisition will be fifo
    Is this a issue with SQL server lock realase?

  2. Argyle New Member

    With a loop do you mean a cursor? How locks are handled depends on a lot of things. For example how many rows you update, what data is updated and if you use transactions or not impact if key, row, page or table locks are held.

    To control locking you could use transactions. Not a good idea though to open a transaction and have a big delay in it before you commit. Won't work well in a muli-user environment.

    What is your goal? Not to allow machine 2 insert data before machine 1 is finished or to allow them to insert at the same time?

Share This Page