Application got "locked out"  
Author Message
Anyme





PostPosted: Thu Jul 08 11:08:44 CDT 2004 Top

SQL Server Developer >> Application got "locked out"

Hello.

One of our developers executed an ad-hoc INSERT INTO ... WHERE ... statement
that inserted about 49,000 records.

While this ran, an application that utilizes the table that was being
Inserted Into timed out while trying to access the table. I assume that SQL
Server somehow locked this table.

What is the technical description of what may have happened? We're running
SQL Server 2000 sp3a.

Thanks in advance,

Mike

SQL Server99  
 
 
Adam





PostPosted: Thu Jul 08 11:08:44 CDT 2004 Top

SQL Server Developer >> Application got "locked out" The technical term for this is "blocking". Your INSERT created locks (or
probably a table-level lock) which meant that the app was unable to read the
data. So it waited. And eventually timed out. This is expected behavior.

There are several ways of dealing with this, depending on what your app
requires...

A) When doing INSERTs, you can split them into smaller batches to minimize
table locks and let other parts of the app read data; this is problematic if
the INSERTs need to be transactional.

B) You can set your reading sessions to use lower isolation levels, or
utilize table hints, to read the data. You'll want to investigate either
NOLOCK or READPAST. The first will read everything from the table,
regardless of whether it's locked. The second will read only data that's
not locked, but won't wait for locked data, and so won't time out. Both of
these have their downsides. The first could give you out of date data, and
the second could mean that you'll miss data.

C) Set a smaller timeout and catch it in your app code and deal with it
appropriately; issue an error, try the query again, etc...






> Hello.
>
> One of our developers executed an ad-hoc INSERT INTO ... WHERE ...
statement
> that inserted about 49,000 records.
>
> While this ran, an application that utilizes the table that was being
> Inserted Into timed out while trying to access the table. I assume that
SQL
> Server somehow locked this table.
>
> What is the technical description of what may have happened? We're running
> SQL Server 2000 sp3a.
>
> Thanks in advance,
>
> Mike
>
>