set transaction isolation level read uncommitted not working 1

[sue1127]

I have several Crystal reports that need to be put on the web. In testing, when 14 people tried to access the reports, the locks forced a number of people to hang. It was suggested that the report be changed to call a stored procedure, and that the 'read uncommitted' command be issued to prevent locks. This seemed reasonable, since they are just reports, and do not update any tables. However, the dba said that the locks still remained when this was done.

Does anyone know why the command would not have worked, or know of any other approach that might solve this problem?

Thanks,

Sue

 

[nigelrivett]

By hang do you mean they were being blocked?
If that's the case then it's not taking shared locks - are you updating something?

Otherwise the problem isn't the locks but probably creation of work tables for the query.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sue1127]

I'm not sure what you mean by being 'blocked'. I was thinking that they were not able to access the report because SIX locks were being set. These locks were not released until the processing completed and the data appeared. Because a number of people were trying to access at the same time, the report took longer and longer to process, and consequently the locks took longer and longer to be released, which, I thought, increased the time still more. Nothing is being updated, so I thought that preventing the SIX locks from being set might solve the problem.

If the problem is creation of work tables for the query, is there a way to resolve that?

Thanks,

Sue

 

[nigelrivett]

Why is a SIX lock being taken?
A select shouldn't do that (I don't think).
What is the object that the lock is on?

To resolve it you will have to recode so that only shared locks are taken.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ArtieChoke]

Selects do seem to cause locks - it happens to us from another report writer - and causes the same problems. Maybe the server "thinks" it's more efficient or the way the query is structured from the report writer - it sets up cursors. Most of the report writers connect via ODBC or OLEDB - which I'm sure doesn't work like query analyzer would - doh!

Try using nolock in the sp for each table:

select * from
a (nolock)
b (nolock)
...

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[sue1127]

Thanks Artie, I will try the nolock. But, according to the dba, the locks were set even when I ran the stored procedure through query analyzer, bypassing Crystal entirely. Do you think something could have been set somewhere to tell MS Sql Server to always set those locks?

Thanks,

Sue