BUG: Cursor on Query with Aggregate Blocks with READ UNCOMMITTED (189946)

SYMPTOMS

Opening a cursor on a query with an aggregate function will block other users as it acquires a shared lock on the underlying table(s) for the duration of the time required to open the cursor. This lock is acquired even with TRANSACTION ISOLATION LEVEL set to READ UNCOMMITTED. The same query executed outside of the cursor will not acquire the shared lock with the same transaction isolation level. This happens with all cursor types, whether the user explicitly declares and opens the cursor, or if an application uses server-side cursors when opening result sets or record sets.

WORKAROUND

To work around this problem, use either of the following methods:

Use the PAGLOCK optimizer hint within the query.

-or-
If an application is opening a result set or record set for the query, a server-side cursor may be used behind the scenes. If this is the case, change the application to use a client-side cursor (or firehose cursor, default record set) for queries using aggregates. Another option is to not open a cursor, but instead execute the query directly, using ODBCDirect or a similar method. Either option prevents the use of a server-side cursor for the query. This will prevent the shared lock from being acquired, and will have the additional benefit of reducing round trip network traffic to the server, as compared to using a server-side cursor.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The shared locks can be viewed by executing the sp_lock stored procedure from a separate connection while the cursor is being opened. The following is sample output:

   spid   locktype                       table_id      page         dbname
   -----------------------------------------------------------------------
   11     Sh_table                       1113055001     0            pubs

Table_id is the object ID of the table the cursor with the aggregate function is being opened on. To find the object name, run the following query in the database listed in the dbname column:

   select object_name (<table_id>)

So for this case, the syntax would be:

   use pubs
   go
   select object_name (1113055001)
   go