Microsoft KB Archive/189946

= BUG: Cursor on Query with Aggregate Blocks with READ UNCOMMITTED =

Article ID: 189946

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q189946



BUG #: 18074 (SQLBUG_65)



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 ()

So for this case, the syntax would be:

use pubs go  select object_name (1113055001) go

Additional query words: serverside performance slow poor count(*) sum exec sp_cursoropen resultset resultsets recordset recordsets roundtrip side roundtrips

Keywords: kbbug kbpending KB189946

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.