הרצה אסינכרונית של פרוצדורות באמצעות Service Broker

הפוסט הבא מתבסס על פוסט שפירסמתי בעבר ב-www.SqlServer.co.il.

לעיתים אנחנו מעוניינים להריץ פרוצדורה באופן אסינכרוני, כלומר- להריץ אותה ובמקביל להמשיך בטיפול בנושאים אחרים.
כל עוד אנחנו עובדים ב-SSMS זו אינה בעייה- פותחים Query חדש, מפעילים שם את הפרוצדורה, ובינתיים חוזרים ל-Query עליו עבדנו וממשיכים בדרכנו;
אבל אם רוצים לעשות זאת מתוך פרוצדורה או קוד שרצים אוטומטית- זו קצת בעייה מכיוון שאם נפעיל מתוך פרוצדורה ראשית פרוצדורה משנית – הראשית תמתין עד שהמשנית תסתיים ורק אז תמשיך, שלא לדבר על כך שאם יש תקלה במשנית- גם הראשית תיפול בשל כך..
עד גרסת SQL Server 2005 בה הוצג ה-Service Broker הדרך היחידה להתמודד עם הבעייה הייתה על ידי הפעלה של הפרוצדורה המשנית בעזרת ג'וב – כשמפעילים ג'וב על ידי sp_Start_Job הוא רץ ברקע והקוד שהפעיל אותו אינו ממתין לסיומו.
כיום ניתן לעשות זאת בעזרת Service Broker, ויש לכך מספר יתרונות (בצד יכולות נוספות של הכלי שאינני נוגע בהן): מדובר בפתרון מובנה ולא מאולתר, ניתן יהיה להפעיל את פרוצדורה המשנה מספר פעמים במקביל – בניגוד לג'וב אותו לא ניתן, ואף להעביר לפרוצדורה פרמטרים בקלות יחסית.

ניצור לצורך העניין דטבייס, נאפשר בו שימוש ב-Service Broker, ונוודא שההגדרות תקינות:

USE master;

GO

If (Select Name From sys.Databases Where Name='MySrvBrkDB') Is Not Null Drop Database MySrvBrkDB;

Go

Create Database MySrvBrkDB;

Go

Alter Database MySrvBrkDB Set Enable_Broker;

GO

Use MySrvBrkDB

GO

Select    Is_Broker_Enabled,

        Name

From    sys.Databases

Where    Name='MySrvBrkDB'

ניצור טבלת עזר שלתוכה תשלח פרוצדורת המשנה P_MySrvBrkProc חיוויים בתחילת הריצה ובסופה כדי שנוכל לעקוב אחר מה שהתבצע באופן אסינכרוני:

Create Table T_MySrvBrk

    (Date datetime,

     Action varchar(100),

     Message varchar(100),

     Body Varchar(Max));

GO

כעת ניצור את האובייקטים השונים המהווים תשתית לשימוש ב-Service Broker על ידי היוזם:

Message – מקובל שההודעה היא מסוג Well_Formed_XML. זה יאפשר להעביר מספר פרמטרים בצורת XML סטנדרטי.

Contract – מי שולח, מי מקבל ומה סוג ההודעה. כרגע מצויין בו רק סוג ההודעה, אך בהמשך ניצור לכל צד Service שיקשר אליו את ה-Queue שלו.

Queue – התור של ההודעות של היוזם.

Service – מקשר בין Contract ו-Queue.

Create Message Type MyMessage

    Validation = Well_Formed_XML;  

GO

Select * From sys.Service_Message_Types Where Name='MyMessage';

--Contract ניצור

Create Contract MyContract

      (MyMessage

       Sent By Any);

GO

Select * From sys.Service_Contracts Where Name='MyContract';

--Queue ניצור 

Create Queue MyInitiatorQueue;

GO

Select * From sys.Service_Queues Where Name='MyInitiatorQueue';

--Service ניצור

Create Service MyInitiatorService

    On Queue MyInitiatorQueue

       (MyContract);

GO

Select * From sys.Services Where Name ='MyInitiatorService';

כעת ניצור את פרוצדורת המשנה בצד המקבל:

הפרוצדורה תופעל באופן אסינכרוני ביוזמת השולח,

תקבל הודעת XML ותשלוף ממנה את המידע (לצורך הדוגמה- שמי ושם משפחתי),

ותבצע פעולות שונות בעזרתו (לצורך ההדגמה- עדכון הטבלה שיצרנו קודם):

If Object_ID('P_MySrvBrkProc', N'P') Is Not Null Drop Procedure P_MySrvBrkProc

GO

Create Procedure P_MySrvBrkProc As

Begin

Declare @Conversation_handle UniqueIdentifier;

Declare @Message_body Varchar(Max);

Declare @Message_Type_Name sysName;

Declare @I Int,

        @FN Varchar(20),

        @SN Varchar(20);

--Get the massage from the queue

Receive Top(1)

        @Conversation_handle = Conversation_handle,

        @Message_body = Message_body,

        @Message_Type_Name = Message_Type_Name

From MyTargetQueue

--Extracts the content fro the XML

Exec sp_xml_preparedocument @I Output, @Message_body

Select    @FN=FirstName,

        @SN=SecondName

From    OpenXML (@I, '/Root',1)

        With (FirstName  Varchar(20),

            SecondName Varchar(20))

Select    @FN FirstName,

        @SN SecondName;

--Updates the table and execute the procedure body        

If @Message_Type_Name = 'MyMessage'

    Begin

    Insert Into T_MySrvBrk (Date, Action, Message, Body)

    Values (GetDate(), 'Start P_MySrvBrkProc', @Message_Type_Name, @FN + ' ' + @SN);

    WaitFor Delay '00:00:05'; --Represents the execution which lasts 5 seconds

    Insert Into T_MySrvBrk (Date, Action, Message, Body)

    Values (GetDate(), 'End P_MySrvBrkProc', @Message_Type_Name, @FN + ' ' + @SN);

    End

End;

GO

ניצור את התשתית לשימוש ב-Service Broker על ידי הצד המקבל:

Queue – תור של ההודעות המתקבלות.

Service – מקשר בין התור המקבל וה-Contract המשותף לשני הצדדים.

--Queue ניצור

Create Queue MyTargetQueue

    With Status = On,

         Retention = Off,

         Activation(Status = On,

                    Procedure_Name = P_MySrvBrkProc,

                    MAX_Queue_Readers = 1,

                    Execute As Owner);

GO

Select * From sys.Service_Queues Where Name='MyTargetQueue';

--Service ניצור

Create Service MyTargetService

    On Queue MyTargetQueue

       (MyContract);

GO

Select * From sys.Services Where Name ='MyTargetService';

וכעת, כדי שהצד היוזם יפעיל באופן אסינכרוני את הפרוצדורה ויעביר אליה פרמטרים, ניצור פרוצדורה מתאימה.

אפשר לוותר על הפרוצדורה ולהסתפק בקוד שבתוכה, אך בהמשך כשנרצה להפעיל את פרוצדורת המשנה שהגדרנו קודם מספר פעמים במקביל – יהיה יותר נוח בעזרת הפרוצדורה הבאה (שיוזמת Dialog עם הצד השני ובו היא מעבירה XML שכולל את שמי ושם משפחתי):

Create Procedure P_MainSrvBrk As

Declare @Cnvrst UniqueIdentifier;

Declare @Message Varchar(Max);

Select @Message ='<Root FirstName="Geri" SecondName="Reshef"></Root>'

Begin Dialog @Cnvrst

     From Service MyInitiatorService

     To Service  N'MyTargetService'

     On Contract  MyContract

     With Encryption = Off;

Send On Conversation @Cnvrst

     Message Type MyMessage (@Message);

End Conversation @Cnvrst;

Go

נפעיל את הפרוצדורה הראשית, כאשר רצוי לבדוק את הטבלה לפני ההרצה (לוודא שהיא ריקה), מייד לאחר ההרצה (פרוצדורת המשנה התחילה אך טרם הסתיימה ולכן העבירה רק את הודעת הפתיחה), וכעבור מספר שניות (לוודא שפרוצדורת המשנה סיימה והעבירה את הודעת הסיום):

Exec P_MainSrvBrk;

Go

Select *

From MySrvBrkDB..T_MySrvBrk

Order By Date;

מה יקרה אם נריץ אותה עשר פעמים בו זמנית?

Exec P_MainSrvBrk;

Go 10

Select *

From MySrvBrkDB..T_MySrvBrk

Order By Date;

הריצות ימשכו כ-50 שניות ובמהלכן נוכל לראות שהטבלה אט אט מתמלאת ובכל פעם שריצה מסתיימת – הבאה אחריה מתחילה.

הסיבה שהריצות מתבצעות בטור ולא במקביל היא שב-Queue של הצד המקבל הגדרנו MAX_Queue_Readers = 1 כלומר – בכל פעם קריאה אחת מהתור..

נתקן אם כך את ההגדרה של ה-Queue ל-100,

נרוקן את הטבלה כדי שנוכל לעקוב אחר מה שקורה מבלי שהמידע הקודם יפריע לנו,

ולבסוף- חוץ מלעקוב אחר הטבלה שתתמלא על ידי מאה ההפעלות של פרוצדורת המשנה – נעקוב גם אחרי ה-Processes שיפתחו לכל הפעלה:

Alter Queue MyTargetQueue

    With Status = On,

         Retention = Off,

         Activation(Status = On,

                    Procedure_Name = P_MySrvBrkProc,

                    MAX_Queue_Readers = 100,

                    Execute As Owner);

Go

Truncate Table MySrvBrkDB..T_MySrvBrk

Go

Exec P_MainSrvBrk;

Go 100

Select *

From MySrvBrkDB..T_MySrvBrk

Where    Action='Start P_MySrvBrkProc'

Order By Date;

Go

Select    * 

From    sys.sysprocesses

Where    dbid=DB_ID()

        And spid<>@@SPID;

Go

כפי שאפשר לשים לב- לא כל מאה ההפעלות של פרוצדורת המשנה התרחשו בו זמנית: בהתחלה הייתה הפעלה אחת, לאחר מכן שתי הפעלות במקביל, שלוש במקביל, וכך הלאה עד לשלוש עשרה הפעלות במקביל.

ניתן לראות זאת בבירור אם נקבץ את נקודות ההתחלה יחד (בגלל שעלול להיות הפרש של מספר אלפיות שניה- "איישר" את התוצאות לשניה הקרובה):

Select    Date,

        Count(*) Camut

From    (Select    DateAdd(S,DateDiff(S,Cast(DateDiff(D,0,GetDate()) As DateTime),Date),Cast(DateDiff(D,0,GetDate()) As DateTime)) Date

        From MySrvBrkDB..T_MySrvBrk

        Where    Action='Start P_MySrvBrkProc') T

Group By Date

Order By Date;

לסיום- במקרה הצורך ניתן למחוק את כל האובייקטים שיצרנו:

Drop Table T_MySrvBrk

Go

Drop Service MyTargetService;

Go

Drop Queue MyTargetQueue;

Go

Drop Procedure P_MySrvBrkProc;

Go

Drop Procedure P_MainSrvBrk;

Go

Drop Service MyInitiatorService;

Go

Drop Queue MyInitiatorQueue;

Go

Drop Contract MyContract

Go

Drop Message Type MyMessage

Go

Use master

GO

Drop Database MySrvBrkDB;

Go