Re: How to switch off automatic blanking of table views in enterprise manager ?


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

.

Relevant Pages

  • Re: Sql Server Locks
    ... Tran and Commit Tran are as close together as possible and the transactions ... > The problem is with SQL Server Database Locks. ... > client A and reinserts the same with some data changes ... > provide us any good links for this kind of scenarios. ...
    (microsoft.public.sqlserver.clients)
  • Re: Synchronization between 2 PHP threads?
    ... >> means I have to consider multiple threads of the same PHP script running ... > fork a PHP - but that creates a new process, ... Table A is a client table. ... why use discretionary locks over table locks? ...
    (comp.lang.php)
  • Re: Locking
    ... Returns the type of locks participating in the deadlock and the current ... This forces the second connection to either wait or block on the first. ... Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems ... locks into fewer coarse-grain locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Synchronization in .Net Remoting
    ... the related server side object code has been posted. ... > is the related client side code: ... > the locks before doing processing and then release all the locks. ... >>> different locks on different remote objects before performing any real ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Overlapped file locking doesnt work on NT4?
    ... One locks the file then unlocks it, ... each client has to close the file after ... seems to lock and unlock internally? ... Speaking of "contact MSFT", NT4 support has been officially discontinued, ...
    (microsoft.public.win32.programmer.kernel)
Loading