Re: Blocking issue

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 07:49:32 -0500

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 :)

Relevant Pages

  • Irregular read-only errors
    ... not allowing me to open a recordset with the correct cursor and lock ... I'm about ready to switch to using SQL Server (which I know I probably ... be an easy switch so if I could sort out Access for the time being ... Would setting the ADO connection mode to adModeReadWrite accomplish ...
    (microsoft.public.inetserver.asp.db)
  • Re: Locking question when using Select clause with For Update and Skip locked
    ... This is working fine in SQL Server and multiple session can get the ... But in Oracle the first session only return 1 row but locks all the ... It looks like both the session got the ROW-X lock but one session is ...
    (comp.databases.oracle.server)
  • Re: OLE DB Cursors
    ... You don't do this with properties in the SQL Server OLE DB provider. ... client-side cursor, otherwise you have a server-side cursor managed from the ... I have to do a lot of updates() here, ... > its optimistic lock / client cursor. ...
    (microsoft.public.data.oledb)
  • Re: Hung up using a SELECT MAX when an alter database has been execute in a SQL Server database
    ... exclusive lock, as it does for the ALTER TABLE statement. ... see "Isolation Levels" in SQL Server Books Online. ... Public Sub StartConnection() ... Set objRstSel = New ADODB.Recordset ...
    (microsoft.public.data.oledb)
  • Re: How does CREATE INDEX impact current users?
    ... take an X lock on the table, thus preventing any access to the table at all. ... This problem is alleviated in SQL Server 2005 with the concept of online ... Creating a clustered index does not copy the table per se - the heap ...
    (microsoft.public.sqlserver.server)