How to force SQL server to use row locking for specific update/delete statements

Question

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

Answer 1

Some notes from the MSDN Table Hints topic show limitations on how the ROWLOCK hint may work or not work. Remember, this is a HINT not a REQUIREMENT.

For examples of issues that may not match your expectation:

Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

Also, though probably less likely, are the following issues:

The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.

Likely there are other interactions that will affect the state of the hints.

Answer 2

I know you said with (rowlock) did not appear to be working
Are you including a <> for the set as then it will avoid an update lock

update table with (rowlock) 
set col1 = 1 
where col1 <> 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?