I read this great response to a similar question How to force the use of row locks but I was looking for a per update/delete statement instead of permanently putting the rowlock restriction on an index.
My problem is that I have multiple sessions running the same scripts and updating the same tables giving us some deadlocks... And we know for a fact that there will never be any conflicting rows between different sessions. The problem seems to be caused by page locking (when a page contains data that confilcts between 2 sessions) I've tried using my update and delete statements WITH (ROWLOCK) hints but when analyzing the locks (starting a transaction and statement and then verifying sp_lock in another session), I see absolutely no difference between specifying and not specifying the WITH (ROWLOCK) hint.
any ideas to resolve this issue are welcome.
Edited for more info: I ran different scripts testing different scenarios with and without the "With(ROWLOCK)"... the test scenarios I've tested are:
-- deleting 9 rows of 4600 - no difference between with or without rowlock hint -- 9 row locks and 3 page locks -- COL1 is part of the combined PK -- DELETE FROM RPT_CHK WHERE COL1='RPT_LAST' -- DELETE FROM RPT_CHK with(rowlock) WHERE COL1='RPT_LAST' -- updating 16000 rows - no difference between with or without rowlock hint -- table lock in both cases (which is understandable) -- update price with (rowlock) set cost = cost + 0.05 -- update price set cost = cost + 0.05 -- no difference between with or without rowlock hint -- 3833 rows -- 4028 locks (row and page Locks) -- update client set COL1 = 'a' where ClientId <= 3833 -- update client with (rowlock) set COL1 = 'a' where ClientId <= 3833