Tags: 172-32-1176, au_id, authors, exclusive, intent, lock, locks, page, pages, placed, placing, programming, select, selecting, sql

Why intent exclusive lock is placed on pages while selecting data

On Programmer » SQL

1,656 words with 1 Comments; publish: Thu, 29 May 2008 02:53:00 GMT; (20093.75, « »)

Hi All,

Trying simple select like

select * from authors where au_id = '172-32-1176'

sql is placing intent exclusive locks on page. It is correct. To my

knowledge "selectâ' statement either share lock or intent share lock. (run sql

profiler for that spid & select event lock acquired & mode also data columns

)

Test this under sql server 2000 with sp3 with default isolation ( read

committed )

Thanks

DJ Pallerla

All Comments

Leave a comment...

  • 1 Comments
    • Never mind, looks like bug with sql profiler, it is not showing lock mode

      information correctly.

      If you filter for mode = 8 which ix, in the profile it is displaying as

      Share lock. Now I know the reason, nothing wrong with sql server.

      6= IS (Intent Shared). Indicates the intention to place S locks on some

      subordinate resource in the lock hierarchy.

      7= IU (Intent Update). Indicates the intention to place U locks on some

      subordinate resource in the lock hierarchy.

      8= IX (Intent Exclusive). Indicates the intention to place X locks on some

      subordinate resource in the lock hierarchy.

      "DJ" wrote:

      > Hi All,

      > Trying simple select like

      > select * from authors where au_id = '172-32-1176'

      > sql is placing intent exclusive locks on page. It is correct. To my

      > knowledge "selectâ' statement either share lock or intent share lock. (run sql

      > profiler for that spid & select event lock acquired & mode also data columns

      > )

      > Test this under sql server 2000 with sp3 with default isolation ( read

      > committed )

      > Thanks

      > DJ Pallerla

      >

      #1; Thu, 29 May 2008 02:54:00 GMT