Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snanda
Starting Member
1 Post |
 Posted - 2011-11-21 : 06:16:56
|
| I have a scenario where1. I have a small table 'table1' with 25 columns and 100000 rows of data.2. I have a larger table 'table2' with 75 columns and 100000000 rows of data.3. 'table2' has a FK on 'table1'4. The Foreign keys on both the tables are disabled.5. Now, when the below steps are performed around the same time:a. Add a new not-null column with default value to the larger table, 'table2'b. Update a column in 'table1'I observed that most of the times, the add column (step 5.a) blocked the 'update' to the smaller table. At all these times, it was blocking with wait_type LCK_M_SCH_S.A very few times, the addcolumn didn’t block the 'update'. At these times, the 'update' was completed within seconds.Why does SQL Server need a schema lock on table1 when we are trying to modify table2? |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2011-11-21 : 06:32:15
|
| The PK on table1 is probably the clustered index and is being referenced by the FK on table2.To avoid the blocking, create an additional nonclustered index on the PK column(s) of table1. |
 |
|
|
|
|
|
question on schema locking