4

I'm getting very poor performance on a stored procedure that does well over a hundred (!) updates to a couple of very small tables and it appears that all the concurrent users are constantly blocking each other.

A full rewrite of the proc is scheduled for later this year, but in the meantime I wanted to see if we can alleviate the problem by forcing row-level locking on each of the affected tables.

On Sybase, you can (or at least could circa 2007) force row-level locking for a table using this statement:

alter table titles lock datarows

On SQL Server, it appears that the only way to get the same effect, is to use WITH (ROWLOCK) on every update or insert statement. And even then, it's only a hint that may be ignored.

Is there a way in SQL Server to force (or strongly favour) row-level locking for all updates to a given table?

Cobus Kruger
  • 8,338
  • 3
  • 61
  • 106
  • 1
    Perhaps the answers to this question might help: http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server – Bridge Jan 28 '13 at 09:03
  • You should also consider using isolation level "snapshot". It avoids locks when reading the data, which was a major problem in out application. – Stefan Steinegger Jan 28 '13 at 09:20

3 Answers3

10

First and foremost make sure the updates are not table scans. In other words you do have the proper indexes (UPDATE needs indexes too...). After you ensure that, upon careful consideration, disable page locks on the index used:

ALTER INDEX ... WITH (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);

Give this operation the same care you would give in joggling a barrel of TNT and a bottle of Napalm...

Additional information (from the comments below):

  • You can disable page locks on the clustered index, but you cannot disable page-locks on heaps (since they're physically structured and would not be possible to do proper lock hierarchy w/o locking the page).

  • Lock escalation is a (related, but) different subject. Locks escalation only comes into picture if the statement chooses row-level lock granularity and it decides to escalate during execution to rowset granularity level. Is true that the OP may be in fact victim of escalation, from how I read the OP I think the more likely cause is just lack of an index (ie. a high lock granularity is chosen upfront because of the scan, no escalation is triggered).

  • The initial granularity level is the result of the engine estimation of the task at hand. If the estimate indicates a large number of rows will have to be locked then it may opt for page granularity instead, since acquiring a large number of row locks is usually problematic. Lack of an index will trigger a scan, which will usually choose page granularity.

  • Also escalation is from row/page to rowset (object) granularity. Going through the intermediate page level first is riddled with concurrency problems so the 'big-hammer' is used.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Why only on the index? You can force row-locks on the table as well. – Stefan Steinegger Jan 28 '13 at 09:19
  • 1
    @Stefan: actually no, you cannot. See [ALTER TABLE](http://msdn.microsoft.com/en-us/library/ms190273.aspx). You can disable page locks on the *clustered index*, but you cannot disable page-locks on *heaps* (since they're physically structured and would not be possible to do proper lock hierarchy w/o locking the page). – Remus Rusanu Jan 28 '13 at 09:24
  • 1
    lock escalation is a (related, but) different subject. Locks escalation only comes into picture if the statement chooses row-level lock granularity and it decides to escalate during execution to rowset granularity level. Is true that the OP may be in fact victim of escalation, from how I read the OP I think the more likely cause is just lack of an index (ie. a high lock granularity is chosen upfront because of the scan, no escalation is triggered). – Remus Rusanu Jan 28 '13 at 09:29
  • I don't understand the difference. I thought that statements usually start with row-locking, and page locks are generated because of lock escalation. However, when the OP updates *different* rows in parallel transactions and has locking problems, it is most probably because of escalation. – Stefan Steinegger Jan 28 '13 at 10:03
  • The initial granularity level is the result of the engine estimation of the task at hand. If the estimate indicates a large number of rows will have to be locked then it may opt for page granularity instead, since acquiring a large number of row locks is usually problematic. Lack of an index will trigger a scan, which will usually choose page granularity. – Remus Rusanu Jan 28 '13 at 10:06
  • Also escalation is from row/page to rowset (object) granularity. Going through the intermediate page level first is riddled with concurrency problems so the 'big-hammer' is used. – Remus Rusanu Jan 28 '13 at 10:08
0

You can use this to disable lock escalation:

ALTER TABLE titles SET (LOCK_ESCALATION=DISABLE)

See the docs about lock escalation.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
0
  1. trace flag 1211
  2. turn off page level locking via alter index

combo of those 2 will give you row level locking. unless of course sql will decide to go directly to table locking :) there is also 1224 flag, but it will ignore your request being under memory pressure, 1211 won't. memory consumption might increase big time, be warned and do some user activity simulation (RML or Benchmark Factory) and run perfmon to observe. I know people that have done it in the past.

Ilya
  • 1