Depending on which database and table we are locking on there several possible causes, and I want to list out these possible reasons for you.
- If we don’t schedule the crawls properly, they could interfere with each other and cause locks on our search databases.
- If we have large lists or libraries then a user who enumerates it could cause us to retrieve more than 5000 items which would cause a lock on the content database
- If the any database or it’s logs need to grow, then during that time the database would lock
- Poor performance in SQL server could cause any database to lock
- Memory contention
- CPU starvation
- Poor disk performance
- MAXDOP not being set properly could cause locks on any database (Performance issue)
- Overlapping process accessing a single database ie Backup, Crawl, Users, Antivirus, or any other 3rd party app.
- Admins accessing database with T-SQL query that doesn’t use ‘No lock’
- A single workflow could cause a lock on the database
- Deep Site/List Hierarchy could cause a lock on the content database
- Certain web parts could cause locks on the content database if used in certain ways.
I have not listed all the outliers that could cause a lock on a database but they would be a variation of what I have listed here at the root of it.