SQL Server Performance Forum – Threads Archive

AllowTableLocks?

Why there is no AllowTableLocks in sql server 2k, only
AllowRowLocks and AllowPageLocks go with SP_INDEXOPTION: SP_INDEXOPTION ‘table_name’, ‘Allow<…>Locks’, TRUE/FALSE ?

---

This option specifies how the index pages are locked ( lached to be more precise) while data is being modified. As index pages don’t have a table, there is no option to lock tables. For locking data pages for the table TABLOCK is provided. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Good point though… There is no way to force SQL to only use row or page locks i.e. to never escalate to table locks… Are you having a specific problem that you are trying to solve? Cheers
Twan

---

What can I do to disallow lock escalation to table level?
I mean, row level locking is acceptable.

---

From what I understand about locking, row-level locking is more resource intensive. Therefore a table lock requires less resources which is a good thing. -chili

---

There is no way you can do that. By providing locking hints, you can specify the optimizer to start with particular kind of lock but during query execution if optimizer is taking lot of memory because of multiple locks, it will escalate the same to next level. For addressing these issues, consider creating appropiate indexs on the table. Normalization to redue the data size can also be considered. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Chilli, row locking is desirable in certain cases wherein there are very few rows impacted by query. This was a considerable perofrmnace improvement in SQL Server 7.0. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

Thanks. My 1st reply was a few secs too earlier.
Yes, I have lock escalation problem: a fairly large table, a half million records,
from time to time, a process kicks in and does some
updates after comparing the incoming data. I have a
row lock on every single update. The worst thing is,
I CANNOT have an index on the unique column which is
one of the where clause fields – I know if I had that
index, there should be no lock escalation.

---

I understand this concept. I was trying to convey that lock-escalation to the table level is not a bad thing. Many people think this is the case when in fact it is the opposite. Think about it. It is easier to lock and entire object then to find specific elements within the object and lock them. -chili

---

Do you experience row locks or you have forced row locks in your query? And could you be more specific about not being able to create index on the unique column? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

This is from MS http://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=323630 Cheers
Twan

---

Chilli, Again elaborating on the same point, table level lock is good from less memory consumption point of view but bad from concurrency point of view. So we should leave it to SQL Server what it thinks is the best option. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

---

An index would not prevent lock escalation… If you need to prevent it then batch updates in a few hundred rows at a time… Cheers
Twan

---

Did you try using PAGLOCK? Bambola.

---

What does your insert do? <br />When you say massive, how massive? <br />How much is performance affected?<br /><br />How many columns in the table?<br /><br />If you have an identity field, could you create a clustered index, which would be ok for insert performance at the end of the index…?<br /><br />What is your ratio between insert/update/delete/select on this table? <br />The index could assist the latter three, but would hurt the insert to some extent…<br /><br />Why is the insert so time critical?<br />Is the insert done during the same time period as the updating, or are both batched?<br /><br />Sorry lots of questions… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Twan

---

Haven’t tried PageLock, but I suspect it could cause the
same problem: deadlock. I think TWan’s batching idea is good. Actually that’s what
we have been doing a lot of other places. This one was
apparently missed because it doesn’t occur as frequent,
but deadlock even once is bad enough. Thanks everyone!

---

Twan, an index will not prevent lock escalaltion but it will hwlp to access the record faster and the locks will be relased soon. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

yep definitely agree. An index could potentially help updates, selects and deletes, but would penalise inserts to some extent (I wouldn’t have thought there would be a drastic difference though). Sounds like Zackhu will choose to batch the updates… Cheers
Twan

---

If I don’t want to rowlock escalated into pagelock so
I run the disallow command. Would that actually make
rowlock escalated into the nect level: tablelock, or
the qyery would run slower by stick to rowlock?

---

Hi Zackhu,<br /><br />if you disallow page locks then row locks will escalate to table locks. The only way to prevent SQL from escalating is to not let the sql take out enough locks to warrant escalation (which is by batch updating <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> )<br /><br />Cheers<br />Twan

---

Sql server does not really escalates locks to the next level. It actually always escalates to table lock. Both rowlock and paglock will escalate to a table lock. This is why using PAGLOCK might make a difference as it will use less resources.
You might want to consider adding an index after all. This together with batch updates as Twan suggested would probably solve the update problem.
And when you have massive inserts, you could drop the index then recreate it. Bambola.

---

]]>