Problem due to locking in MSSQLServer 2000

[sangramd]

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.

scenario 1)
In transaction A, I'm updating the 3'rd row. and in another transaction B I'm reading
row 1.
Transaction B seems to be waiting for transaction A to finish before returning the select results.


Scenario 2)
In transaction A, I'm inserting new row (4'th row) and in another transaction B I'm reading
row 1.
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
or inserted.
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.

[skhanal]

Do you have any index on the table?

If SQL Server needs to do table scan then all rows may be locked during update.

[MAK]

Give table hints.