درک اهمیت استفاده از SQL Server Service Broker با یک مثال

نوشته شده توسط: میلاد فیروزی

۱۴ دی ۱۳۹۴

زمان مطالعه: 14 دقیقه

مقدمه

آیا تا به حال به مشکل Transactionهای خیلی بزرگ برخورده اید؟
آیا تا به حال INSERT و یا UPDATE شما معطل Trigerهای وحشتناک شده است؟

خوب خیلی اوقات بعد از Insert کردن یک سطر در یک جدول نیاز داریم تا یک سری اتفاق ها بیوفتد ، مثلا در جدول فروش یک رکورد زده ایم ، حالا می خواهیم به خریدار اس ام اس یا ایمیل بزنیم که سفارشش ثبت شده است و یا اینکه در یک جدول دیگر نیاز داریم موجودی یک کالا را یک عدد کمتر کنیم و خیلی کارهای دیگر ، حال همیشه همه ی این اتفاق ها در یک Transaction بزرگ اتفاق می افتد که این مساله قطعا دیتابیس را در تعداد درخواست های بالا درگیر خواهد کرد و با مشکلات عدیده Locking و Blocking برخورد خواهید کرد.

حال جدا از هر راه حلی که شما به عنوان یک توسعه دهنده وب و یا یک برنامه نویس ممکن است به ذهن شما خطور کند ، SQL Server هم برای این موضوع راه حلی ارائه داده که بررسی کردنش نه تنها خالی از لطف نیست بلکه ممکن است شما را مجاب نماید تا از این قابلیت SQL Server استفاده نمایید.

SQL Server Service Broker سرویسی می باشد که SQL به شما ارائه می دهد تا با تعریفی از پیغام ها و صف ها شما بتوانید صفی از کارها تعریف نمایید تا اگر نیاز ندارید یک سری تغییرات آنی و در لحظه انجام پذیرند ، آن کارها را به این سرویس بسپارید تا برای شما به ترتیب کارها را انجام دهد.

این سرویس شاید به صورت آنی (Syncronous) نباشند ولی قطعا خیلی سریع همه ی کارها را انجام خواهد داد ، این نوع سرویس ها به اصطلاح (Asyncronous) نامیده می شوند.

برخی از مزیت های این سرویس عبارتند از

دلیور شدن پیغام و یا به عبارتی انجام شدن کار به صورت تضمین شده ، حتی اگر هدف شما آفلاین باشد سرویس پیغام را تا آنلاین شدن نگهداری می کند.

ارتباط Asyncronous
قابلیت پشتیبانی از APIهای عمومی
Transactionها
گزینه های Backup

این سرویس چگونه کار می کند؟

همان طور که شاید حدس زده باشید این سرویس از طریق تعریف یک صف از پیغام ها کار می کند به گونه ای که شما به سادگی یک صف مقصد تعیین کرده و پیغام ها را به آن می فرستید ، این صف مقصد می تواند Local و یا Remote باشد ، حتی اگر این صف خارج از دسترس هم باشد پیغام های شما به یک حالت Pending خواهند ماند تا صف مقصد در دسترس قرار بگیرد و پیغام فرستاده شود. در صف مقصد نیز سرویسی وجود دارد که کارهای تعریف شده را انجام خواهد داد و پیغامی مبنی بر انجام شدن کار به صف مبدا اعلام خواهد کرد.

چرا باید از Service Broker استفاده کنیم؟

به شما کمک خواهد کرد تا وابستگی (Coupling) بین اپلیکیشن های مختلف سیستم شما کمتر شود.

به شما این امکان را می دهد تا خارج از معماری سیستم شما یک سری کارهای پیچیده روی سرورهای دیگری صورت پذیرد.

به شما کمک می کند تا با کمترین تاثیر روی کاربران نرم افزار شما قسمت های مختلف سیستم را به صورت آفلاین Upgrade کنید.

شما می توانید زمان انجام شدن کارها را خودتان تعیین کنید ، به عنوان مثال یک سری پردازش های سنگین را به نیمه شب موکول کنید که بار کمتری روی سرورهای شما قرار دارد.

حال زمان یک مثال فرا رسیده است.

فرض کنید می خواهیم یک وبسایت رزرو بلیط راه بیاندازیم که ۱۰۰۰ کاربر هم زمان دارد و نیاز داریم تا سرعت وبسایت را در بالاترین سطح ممکن نگه داریم. ۲ قسمت از کار هستند که نیاز به آن نیست که به صورت آنی انجام پذیرند و می توانند به زمانی که بار کمتری روی سرور قرار دارد موکول شوند ، چردازش پرداخت ها و پرینت بلیط ها.

به عکس زیر دقت کنید

کاربر دکمه “رزرو بلیط” را می زند.

وب سرور یک رکورد رزرو در دیتابیس INSERT می کند.
وب سرور یک پیغام به پردازشگر پرداخت مقصد ارسال می کند.

سرویس پردازش پرداخت پیغام را گرفته و پردازش می کند.

