How to force SQL server to use row locking for specific update/delete statements 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 &lt;= 3833
-- update client with (rowlock) set COL1 = 'a'  where ClientId &lt;= 3833
I know you said with (rowlock) did not appear to be working
Are you including a &lt;> for the set as then it will avoid an update lock  

update table with (rowlock) 
set col1 = 1 
where col1 &lt;&gt; 1


with (tablock holdlock) might fix deadlock
as then you only have one update at a time
it is worth a try  

Are your transactions updating tables in the same order?