SQL Server Performance Forum – Threads Archive
sql server lock release issuesI 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? Regards,
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?