۱ . اگر پرداخت موفقیت آمیز باشد یک پیغام موفقیت به پردازشگر پرداخت مبدا ارسال می کند.

۲ . اگر موفقیت آمیز نبود یک پیغام عدم موفقیت ارسال می کند.

SQL Server صف مبدا را برای دریافت پاسخ پرداخت Monitor می کند.

۱ . اگر پرداختی موفقیت آمیز نبود در رکورد رزرو پرداخت را Failed ذخیره می کند.

۲ . اگر پرداخت موفقیت آمیز بود در رکورد رزرو پرداخت را Success ذخیره می کند و یک پیغام به صف مقصد پرینت می فرستد.

سرویس پرینت بلیط پیغام را از صف پرینت مبدا برداشته پرینت را انجام می دهد.

۱ . اگر پرینت موفقیت آمیز بود یک پیغام Success به صف پرینت مبدا می فرستد.

۲ . اگر موفقیت آمیز نبود پیغام Failed می فرستد.

SQL Server از صف مبدا پرینت پیغام Success و یا Failed را برمی دارد و بلیط را آپدیت می کند.

با این روش می توانیم پروسه پردازش پرداخت و پرینت را حتی به سرورهای دیگری موکول کنیم و یا حتی تایم اجرا شدن آن ها را تا زمانی که سر سرور خلوت می باشد به تعویق بیاندازیم. حال اگر می خواستید این عملیات را به صورت آنی (Synchronously) انجام دهید قطعا با دردسر های خیلی بزرگتر مواجه می شدید.

اولین کاری که می بایست برای پیاده سازی این سیستم انجام دهیم این است که باید ۴ نوع پیغام (Message) تعریف کنیم ، درخواست و پاسخ (Request/Response) پرینت و Request/Response پردازش پرداخت.به کد زیر توجه کنید ،

CREATE MESSAGE TYPE [PrintRequest] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [PrintResponse] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ProcessPaymentRequest] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ProcessPaymentResponse] VALIDATION = WELL_FORMED_XML;

تنها محدودیت برای این پیغام ها این می باشد که می بایستی Well Formed XML باشند که ایرادی ندارد چون تنها نرم افزار ما می باشد که پیغام ها را ارسال می کند.

در مرحله ی بعدی می بایست صف (Queue) هایی را تعریف کنیم تا پیغام ها از یا به آن ها ارسال شوند. در این مثال به ازای هر Message یک Queue داریم ،

CREATE QUEUE PrintInitiatorQueue WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [BrokerPrintMessageProcessed],
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
)
CREATE QUEUE PrintTargetQueue WITH STATUS = ON
CREATE QUEUE ProcessPaymentInitiatorQueue WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [BrokerPaymentMessageProcessed],
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
)
CREATE QUEUE ProcessPaymentTargetQueue WITH STATUS = ON

همانطور که در تصویر اول بحث دیدید Queueهای مقصد (Target) توسط سرویس های جداگانه ای پردازش می شوند ولی Queueها مبدا (Initiator) توسط SQL Server پردازش می شوند.همان طور که در کد بالا مشاهده می کنید برای Initiator Queueها یک Activation تعریف شده است ، Activation راهیست برای این که به Service Broker بگوییم زمانی که یک Message به Queue اضافه می شوذ ، یک Procedure و یا یک CLR Method را اجرا کند.

خوب بیایید یک بار دیگر بررسی کنیم ، Target Queueها جایی هستند که Messageها برای اولین بار فرستاده می شوند و Initiator Queueها جایی هستند که Responseها فرستاده می شوند ، از این نقطه یک مکالمه (Conversation) آغاز می شود و تا زمانی ادامه پیدا می کند که هر دو طرف Conversation را پایان دهند.

حال می بایستی چند قرارداد (Contract) برای تعریف نوع Messageهایی که مجاز هستند در Conversation شرکت کنند ، مشخص کنیم ،

CREATE CONTRACT [ProcessPaymentContract]
(
[ProcessPaymentResponse] SENT BY TARGET,
[ProcessPaymentRequest] SENT BY INITIATOR
)
CREATE CONTRACT [PrintContract]
(
[PrintRequest] SENT BY INITIATOR,
[PrintResponse] SENT BY TARGET
)

آخرین مرحله تولید خود سرویس ها می باشد ،

CREATE SERVICE PrintInitiatorService ON QUEUE PrintInitiatorQueue(PrintContract)
CREATE SERVICE PrintTargetService ON QUEUE PrintTargetQueue(PrintContract)
CREATE SERVICE ProcessPaymentInitiatorService ON QUEUE ProcessPaymentInitiatorQueue(ProcessPaymentContract)
CREATE SERVICE ProcessPaymentTargetService ON QUEUE ProcessPaymentTargetQueue(ProcessPaymentContract)

حال همه چیز آماده است تا بتوانیم Messageها را فرستاده و دریافت کنیم.

