Bobbo wrote:
Mark D Powell wrote:
EM locks data when it reads it.
I don't think that's the case. I've just created a test table, filled
it with some data and viewed it in EM. I could still update one of the
viewed rows using QA. Once I re-ran the EM query using the exclamation
icon again, the changed row reflected its new state.
Surely the result set clearing is because of the resources consumed by
the open connection, rather than any locking? If I'm wrong, somebody
stop me - to quote Jim Carey.
You should really use Query Analyzer
I agree! It's a much better tool for actually executing queries.
We have traced data access problems to users accessing data via EM. MS
may have modified some of the logic with SP1, SP2, SP3, and/or SP4 so
that EM works more effectively.
>>
From the SQL Server Documentation: Optimizing Query Performance -
SQL Server is essentially a puppet of the client application. The
client application has almost total control over (and responsibility
for) the locks acquired on the server. Although the SQL Server lock
manager automatically uses locks to protect transactions, this is
directly instigated by the query type sent from the client application
and the way the results are processed. Therefore, resolution of most
blocking problems involves inspecting the client application.
A blocking problem frequently requires both the inspection of the exact
SQL statements submitted by the application and the exact behavior of
the application regarding connection management, processing of all
result rows, and so on. If the development tool does not allow explicit
control over connection management, query time-out, processing of
results, and so on, blocking problems may not be resolvable.
Possible cause of problems listed in the above article (these are
things to avoid)
Submitting queries with long execution times
Canceling queries that were not committed or rolled back. [The
application needs to issue a rollback or commit after a query is
cancelled in order to release held locks!]
Applications that are not processing all results to completion
Distributed client/server deadlocks
Locks are held for the length of time needed to protect the resource at
the level requested:
<<
So it depends on how the clients works if one user can cause more
contention than is reasonable. Cursor and especially select for update
cursors can result in unexpected locks waiting. Like I said we tested
and identified a problem, but it was a couple of SP ago.
-- Mark D Powell --
