9.06.2009

Asynchronous Database Applications – Asynchronous Triggers

I always hate developing triggers cause, the transaction that SQL Server handles gets bigger by this way. For example let’s design a table which holds  Video data like videoid,title,length status ..etc. When we want to insert a new row into Video table, if there is a trigger on this table the insertion time will increase so it will be a performance overhead. With the SQL Server 2005 Service Broker we can write asynchronous triggers which separates trigger’s unit of work’s transaction from insertion transaction

use BlogDB
go
--enable service broker
alter database BlogDB
set enable_broker ;
go

if exists(select * from sys.procedures where name = 'procSuspendVideoClipFromQueue')
drop proc procSuspendVideoClipFromQueue
go
CREATE PROCEDURE procSuspendVideoClipFromQueue
AS
SET NOCOUNT ON
;
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @Message XML;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body

FROM [VideoQueue];

declare @videoId uniqueidentifier


IF
(@Handle IS NOT NULL AND @Message IS NOT NULL)
BEGIN

SELECT
@videoId = tmpVideo.Column1.value('@VideoId', 'uniqueidentifier')
FROM @Message.nodes('/row') AS tmpVideo(Column1)

exec procSuspendVideoClip @videoId

END
go

-- Create Message Type

CREATE MESSAGE TYPE [VideoMessage] VALIDATION = WELL_FORMED_XML;

GO

-- Create Contract

CREATE CONTRACT [VideoContract] ([VideoMessage] SENT BY ANY);

GO

CREATE QUEUE VideoQueue WITH ACTIVATION

(STATUS = ON, MAX_QUEUE_READERS = 1,

PROCEDURE_NAME = procSuspendVideoClipFromQueue,

EXECUTE AS OWNER);

GO

CREATE SERVICE [VideoService] ON QUEUE [VideoQueue] ([VideoContract]);
go

if exists(select * from sys.triggers where name = 'trgVideoStatus')
drop trigger trgVideoStatus
go
CREATE TRIGGER trgVideoStatus ON Video AFTER INSERT,Update
AS
BEGIN

SET NOCOUNT ON
;

DECLARE @MessageBody XML;

if update(VideoStatusId)
begin
set
@MessageBody = (SELECT VideoId FROM Inserted FOR XML RAW, TYPE);
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [VideoService]
TO SERVICE 'VideoService'
ON CONTRACT [VideoContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE [VideoMessage](@MessageBody);
end
END
GO


2 yorum:

bulente dedi ki...

Hi,
Do we lose the transactional consistency between the real db transaction and the async trigger execution ?
Regards,
Bulent ERdemir

montoya dedi ki...

hi Mr. Erdemir
If we think classical triggers, when
the trigger can not do it's job the DML(insert,update,delete)'s transaction is rolled back so the time to insert,update or delete increases by this way. With the help of async. triggers put a message to queue, then process the message so we have got two transactions, one is DML's transaction and the other is your async. triggers procedure. To sum up my words your question's answer is yes :)