April 14, 2011 at 5:53 am
I have a really odd problem I wanted to poste, as (a) I think some other people might have seen this also and (b) because I
just don't understand why SQL Server behaves the way it does in this case :hehe:
Our instance is set to isolation level snapshot read committed:
output from DBCC useroptions:
textsize2147483647
languageus_english
dateformatmdy
datefirst7
lock_timeout-1
quoted_identifierSET
arithabortSET
ansi_null_dflt_onSET
ansi_warningsSET
ansi_paddingSET
ansi_nullsSET
concat_null_yields_nullSET
isolation levelread committed snapshot
I have reduced the example to something easy to replicate:
I have a table such as:
CREATE TABLE [dbo].[business_test](
[business_test_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[business_id] [numeric](18, 0) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[create_timestamp] [datetime] NULL,
[update_timestamp] [datetime] NULL,
[delete_timestamp] [datetime] NULL,
[modify_user] [varchar](64) NULL,
CONSTRAINT [PK_business_business_test] PRIMARY KEY CLUSTERED
(
[business_test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I then create 2 triggers:
create TRIGGER [dbo].[tr_bi_business_test]
ON [dbo].[business_test]
FOR INSERT
AS
BEGIN
UPDATE dbo.business_test SET modify_user=USER_NAME(USER_ID()),
create_timestamp=GETDATE()
WHERE business_test_id IN (SELECT business_test_id FROM inserted)
END
CREATE TRIGGER [dbo].[tr_bu_business_test]
ON [dbo].[business_test]
FOR UPDATE
AS
BEGIN
IF NOT UPDATE(update_timestamp)
UPDATE dbo.business_test
SET update_timestamp=GETDATE(),
modify_user=USER_NAME(USER_ID())
WHERE business_test_id IN (SELECT business_test_id FROM inserted)
END
Now open one window in SSMS and execute the following command:
In one window execute the following steps
create table #excluded_businesses
([business_id]numeric
)
BEGIN TRANSACTION
;disable trigger tr_bi_business_test on business_test
;disable trigger tr_bu_business_test on business_test
-- for those businesses where the model has changed set the end date for the currently defined model
UPDATE [business_test]
SET[end_date] = CASE
when [business_test].start_date > dateadd(day, -1, GETDATE())
THEN [business_test].start_date
else dateadd(day, -1,GETDATE())
END,
[update_timestamp] = GETDATE(),
[modify_user] = 'whatever'
FROM#excluded_businesses
where[business_test].business_id = #excluded_businesses.business_id
Then in another window:
select * from [business_test]
and you will get a LCK_M_SCH_S lock
NOW: If you disable the triggers before the transaction everything works well. I have absolutely no idea why this is or why SQL Server behaves that way.
Does anyone have some clue? Just to satisfy my curiosity.
Sergio
April 14, 2011 at 5:59 am
That's a schema stability lock. All queries in all isolations take schema stability locks so that the table cannot change in structure while they are working on it.
What's causing the problems here is the disable trigger that you have there. That's a schema modification, it requires a schema modification lock, and that (schema changes) is the only thing that is blocked by an Sch-S lock. The sch-M lock is held until the end of the transaction (as with all modification locks)
This is not unique to snapshot isolation, it would behave much the same way in all isolation levels. Snapshot just stops reads from taking shared data locks.
The question is, why are you disabling triggers in a transaction?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2011 at 6:19 am
Thanks for the quick reply. Now it makes sense to me.
One thing I have omitted was that there is also a BEGIN TRY where I would rollback the transaction in case of a problem.
The idea of putting it into a transaction was that if I was going to rollback that it would also re-enable the triggers.
Cheers
Sergio
April 14, 2011 at 6:24 am
Ok, let me be clearer.
Why are you disabling and re-enabling triggers?
Do you realise that if some other update runs, from another session, during yours, the triggers wil be disabled for that as well?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2011 at 7:55 am
Ah. Ok I understand your question now.
Yes, I know that during this time the triggers aren't firing. However, for reasons which are not important, I can quarantee that I am the only one accessing this table at this time.
The main reason for disabling/enabling the triggers is simply the fact that they caused some performance issues. disabling them did away with it.
April 14, 2011 at 8:01 am
slaberer (4/14/2011)
The main reason for disabling/enabling the triggers is simply the fact that they caused some performance issues. disabling them did away with it.
I'd probably tune the triggers first.
If you insist, you will either need to disable/enable outside of the transaction (with associated risk) or hold a lock that will prevent any other access to the table at all until the tran commits or rolls back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2011 at 8:32 am
thank you for your help. Unfortunately the triggers are really very simply and only set time and userid to the record. However, I will revisit it again.
April 14, 2011 at 3:42 pm
Your trigger is not correctly written and it can definitely do what they shouldn't
* Noel
April 15, 2011 at 12:27 am
ok. Could you please elaborate on that matter?
April 15, 2011 at 2:32 am
Post the trigger code. Triggers should not have major impacts, if they do, there's likely something wrong, but it's impossible to be specific without seeing the trigger definition.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 2:55 am
I had listed the code above. Here we go again:
create TRIGGER [dbo].[tr_bi_business_test]
ON [dbo].[business_test]
FOR INSERT
AS
BEGIN
UPDATE dbo.business_test SET modify_user=USER_NAME(USER_ID()),
create_timestamp=GETDATE()
WHERE business_test_id IN (SELECT business_test_id FROM inserted)
END
CREATE TRIGGER [dbo].[tr_bu_business_test]
ON [dbo].[business_test]
FOR UPDATE
AS
BEGIN
IF NOT UPDATE(update_timestamp)
UPDATE dbo.business_test
SET update_timestamp=GETDATE(),
modify_user=USER_NAME(USER_ID())
WHERE business_test_id IN (SELECT business_test_id FROM inserted)
END
April 15, 2011 at 3:18 am
I thought those were simplifications
The insert trigger is unnecessary. Set defaults on the columns and SQL will set the values for you without a trigger. The update trigger should not cause any major perf problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 4:03 am
thank you very much. I didn't even think of using default values. That's much better.
Greatly appreciate your help.
cheers
Sergio
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply