We are using read committed snapshot isolation level so I thought reads couldn't cause locking. We seem to be getting a fair amount of it and the software I use to monitor it (Ignite) is reporting select statements (such as the one below) as the cause...
(@p0 datetime,@p1 datetime,@p2 datetime,@p3 datetime,@p4 datetime,@p5 datetime,@p6 int,@p7 int,@p8
int,@p9 int,@p10 int)
SELECT COUNT(*) AS [value]
FROM
(
SELECT
(
SELECT COUNT(*)
FROM [dbo].[Subscription] AS [t2]
LEFT OUTER JOIN [dbo].[Students] AS [t3]
ON [t3].[StudentsID] = [t2].[StudentsID]
LEFT OUTER JOIN [dbo].[Departments] AS [t4]
ON [t4].[DepartmentID] = [t3].[DepartmentID]
WHERE ([t2].[StartDate] >= @p0)
AND ([t2].[StartDate] < @p1)
AND ([t2].[CourseID] = [t0].[CourseID])
) AS [value],
(
SELECT COUNT(*)
FROM [dbo].[Subscription] AS [t5]
LEFT OUTER JOIN [dbo].[Students] AS [t6]
ON [t6].[StudentsID] = [t5].[StudentsID]
LEFT OUTER JOIN [dbo].[Departments] AS [t7]
ON [t7].[DepartmentID] = [t6].[DepartmentID]
WHERE ((( SELECT MIN([t9].[ActivityStarted]) FROM [dbo].[Students] AS [t8], [dbo].[Activity]
AS [t9] WHERE ([t9].[StudentsID] = [t5].[StudentsID]) AND ([t9].[CourseID] = [t5].
[CourseID]) AND ([t8].[StudentsID] = [t5].[StudentsID]) AND ([t9].[StudentsID] = [t8].
[StudentsID]) )) >= @p2)
AND ((( SELECT MIN([t11].[ActivityStarted]) FROM [dbo].[Students] AS [t10], [dbo].[Activity]
AS [t11] WHERE ([t11].[StudentsID] = [t5].[StudentsID]) AND ([t11].[CourseID] = [t5].
[CourseID]) AND ([t10].[StudentsID] = [t5].[StudentsID]) AND ([t11].[StudentsID] = [t10].
[StudentsID]) )) < @p3)
AND ([t5].[CourseID] = [t0].[CourseID])
) AS [value2],
(
SELECT COUNT(*)
FROM [dbo].[Subscription] AS [t12]
LEFT OUTER JOIN [dbo].[Students] AS [t13]
ON [t13].[StudentsID] = [t12].[StudentsID]
LEFT OUTER JOIN [dbo].[Departments] AS [t14]
ON [t14].[DepartmentID] = [t13].[DepartmentID]
WHERE ([t12].[Completed] >= @p4)
AND ([t12].[Completed] < @p5)
AND ([t12].[Status] = @p6)
AND ([t12].[CourseID] = [t0].[CourseID])
) AS [value3], [t0]
.[ClientID]
FROM [dbo].[Courses] AS [t0]
LEFT OUTER JOIN [dbo].[CourseCategories] AS [t1]
ON [t1].[CourseCatID] = [t0].[CourseCatID]
) AS [t15]
WHERE (([t15].[value] > @p7)
OR ([t15].[value3] > @p8)
OR ([t15].[value2] > @p9))
AND ([t15].[ClientID] = @p10)
Select statements causing locking???
Posted - 05/09/2012 : 12:11:28
