I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
