ALTER TABLE SET (LOCK_ESCALATION = DISABLE) vs exceptions

Hi all,

At this URL (https://msdn.microsoft.com/en-us/library/ms190273.aspx) explaining the various available ALTER TABLE arguments, there is one that particularly concern us.

This is this one:

<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

SET( LOCK_ESCALATION = { AUTO | TABLE | DISABLE })

Applies to: SQL Server 2008 through SQL Server 2016, SQL   Database V12.

Specifies the allowed methods of lock escalation for a table.

...

DISABLE

Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

-------------------------------

More precisely we wanted to know exactly in which cases a table that have been set to LOCK_ESACALATION = DISABLE can still do lock escalation apart from the example given that URL.

Can someone give us a complete list?

Best regards.

Carl 

August 24th, 2015 6:15pm

I am not sure that there is a complete list. What the note is trying to convey is that the storage engine may take a table lock up front, in which case it does not count as lock escalation.

Also beware that there are certain operations that take out a schema-modification lock, which is even stronger. Examples of the latter are DBCC CHECKIDENT, partition switching and (I think) TRUNCATE TABLE.

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 3:31am

Hi Erland,

Thank's for your input. It is really appreciated.

Our goal is to get rid of these lock escalations due to the modification or insertion of several records in one transaction (it is sometimes in our business needs and in these cases we don't want the Engine to serialise access to one table for every users, that is not scalable at all).

We will increase the max server memory to compensate for this.

Best regards.

Carl

August 25th, 2015 1:30pm

Hi Erland,

Thank's for your input. It is really appreciated.

Our goal is to get rid of these lock escalations due to the modification or insertion of several records in one transaction (it is sometimes in our business needs and in these cases we don't want the Engine to serialise access to one table for every users, that is not scalable at

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 12:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics