SQL Server Locks/ProcessID - Is this a problem?

[Guest]

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.

 

[Mary Chipman [MSFT]]

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.

 

[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

 

[Guest]

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.

Thanks for the information.

Do you know why these shared locks remain listed in Enterprise Manager?
After the app is closed, shouldn't these locks be released?

Or does SQL Server just keep them around since they're shared locks and not
exclusive locks?

Thanks.