SQL Server Locks/ProcessID - Is this a problem?

5 Apr 2006 9:24 PM

Spam Catcher

Hi all,

I have a simple application which polls a DB every couple of seconds to
retrieve some status information. The app also updates the database from
time to time. I would say 85% polling, 15% updates.

In SQL Server's Management Screen (Locks/ProcessID) I see ~3 SPIDs related
to my application. These seem to be granted locks.

Is this an issue?

A client of our believes that the app should have no locks whatsoever.

I'm just doing standard SELECT * FROM Users ORDER BY UserName via SQLHelper
(DABB). The table will have at most ~100 records (typically 10 - 15). I do
need to grab all the records at once.

Any suggestions, comments, or ideas?

Thanks.

7 Apr 2006 3:38 PM

Mary Chipman [MSFT]

On Wed, 05 Apr 2006 21:24:37 GMT, Spam Catcher
<spamhoneypot@rogers.com> wrote:

Show quoteHide quote

>Hi all,
>
>I have a simple application which polls a DB every couple of seconds to
>retrieve some status information. The app also updates the database from
>time to time. I would say 85% polling, 15% updates.
>
>In SQL Server's Management Screen (Locks/ProcessID) I see ~3 SPIDs related
>to my application. These seem to be granted locks.
>
>Is this an issue?
>
>A client of our believes that the app should have no locks whatsoever.
>
>I'm just doing standard SELECT * FROM Users ORDER BY UserName via SQLHelper
>(DABB). The table will have at most ~100 records (typically 10 - 15). I do
>need to grab all the records at once.
>
>Any suggestions, comments, or ideas?
>
>Thanks.

7 Apr 2006 3:45 PM

Mary Chipman [MSFT]

SQL Server issues shared locks if you are running under the default
isolation level of READ COMMITTED. These locks are granted-released in
sub-second time, and should not cause a problem with such a small
amount of data. If you want to avoid even those locks, run your
polling under the READ UNCOMMITTED isolation level, which does not
place any locks on the data and ignores the locks placed by other
processes. However, if you do that, you will get dirty reads, so your
polling app would react to data that never actually gets committed in
the database, which totally defeats its purpose.

--Mary

On Wed, 05 Apr 2006 21:24:37 GMT, Spam Catcher
<spamhoneypot@rogers.com> wrote:

Show quoteHide quote

>Hi all,
>
>I have a simple application which polls a DB every couple of seconds to
>retrieve some status information. The app also updates the database from
>time to time. I would say 85% polling, 15% updates.
>
>In SQL Server's Management Screen (Locks/ProcessID) I see ~3 SPIDs related
>to my application. These seem to be granted locks.
>
>Is this an issue?
>
>A client of our believes that the app should have no locks whatsoever.
>
>I'm just doing standard SELECT * FROM Users ORDER BY UserName via SQLHelper
>(DABB). The table will have at most ~100 records (typically 10 - 15). I do
>need to grab all the records at once.
>
>Any suggestions, comments, or ideas?
>
>Thanks.