Posting Yak Master
Posted - 12/15/2004 : 11:21:25
We are running a transaction1 in Read committed mode in which we are inserting into a table and selecting from 2nd table like:
Insert into tab1 select * from tabl2
Also in transaction2 we want to insert into table2 like
Bulk insert tab2…….
Strangely, trans1 is holding X lock on page of tab2 (so trans2 is waiting for this page to be released) so we have 3 questions/doubts:
1) Why SQL Server needs X lock on a page for select statement
2) 2nd problem is SQL Server is not holding IX lock on a table2 in trans1 so howcome SQL Server have X lock on a page without IX lock on table
3) Trans2 is able to get BU lock on a table but waiting for X lock on a page that is hold by trans1 (this is problem since trans1 should have IX lock on a table so that BU lock for trans2 should also not be granted)
Any opinion or suggestions will be appreciated for this locking behavior.