Number of Locks and MSA

[Former Member]

I am asking a question here in which I think I know the answer but I will reach out to the community for clarification anyways.

I recently added an MSA  Server to a warm standby replication system and I have had two separate systems run out of locks on the PRIMARY database server. Both systems have not had new code applied nor have it had new jobs in place. The only thing I would say that is new about the environment is that we had added an MSA

I would say that I expect the amount of required locks to be more on the target systems but on the primary I don't expect that we need to increase the locks due to replication.

Have anyone had a similar experience before? Is it possible that replication (MSA in this case) would require additional locks on the primary. Just so you know we increased locks from 500000 to 1000000. Target systems were always 1000000, including MSA target

Thanks

[former_member89972]

Hi Courtney

Two possibilities come to my mind

1.

A one off query where a user did insert or select into a table or temp table with a select from a table with datarows locking scheme.  If the table from where data is selected is very active then the number of rows locked will climb steeply in a matter of seconds. I have seen this repeatedly and advised users to reduce the selection batch size. 

2.

Somehow an atomic materialization got triggered with scenario similar to above.

This is just a guess because the symptoms are similar to 1 above.

As Mark pointed out you should have a monitoring job polling for extremely high number of locks to  nail down one or more SPIDs.   Then using your homegrown tools or third party tools you can zero down on the actual SQL used. 

In my environment our job monitors if a single SPID grabs 1M+ locks. It logs the SPID details and then terminates it  We have our server limit configured for 2M locks. This has saved us from potential server crashes and unplanned shutdowns 🙂

HTH

Cheers

Avinash