GilaMonster (3/25/2013)
abitguru (3/25/2013)
GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)You can. So you'd prefer SQL to run out of lock memory, fail any pending queries with an error (out of lock memory) or possibly even crash rather than escalating locks?
I think its my mistake for not to explain me well (and my out of practice english)
I have a parameter table to number invoices for example, with around 20 records,
this records are independent each other, so if I read parameter number 1 I want noone can read this row. I know that this action is imposible in sql server (I research a lot), because it hasn't something like select for update...
Now I want to know if it is posible to lock one row meanwhile I am updating it. I see that a transaction that updates one row affects most of the records because SQL Server takes a PAGE lock.
I try varios ways to disable lock escalation in this particulary small table, but with no results. (LOCK_ESCALATION = DISABLE, ISOLATION LEVEL 3,4, Hints.)
I understand what you say GilaMonster, but I want only disable lock escalation in this table.
Am I explain myself right?
sorry if you dont understand my point, I'll try my best.
Thanks a lot!