My senior DBA told me that SQL Query execution by default doesn't lock the table.

I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues with locking and getting some errors.

I did some Googling but fell short of finding anything.

Do SSRS reports lock the tables that are being queried?

Is there any MSDN documentation that document this behavior specifically?

  • We had the same problem. Though it's technically not an answer to your questions, we did a quick fix by starting dataset queries with SET TRANSACTION ISOLATION LEVEL for example to READ UNCOMMITTED if you don't mind risking a few dirty reads.
    – Jeroen
    Sep 11, 2012 at 10:00
  • Just as a note for the future, you can redirect reporting load to readable secondaries with an AlwaysOn Availability Group in SQL 2012. msdn.microsoft.com/en-us/library/hh882437.aspx
    – wBob
    Sep 12, 2012 at 21:44

3 Answers 3


Short answer: No


SQL Server doesn't know that it is SSRS sending it a query. So the query from SSRS will run like any other query.

It's more likely be that the query optimiser decides to use a table lock for the SSRS query. of course, it could be a different problem, but that's a different question

  • mm so then does SRSS tries to lock the table when it does query then?
    – melaos
    Sep 11, 2012 at 15:05
  • The T-SQL statement results in some type of lock or another, depending on the T-SQL statement. It is not dependent on where that statement originates (SSRS, query window, app, etc) Sep 11, 2012 at 16:16

my senior told me that for SQL Query execution by default doesn't lock the table.

This is true. However, it doesn't mean a query can't lock a table.

does SSRS report actually will lock any tables that is being queried?

SSRS gets the data used to render the report by running a query or stored procedure against the database.

This query is defined by the developer, and it may end up locking a table (or tables), depending on the isolation level and how many rows are involved. (In fact, there may be cases where you'd want to do this on purpose.) The bottom line is that it's up to the developer how the locking works for the query. SSRS can't solve this problem for you. That's why there isn't any documentation.

Consider (for example):

  • Using READ UNCOMMITTED if dirty reads are okay
  • Enabling and using a snapshot isolation level
  • Log shipping in Standby mode, and running queries against the read-only copy

How do you know that there is any lock when the report is running? I would suggest you to check the query/stored proc that is the source of the report and ensure it is working fine by itself.

If you are sure the source query is working fine, try to pinpoint the issue using SQL server profiler. The link below might help:

  • because i was running the report on a few browser and the report keep getting errors that there's process locked with dead locked.
    – melaos
    Sep 11, 2012 at 15:05
  • In that case, most likely your report is trying to read a data that is locked by another user (e.g. someone is updating the same record). You can add "with no lock" to all "Select" statements, if reading uncommitted data is ok.
    – Sky
    Sep 11, 2012 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.