of our databases. In the
research that I have done I think it is being caused by
users querying the tables
with Enterprise Manager's Query Tool. I am seeing table
level locks that are granted
against the table in Mode IS. I am interpreting this to
mean that there is an 'Shared
Intent' Lock on that table. Which to my understanding
means that anyone can continue
to read but that I have also declared an intent to update
the table and therefore
requests for exclusive locks for update, insert or delete
operations would block
until my intent lock is removed. If the block occurs for
too long the blocked call
would then timeout. I believe that Enterprise Manager is
creating this type of lock
because the user interface allows the data that is
displayed to be updated and if the
Intent lock was not declared then data syncronization
issues would occur.
For now I have instructed people who access the tables to
use Query Analyzer since it
will only lock when queries that require the lock are
executed and will not hold the
lock while the user browses the results.
My questions are as follows:
1) Is my interpretation of the IS Mode Table lock correct?
2) Is there a way to prevent Enterprise Manager from
locking the table in this way
(i.e. make the query results read-only)?
3) If the user that is performing the query only has
dbreader privledges then why is
the Intent lock being created?
Thanxshttp://vyaskn.tripod.com/sql_enterp...er_or_t-sql.htm
"
" <anonymous@.discussions.microsoft.com> wrote in messagenews:535b01c4003c$1ec4aca0$a301280a@.phx.gbl...
> We have noticed some random failures to write data to some
> of our databases. In the
> research that I have done I think it is being caused by
> users querying the tables
> with Enterprise Manager's Query Tool. I am seeing table
> level locks that are granted
> against the table in Mode IS. I am interpreting this to
> mean that there is an 'Shared
> Intent' Lock on that table. Which to my understanding
> means that anyone can continue
> to read but that I have also declared an intent to update
> the table and therefore
> requests for exclusive locks for update, insert or delete
> operations would block
> until my intent lock is removed. If the block occurs for
> too long the blocked call
> would then timeout. I believe that Enterprise Manager is
> creating this type of lock
> because the user interface allows the data that is
> displayed to be updated and if the
> Intent lock was not declared then data syncronization
> issues would occur.
> For now I have instructed people who access the tables to
> use Query Analyzer since it
> will only lock when queries that require the lock are
> executed and will not hold the
> lock while the user browses the results.
> My questions are as follows:
> 1) Is my interpretation of the IS Mode Table lock correct?
> 2) Is there a way to prevent Enterprise Manager from
> locking the table in this way
> (i.e. make the query results read-only)?
> 3) If the user that is performing the query only has
> dbreader privledges then why is
> the Intent lock being created?
> Thanxs
