CODEWITHSUNDEEP
sql-serversharepoint
Martin Muldoon
Martin Muldoon

Reputation: 3428

Does SQL lock tables when queries return results greater than 5000 records?

I'm am working in SharePoint and am trying to understand why there is a limit of 5000 records for views on lists that can have a far greater number.

My question is not about SharePoint, but SQL. Does SQL have the limit described below, or was this something imposed on SQL by the SharePoint team?

For performance reasons, whenever SQL Server executes a single query that returns 5000 + items, locks escalation happens within the SQL table. As a result, the entire table will be locked. Since the entire Share Point data is stored as a single table, a single list view query that exceeds 5000+ items will lock the entire Share Point data table within that content. The database and all the users will face huge performance degradation. The entire set of users using Share Point at the time of lock escalation will have to wait for a longer time to retrieve the data. Hence, you can see that the list threshold is a limitation that is imposed upon Share Point by its backend SQL Server. This issue is generated from SQL and the reason is row lock escalation. In order to avoid this performance degradation, Share Point has imposed the limitation of 5000 items to be queried at any point of time. Any queries for 5000+ items will be dealt the threshold error message. Ref link

Thanks

EDIT____________________________________

An article on this issue: https://www.c-sharpcorner.com/article/sharepoint-list-threshold-issue-the-traditional-problem/

Upvotes: 1

Views: 2423

Answers (1)

Martin Smith
Martin Smith

Reputation: 453328

Does SQL Server lock tables when queries return results greater than 5000 records?

Not generally, no.

It is documented that 5,000 is a magic number for the database engine to first attempt lock escalation (followed by further attempts at 1,250 increments) but unless running at repeatable read or serializable isolation level this will not generally be hit just by returning 5,0000 items in a SELECT. The default read committed level will release locks as soon as the data is read so never hit the threshold.

You can see the effect of isolation level on this with the following example.

CREATE TABLE T(C INT PRIMARY KEY);

INSERT INTO T 
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2

And (uses undocumented trace flags so should only be used in dev environment)

DBCC TRACEON(3604,611);

/*5,000 key locks are held*/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT COUNT(*) FROM (SELECT TOP 5000  C FROM T) T
SELECT resource_type, request_mode, count(*) FROM sys.dm_tran_locks where request_session_id = @@spid GROUP BY resource_type, request_mode;
COMMIT


/*No key locks are held. They have been escalated to an object level lock. The messages tab shows the lock escalation (in my case after 6248 locks not 5,000)*/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT COUNT(*) FROM (SELECT TOP 10000  C FROM T) T
SELECT resource_type, request_mode, count(*) FROM sys.dm_tran_locks where request_session_id = @@spid GROUP BY resource_type, request_mode;
COMMIT


/*No key locks are held. They are released straight away at this isolation level. The messages tab shows no lock escalation messages*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT COUNT(*) FROM (SELECT TOP 10000  C FROM T) T
SELECT * FROM sys.dm_tran_locks where request_session_id = @@spid 
COMMIT


DBCC TRACEOFF(3604,611);

Upvotes: 6

Related Questions