April 3, 2008 at 3:19 am
Hello,
SQL Server 2000/
I have to modify in procedure some data in the table that have trigger.
First I make simple
begin tran
alter table myTable disable trigger trMyTable_upd
...........
some update
...........
alter table myTable enable trigger trMyTable_upd
commit / rollback tran
I thought that altering of table make blocking of table.
So I modified procedure like:
begin tran
create table #trigger$skip(pk int)
...........
some update
...........
drop table #trigger$skip
commit / rollback tran
I have checked on test db. All worked well.
But when I adjust on production db - I received many blocking.
In few seconds I received near 600 blocked processes.
Could you please explain what the reson can be and
what way select ?
Thank you.
April 3, 2008 at 6:07 am
"create table" statement causes schema lock because it changes the schema of the database.
Because you create # table it locks tempdb.
Lock stays there until transaction is finished.
Because you probably have quite complex code in between it probably locks a lot of objects in user database. Some of them are probably deadlocks.
Are you sure you need that explicit transaction?
_____________
Code for TallyGenerator
April 3, 2008 at 6:31 am
yes, because I have insert in two tables.
April 3, 2008 at 4:47 pm
Can you start transaction AFTER you created and populated # table?
_____________
Code for TallyGenerator
April 3, 2008 at 4:51 pm
And probaly your problem is within that "some update" thing.
Probably even in "CREATE TABLE" thing used iwithin that "some update".
They must be causing the locking, tempdb is just happenned to be caught in the bad company. 🙂
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply