ADO.Net locking rows on read

[Guest]

I have a VB .Net COM+ component running a stored procedure on a SQL Server 2000 DB. If the stored procedure is executed within Query Analyser it takes in the order of three seconds to complete, however when accessed from my component it hits a 5 minute timeout when retrieving the rows (in the order of 30,000 rows). Further investigation points the finger at ADO .NET locking/unlocking each row of data its returning (which Query Analyser isn’t doing). An example profiler from the database follows…

Lock:Acquired .Net SqlClient Data Provider user1 dom\user1 0 0 57 2004-07-05 13:29:03.380

Lock:Acquired .Net SqlClient Data Provider user1 dom\user1 0 0 57 2004-07-05 13:29:03.380

Is there some way of turning off this locking – it only needs to be a read-only query.

Thanks

Gavin

 

[Wallace B. McClure]

Without seeing your code, it is hard to answer the question. Here are some
basics that I do know. By default, COM+ will create a distributed
transaction. The isolation level of that transaction is set to
serializable. This is by default. if you are running Win2000 Server / cOM+
1.0, you will need to either turn off distributed transactions, or specify
the locking type within the query which should override the default
isolation level. If you are using Win2k3 / WinXp / COM+ 1.5, you should
have an additional option of changing the isolation level at the component
level (if my memory is correct).

Wally