2

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
2

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.

  • I'm going to read the link you sent. Your answer is very good but what options do I have or how would you face the issue? All I want is to make sure there are no Page locks that interfere with other sessions... The scripts that run touch hundreds of tables... – JohnG Nov 20 '15 at 14:22
  • I read ROWLOCK, UPDLOCK, AND XLOCK including the rest of the link. Based on the definition of ROWLOCK, it should work as I thought but it doesn't?!?!?. as for the UPDLOCK and the XLOCK, It can't be used in my case due to the fact that we have separated the processors in to different sessions to improve performance. If I make it serialized, then It would be exactly like it was before (which was way too long as nightly processes) – JohnG Nov 20 '15 at 15:07
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?

  • I added the test scenarios in the description... I will be reading about the Tablock and holdlock. – JohnG Nov 20 '15 at 14:24
  • Yes the updates are all done in the same order as they are all identical scripts... As I commented to RLF, Based on the definition of ROWLOCK, it should work as I thought but it doesn't?!?!?. I can't use serialized processing due to the fact that we have separated the processors in to different sessions to improve performance. If I make it serialized, then It would be exactly like it was before (which was way too long as nightly processes) – JohnG Nov 20 '15 at 15:12
  • You can almost never make a database go faster with parallel updates. Unless exotic you have a single write head on the other end. I suggest you rethink the architecture. Use a producer consumer where you do the updates in the consumer. You can use parallel in the producer. – paparazzo Nov 20 '15 at 15:18
  • I wish I would be able to rethink the architecture but it's been in works for the past 20 years and I've been hired 5 months ago. ;-)... If I would be able to rethink the architecture, I would do it through a Queuing system with priorities... where each Queue would take care of some tables that are related to each other... but I don't have that luxury. I was able to minimize locks by using the the WITH(NOLOCK) hint on selects and Joins (where the table, pages or rows don't need to be locked - since I know other sessions' data don't conflict with each other and that dirty data is good data). – JohnG Nov 20 '15 at 16:37
  • but there's still that slight chance that a page lock can contain data from 2 sessions / processors... forwhich, If I'm able to force the ROWLOCK, I know for sure that I won't have any deadlocks. – JohnG Nov 20 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.