در مثال ما رزرو بلیط توسط یک Stored Procedure به نام CreateBooking صورت می پذیرد ، در این Stored Procedure پس از INSERT شدن رکورد یک Message به سرویس ProcessPaymentTarget فرستاده می شود تا پردازش شود ، به کد زیر دقت کنید ،

CREATE PROCEDURE [dbo].[CreateBooking]
(
@EventId INT,
@CreditCard VARCHAR(20)
)
AS
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO Bookings(EventId,CreditCard)
VALUES(@EventId,@CreditCard)
DECLARE @BookingId INT = SCOPE_IDENTITY()
--Send messagge for payment process
DECLARE @ConversationHandle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [ProcessPaymentInitiatorService]
TO SERVICE 'ProcessPaymentTargetService'
ON CONTRACT [ProcessPaymentContract]
WITH ENCRYPTION = OFF
DECLARE @Msg NVARCHAR(MAX)
SET @Msg = '' + CAST(@BookingId AS NVARCHAR(10)) + '' + @CreditCard + '';
SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [ProcessPaymentRequest](@Msg)
COMMIT
END TRY
BEGIN CATCH
RAISERROR('Booking Failed',1,1)
ROLLBACK
RETURN
END CATCH

همان طور که دیدید تمامی عملیات مشخص کردن منبع و مقصد سرویس ها و قراردادی که با آن می خواهید پیغام بفرتید در این Stored Procedure تعریف شدند .

بعد از سرویس PaymentProcess قرار بود پیغامی به سرویس Print فرستاده شود تا آن سرویس کار را به اتمام برساند ، قرار بود کارهای زیر به ترتیب انجام شوند ،

Message از ProcessPaymentInitiator Queue بیرون کشیده شود.

Message پردازش شده و رکورد رزرو برای پرداخت موفق یا ناموفق Update شود.

End Conversation Message به Payment Conversation فرستاده شود تا این مکالمه پایان یابد.

حال اگر پرداخت موفقیت آمیز بود یک Message به PrintTarget Queue فرستاده شود تا دستور پرینت بلیط صادر شده باشد.

نکته ای که قابل توجه می باشد این است که یک Conversation تنها زمانی خاتمه می باید که هر دو طرف End Conversation Message را ارسال کنند وگرنه Conversation تا ابد باز باقی خواهد ماند.

CREATE PROCEDURE [dbo].[BrokerPaymentMessageProcessed]
AS
DECLARE @ConversationHandle UNIQUEIDENTIFIER
DECLARE @MessageType NVARCHAR(256)
DECLARE @MessageBody XML
DECLARE @ResponseMessage XML
WHILE(1=1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
WAITFOR(RECEIVE TOP(1)
@ConversationHandle = conversation_handle,
@MessageType = message_type_name,
@MessageBody = CAST(message_body AS XML)
FROM
ProcessPaymentInitiatorQueue
), TIMEOUT 1000
IF(@@ROWCOUNT=0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
SELECT @MessageType
IF @MessageType = 'ProcessPaymentResponse'
BEGIN
--Parse the Message and update tables based on contents
DECLARE @PaymentStatus INT = @MessageBody.value('/Payment[1]/PaymentStatus[1]','INT')
DECLARE @BookingId INT = @MessageBody.value('/Payment[1]/BookingId[1]','INT')
UPDATE Bookings SET Bookings.PaymentStatus = @PaymentStatus WHERE Id = @BookingId
--Close the conversation on the Payment Service
END CONVERSATION @ConversationHandle
--Start a new conversation on the print service
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [PrintInitiatorService]
TO SERVICE 'PrintTargetService'
ON CONTRACT [PrintContract]
WITH ENCRYPTION = OFF
DECLARE @Msg NVARCHAR(MAX) = '' + CAST(@BookingId AS NVARCHAR(10)) + '';
SELECT @ConversationHandle;
SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [PrintRequest](@Msg)
END
COMMIT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END

چند نکته . . .

اگر به به TSQLی که برای ایجاد Queueها استفاده کردیم دقت کنید می بینید که Procedure بالا به صورت اتوماتیک زمانی که یک Message به PrinitInitiatorQueue فرستاده می شود فراخوانده می شود.

WHILE(1=1) : این Procedure در یک حلقه ی بی نهایت تا زمانی که صف پیغام ها تماما پردازش شود کار می کند تا مجبور نباشیم هر سری آن را از Activation فراخوانی کنیم.

این حلقه در این حالت یک ثانیه صبر می کند و اگر پیغامی دریافت نشد @@RowCount صفر خواهد شد و از آن جایی که این بدان معناست که پیغامی در صف وجود ندارد از Procedue خارج می شویم.

باقی ماجرا تقریبا واضح می باشد ، پیغام دریافت شده برای آپدیت کردن رکورد رزرو پردازش می شود ، پیغام پایان مکالمه فرستاده می شود ، مکالمه جدید برای پرینت باز خواهد شد ، پرینت صورت می پذیرد و مکالمه پایان می پذیرد.