Locking Issues and Types

• raj-247939

  SSCertifiable

  Points: 5999

  More actions

  November 25, 2005 at 9:49 pm

  #167184

  Hi,

  I got confused as i read lot of documentation about lock type / Locking.

   

   

  54	6	0	0	DB		S	GRANT
  54	6	1076407104	0	TAB		IS	GRANT
  54	6	1076407104	1	PAG	1:4908422	S	GRANT
  54	6	1076407104	255	PAG	1:2081187	S	GRANT
  when issued delete statement where a select statement is already running on the same table
  54	6	1076407104	255	PAG	1:1263264	S	GRANT	Shared Lock
  54	6	0	0	DB		S	GRANT	Shared Lock
  54	6	1076407104	0	TAB		IS	GRANT	On Table Intent Shared Lock
  54	6	1076407104	1	PAG	1:4908964	S	GRANT	Shared Lock
  55	1	85575343	0	TAB		IS	GRANT
  57	6	1076407104	1	PAG	1:4991145	U	GRANT	Update Lock
  57	6	0	0	DB		S	GRANT	Shared Lock
  57	6	1076407104	0	TAB		IX	GRANT	Intent Exclusive
  57	6	1076407104	255	PAG	1:1754553	X	GRANT	Exclusive Lock

   

  Pls explain  what is going on here as i know

  IS is issued on the table (transaction will read some ) but i issued the command select * from Table name.

  S is issued on pag , DB  because the transaction is reading the data.

  as the above two lock are aready issued on the table then why there is update lock and intent exclusive locks when i try to delete few rows.

  IX the transaction will modify all but not all.

  X Completely lock the resources from anytype of access.

   

  from

  Killer

• November 28, 2005 at 8:00 am

  This was removed by the editor as SPAM

• Andy Smith

  SSC Rookie

  Points: 46

  More actions

  November 28, 2005 at 10:50 am

  #606168

  I'm no expert in locking, but from my limited knowledge the Update lock is the delete statement updating the table as there is no such thing as a delete lock.

  Intent locks are locks that will occur if the lock needs to be escalated, so if the delete lock starts locking more pages there comes a point where it is more efficient for SQL to lock the whole table hence the IX lock on the table.

  The same goes for the Shared locks from the Select statement, at the moment there is currently one a shared lock on a page, the IS on the table indicates that if required SQL while acquire a shared lock on the table.

  For more information lock at locking in the index of books online that explains reseanably well what the process is.

  Andy