Re: Data/index locking in SQL server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 04/13/04 

Date: Mon, 12 Apr 2004 21:03:43 -0500

Can you post the actual DDL including the indexes for that table? Is that
index on the key column clustered or non and if non is there a clustered at
all? 

--
Andrew J. Kelly  SQL MVP
"Alex Drobyshev" <ad31415@yahoo.com> wrote in message
news:52a3e1ca.0404091252.42392b7e@posting.google.com...
> By "lock on the table" I actually meant a lock on a specific row in the
> table, not a lock on the whole table. Similarly, "lock on the index"
> means a lock on a specific key.
>
> Having said that, here are lock acquisition sequences I observed:
>
> SELECT:
> - S (Key)
> - S (Row)
>
> UPDATE:
> - X (Row)
> - X (Key)
>
> (I have omitted "U" locks, for they don't really change the behaviour
> here).
>
> When SELECT and UPDATE both operate on the same row (and key), a
> dedalock occurs. :-(

Relevant Pages

  • Deadlock in single session
    ... I'm running a script that applies the delta DDL that is required to get ... existing sequences, and renaming two other sequences (which are created ... deadlock detected while trying to lock object CHS.HARNLOC1 ... lock user session count mode flags ...
    (comp.databases.oracle.misc)
  • RE: [Info-ingres] Friday is coming ...
    ... None of these are arguments for making DDL non-transactional. ... the bit about locking entire catalogs and databases. ... aren't locked unless lock escalation takes place. ...
    (comp.databases.ingres)
  • RE: [Info-ingres] Friday is coming ...
    ... Here's some examples of dubious DDL rollback with procedures: ... aren't locked unless lock escalation takes place. ... DDL that locks an entire database. ...
    (comp.databases.ingres)