Determine which tables are locked

开发者_开发问答I have to following problem. I have an application where users can login and do some things like adding new item. I also have statistics in Reporting Services. The problem is that statistic is time consuming and when it is executed, users cannot make new items. In my sql query for statistic I have all select statements decorated by WITH nolock statement. However I can see some tables are locked using Activity Monitor. Is it correct that I see them in locked by objects tab? How can I figure out which tables are locked? When I use the following statement:

SELECT * FROM MyTable WITH (nolock)

I can also see this query locks MyTable table. Please help me.

Don't use NOLOCK. Dirty reads are inconsistent reads.

Use instead SNAPSHOT ISOLATION. Then you get the best of both worlds: consistent reads and no locks. Remove all lock hints from your queries, then enable read committed snapshot:

ALTER DATABASE [<dbname>] SET READ_COMMITTED_SNAPSHOT ON