Unanswered: Problem due to locking in MSSQLServer 2000
Insert or update statements seems to be locking entire page or table rather than locking
the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
In transaction A, I'm updating the 3'rd row. and in another transaction B I'm reading
Transaction B seems to be waiting for transaction A to finish before returning the select results.
In transaction A, I'm inserting new row (4'th row) and in another transaction B I'm reading
here as well trasaction B does not return the row 1 unless transaction A is complete.
Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have returned the results without waiting
for update/insert to finish. As the read is being done on different rows than that of being updated
I have tried both the insert/update as well as select queries with all the possible locking hints
such as ROWLOCK, READCOMMITED, UPDLOCK etc...
The only way select query returns the row without blocking is by using the NOLOCK locking hint. But then this is
not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above issue.