SQL-28 / deadlock

[sachin]

Gurus,

I need your opinion/suggestion on an issue that we are currently
facing in my company. The scenario is, we have a need for live
data so a process called Replication runs on the database every
20 minutes updating all the tables with new data. Since its
always live or so-called "Online Reporting" we see numerous
deadlocking issue due to which many reports fail with datasource
Isolation Level set as Read Uncommitted. The reason is when we
looked at the DB trace, READ UNCOMMITTED was embedded with our
main SQL SPID but not with its child SPIDs. So these child SPIDs
gets deadlocked with Replication and the report errors out. When
spoken with Cognos they said its a DB issue and nothing can be
done on Cognos side to avoid it which does make sense. So my
question is, have any of you worked on online reporting
environment or do you have an idea of any work around for these
deadlocks.

Only one suggestion that I have is to remove Parralelism from our
DB SQL Server 2000 which will send all my queries serially and i
believe with READ UNCOMMITTED embedded getting us away from
deadlocks. Since this cannot be implemented in our environment
due to other reasons I'm looking for viable options.

Please fill me with your thoughts.

Thanks
Sachin

[Skidly]

We had some locking issues with ReportNet also and set our data
connections to read uncommitted.  But we still saw locking on the DB SQL
Server 2000.

Cognos support said the ReportNet Read Uncommitted option for the
Connections didn't work and we had to do the following which resolved
our locking errors.

1.  Stop the Cognos ReportNet service.
2.  Make a copy of the CQEConfig.xml.sample file located in x:\Program
   Files\cognos\crn\configuration
3.  Rename the original from CQEConfig.xml.sample to CQEConfig.xml
4.  Modify CQEConfig.xml to include the following:

Under <section name="QueryEngine">, you will need to add the following
line:

<entry name="UsePassiveTransactions" value="0"/>

For example:

<section name="QueryEngine">
       <!-- Description:       Controls the queryReuse feature. The
entry must be uncommented. -->
       <!-- A value of 0 will disable queryReuse. -->
       <!-- The default value of 5 means that 5 result sets are reused.
The maximum is set to 10. -->
       <!-- entry name="queryReuse" value="0" /-->
       <!-- -->
       <entry name="UsePassiveTransactions" value="0"/>
</section>

5. Change the Isolation Level on the Data Source connection to specify
"Read Uncommitted" with Cognos Connection.
6. Start the Cognos ReportNet service and test.

Hope this helps.
Chad