מניעת הרצות בו זמניות של פרוצדורה

ג'וב אינו יכול לרוץ מספר פעמים במקביל: אם ננסה להפעיל אותו על ידי msdb..SP_Start_Job בשעה שהוא רץ- נקבל הודעת שגיאה ונסיון ההפעלה יכשל.
פרוצדורות אין בעייה להפעיל מספר פעמים במקביל: נכון שעלולות להיות נעילות בגלל פניה לאותם אובייקטים, אבל עצם ההפעלה בו זמנית – אפשרית.
יתכן מצב בו נרצה למנוע אופציה זו, למשל: בפוסט קודם הצעתי פתרון למניעת ריבוי שורות פעילות בטבלה, כאשר שורה פעילה היא שורה שמייצגת תהליך שרץ, ואיננו מעוניינים שאותו תהליך ירוץ יותר מפעם אחת בו זמנית. למעשה מדובר בדיוק בנושא של פוסט זה, רק ששם זה ברמת טבלת הניהול, ואילו כאן- ברמת הפרוצדורה.

ניצור פרוצדורה לצורך ההמחשה הטכנית:

If Object_Id('MyProc','P') Is Not Null Drop Proc MyProc

Go

Create  Proc MyProc As

Begin   Tran

Exec    SP_GetAppLock @Resource='MyResource',

                      @LockMode='Exclusive';

WaitFor Delay '00:00:10';

Exec    SP_ReleaseAppLock @Resource='MyResource';

WaitFor Delay '00:00:10';

Commit  Tran

Go

הפרוצדורה נועלת את עצמה בעזרת SP_GetAppLock,

ממתינה 10 שניות,

משחררת את הנעילה בעזרת SP_ReleaseAppLock (אם לא נשחרר- זה ישתחרר אוטומטית עם סיום הריצה),

ממתינה עוד 10 שניות;

וכל זה בתוך טרנזקציה כדי שתיווצר נעילה.

כעת נפתח שני Query (לשוניות) ב-SSMS, בכל אחד מהם נכתוב:

Exec MyProc;

Go

נפעיל את הראשון, ומייד נפעיל את השני.

כמה זמן תימשך הריצה?

ההפעלה הראשונה תנעל את עצמה, תמתין 10 שניות, תשחרר את הנעילה, תמתין עוד 10 שניות; ותסיים לאחר כ-20 שניות בסה"כ.

ההפעלה השניה- תיאלץ להמתין כ-10 שניות עד שתוכל להתחיל לרוץ, עוד 20 שניות הריצה עצמה, ובסה"כ כ-30 שניות (בפועל הריצה תימשך 2-3 שניות פחות מכיוון שיחלפו 2-3 שניות מרגע שהפעלנו את הראשונה ועד שנפעיל את השניה).

פרוצדורות המערכת SP_Who2 ו-SP_Lock מציגות את שתי ההרצות כך (Session מספר 102 שהופעל ראשון, ומספר 104 שהופעל שני):

 

בגריד העליון ניתן לראות ש-104 נעולה על ידי 102 (העמודה הרביעית משמאל- לא רואים את כותרות העמודות)