59

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?

Elan
  • 6,084
  • 12
  • 64
  • 84
  • 1
    The main question would be: **why** on earth would you want to do this in the first place?? – marc_s Jun 25 '10 at 05:18
  • 5
    I have two SQL Statements running into a deadlock, which I did not expect. See: http://stackoverflow.com/questions/3112699/how-can-i-avoid-a-deadlock-between-these-two-sql-statements – Elan Jun 25 '10 at 16:17
  • 2
    @marc_s I had the same need. Do you know why? I have an sp which needs to be run in parallel and where I have code for multiple tables to be modified by a large number of rows. No idea why this should not be considered as a valid and natural need. Problem is it causes deadlock in SQL Server.. – Mashrur Dec 11 '17 at 00:32
  • I have the same need. Taking out row-level locks is an absolutely necessary feature for the correct functioning of certain algorithms. In order for *sets* of locks to be taken out in a way that avoids deadlock, they have to be sorted and taken out in a well-defined global order (e.g. if the row ids are integers, then lock the set in ascending or descending order). When a database engine arbitrarily escalates row locks to page locks, page-locks inadvertently destroy the locking order, because they end up locking rows with unrelated ids that happen to be on that page. – Triynko Sep 04 '19 at 02:48

3 Answers3

36

You can use the ROWLOCK hint, but AFAIK SQL may decide to escalate it if it runs low on resources

From the doco:

ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

and

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.

And finally this gives a pretty in-depth explanation about lock escalation in SQL Server 2005 which was changed in SQL Server 2008.

There is also, the very in depth: Locking in The Database Engine (in books online)

So, in general

UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93

Should be ok, but depending on the indexes and load on the server it may end up escalating to a page lock.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
  • 11
    just to add, in sql server 2008+ you can effectively DISABLE lock escalation with `ALTER TABLE tableName SET (LOCK_ESCALATION=DISABLE)` – Thiago Dantas Sep 13 '11 at 14:35
  • [based on your comments to Remus Rusanu] means there is nothing we can do to lock a row at all, How does Oracle do it? – bjan May 18 '12 at 06:26
  • @bjan not sure, in general oracle's snapshotted nature means it is less prone to these kind of locking needs anyway. In general I would avoid any rowlock hints it is almost never ever needed – Sam Saffron May 18 '12 at 06:44
  • can you please explain why is it not needed, if you need a question to answer, i will post it – bjan May 18 '12 at 06:58
  • @bjan can you explain a use case? the lock manager is very polished and know way better then us most of the time what to do, if it is not giving you a row lock it is probably running high on locks anyway so it is attempting to improve the situation – Sam Saffron May 18 '12 at 07:08
  • have a look at [I need Row level locking](http://stackoverflow.com/questions/10648448/i-need-row-level-locking) – bjan May 18 '12 at 07:28
  • 2
    "it may end up escalating to a page lock." According to BOL that's impossible: "The Database Engine does not escalate row or key-range locks to page locks" http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx – influent Jul 14 '14 at 18:21
  • @bjan Oracle stores the locks on disk: https://community.oracle.com/message/2706526#2706526 –  Jul 16 '14 at 19:01
  • Perhaps snapshot isolation will solve your issue, whatever it is. https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ – Nick.Mc Mar 02 '17 at 23:07
  • I don´t see how is this the solution, I have 2 open connections, run update with rowlock where id = 1, then in the other connection try select *, will be fully blocked. – Yogurtu Jan 22 '18 at 18:49
  • At a previous company, we used Oracle and had row level locking in almost all our transactions. We never had an issue with deadlocks at the database level. You can hate Larry Ellison all you want, but Oracle is a marvelous database :-) – Jose Aug 13 '19 at 14:32
15

Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:

ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 4
    An important note is that this may escalate to a table lock if it runs low on resources – Sam Saffron Jun 25 '10 at 00:45
  • 9
    @Sam: Details ;) Truth in advertising: I would **never** turn this OFF on my database. The solution is always to properly design the schema and the queries so that scans (which are the culprits for escalation) don't occur to start with... – Remus Rusanu Jun 25 '10 at 00:52
  • totally agree... its so rare to need these kind of hacks and then end up biting you anyway – Sam Saffron Jun 25 '10 at 00:57
  • 1
    @RemusRusanu Not sure about the problem with design or the query - cause, why should I worry about that when I am writing business logic and at least I am not trying to modify other rows which is causing problem. It is SQL server which cannot manage its resources efficiently enough (unlike, say, Oracle) and pushes you to change your code for that. If I have to run something in parallel, we are in a mess for no good reason. What a shame! – Mashrur Dec 11 '17 at 00:24
  • @Mashrur completely agree. – Umesh Aawte Nov 18 '20 at 10:06
8

You can't really force the optimizer to do anything, but you can guide it. 

UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93

See - Controlling SQL Server with Locking and Hints

Sebastian Job Bjørnager Jensen
  • 538
  • 5
  • 9
mdma
  • 56,943
  • 12
  • 94
  • 128