Re: Locking problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Browne (meat_at_hotmail.com)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 15:09:00 -0600

"Francisco D. Kurpiel" <francisco.kurpiel@westbest.com.br> wrote in message
news:e7arqt8$DHA.2432@TK2MSFTNGP11.phx.gbl...
> Woooooooow!
>
> Change the primary key to be nonclustered makes the SQL Server to create a
> RID locking with the locking mode that I have requested when I ran the
first
> script. It locked also the page and the table with an ‘intent’ lock. This
> lock blocks the ‘select * from bla’ command, that tries to get a shared
lock
> on the table.
>
> Your ‘dummy update’ trick also helps. But is so... ’SQLServer7–like’… I
will
> try to avoid this solution.
>
> I have to analyze the impact of this change on my database. The table on
the
> script is just for demonstration purpose. I have some tables and I need to
> project data access paths to avoid dead locks and I’m delayed...
>
> I’m very disappointed with this… at least strange behavior. I don’t
> understand why Microsoft chooses to lock just the index that is being used
> to access the data. This behavior is not deterministic, because the index
> selection depends on the volume of data on the tables. Microsoft doesn’t
> report this as a bug? They suppose to do. This may be dangerous.
>

The reason for all of this is that without snapshot concurrency, it's easy
to become overly syncronized, and loose concurency. There is a very strong
policy in favor of concurrency, and so you might not get all the locks you
expect.

> But I don’t understand why you say “select nome from bla will not be
> blocked”. The first script will create an SIX or an IX lock on the table,
> and the “select * from bla” will create an S lock on the table, these are
> incompatible.

select * from bla takes an IS lock on bla and an S lock on un_bla_nome
because un_bla_nome is, suprisingly, a covering index for the query. It's
only a covering index because the only columns in bla, but not in
un_bla_nome, are the clustered index columns (ie id). The clustered index
columns are included in the index because that's the only way to locate a
row in a table with a clusered index.

And IX and IS are compatable. Here's the lock compatability matrix from
BOL.

                                   Existing granted mode
Requested mode IS S U IX SIX X
--------------------------------------------------------
Intent shared (IS) Yes Yes Yes Yes Yes No
Shared (S) Yes Yes Yes No No No
Update (U) Yes Yes No No No No
Intent exclusive (IX) Yes No No Yes No No
Shared with intent exclusive (SIX) Yes No No No No No
Exclusive (X) No No No No No No

IX locks are compatable with other IX locks.

> Do you have a link to some documentation about this subject? My current
> project probably will become big and I don’t want more surprises.
>

Inside Microsoft® SQL Server™ 2000 by Kalen Delaney
http://www.microsoft.com/MSPress/books/4297.asp

>
> I appreciate your help and the enfort on make it clear. Any additional
> reference will be welcome.
>
> PS: Where did you get this sp_lock2? The one that I have doesn’t show the
> index name. It helps a lot.

http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP

David

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)