Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite

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?

share|improve this question
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 '12 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 '12 at 21:44

3 Answers

up vote 7 down vote accepted

Short answer: No

Longer...

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

share|improve this answer
mm so then does SRSS tries to lock the table when it does query then? – melaos Sep 11 '12 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) – Eric Higgins Sep 11 '12 at 16:16
up vote 3 down vote

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
share|improve this answer
up vote 2 down vote

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:
http://stackoverflow.com/questions/9107383/sql-server-profiler-capture-calls-to-your-databases-stored-procs-during-ssrs

share|improve this answer
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 '12 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. – Nazila Beikpour Sep 11 '12 at 21:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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