מניעת ריבוי שורות פעילות בטבלה

במקום העבודה שלי יש טבלה ובה רשימת תהליכים שמריצים מדי פעם. יכול להיות שמדובר בטבלת התהליכים עצמה, ויכול להיות שמדובר בטבלת לוג בה מצויין איזה תהליך רץ מתי. מה שחשוב לענייננו הוא שיש שם עמודת סטטוס שמציינת איזה תהליך רץ כעת, ומותר רק לתהליך אחד לרוץ בכל רגע נתון, כלומר- שורה אחת בלבד עם סטטוס 1 וכל השאר עם סטטוס 0.
הטיפול בזה מתבצע כמובן בקוד שאמור לעדכן את השורה עם תחילת הריצה ובסיומה, וכן במקרה של תקלה ועצירה לא צפויה של התוכנית; אבל אנחנו רוצים לגבות את זה כך שלא תהיה כל אפשרות מעשית לעקוף את הקוד בזדון או בשוגג ולהריץ שני תהליכים במקביל.

הנתונים- טבלת ג'ובים ובה מספר תהליכים שמייצגים תהליכים היפותטיים שניתן להריץ, בלשב זה לכולם סטטוס 0:

Use tempdb;

Go

If Object_ID('T_Jobs','U') Is Not Null Drop Table T_Jobs;

Go

Create Table T_Jobs(ID Int Identity Primary Key,

                    Job Varchar(20),

                    Status TinyInt);

Go

Insert

Into    T_Jobs(Job, Status)

Select  'Job 1',0 Union All

Select  'Job 3',0 Union All

Select  'Job 4',0 Union All

Select  'Job 5',0 Union All

Select  'Job 8',0;

Go

Select  *

From    T_Jobs;

פתרון אחד הוא שימוש ב-Indexed Views, כלי שקיים מגרסת 2005: ניצור View המתבסס על הטבלה עם תנאי Status=1, וניצור אינדקס Unique על עמודת סטטוס (מכיוון שלפי התנאי של ה-View יהיה בה רק הערך 1 האינדקס מחייב שתהיה שורה אחת לכל היותר):

If Object_ID('V_Jobs','V') Is Not Null Drop View V_Jobs;

Go

Create View dbo.V_Jobs With SchemaBinding As

Select  ID, Job, Status

From    dbo.T_Jobs

Where   Status=1;

Go

Create Unique Clustered Index Idx_V_Jobs On dbo.V_Jobs(Status);

Go

Select  *

From    V_Jobs;

כפי שאפשר לראות- בינתיים אין אף שורה עם סטטוס 1.

ננסה לעדכן כעת את הטבלה עם סטטוס 1:

Update T_Jobs

Set    Status=1

Where  Id=2;

Update T_Jobs

Set    Status=1

Where  Id=3;

העדכון הראשון הסתיים בהצלחה (1 row(s) affected),

והעדכון השני נכשל בגלל הפרת תנאי המפתח.

לפני שנעבור לפתרון השני- נבטל את ה-Indexed View:

If Object_ID('V_Jobs','V') Is Not Null Drop View V_Jobs;

Go

פתרון אחר, פשוט יותר, הוא שימוש ב-Filtered Index; כלי שקיים מגרסת 2008:

Create Unique Index Idx_T_Jobs On T_Jobs(Status) Where Status=1;

Go

באופן דומה לפתרון הקודם- יצרנו אינדקס Unique רק על השורות בהן הסטטוס הוא 1.

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

Update T_Jobs

Set    Status=1

Where  Id=3;

גם במקרה זה האינדקס מנע כפילות בסטטוס=1.

שימוש יותר מתקדם בשיטה הזו הוא בטבלאות Slowly Changing Dimension בהן השורות הפעילות מסומנות באופן דומה לנ"ל. מקרה זה קצת יותר מסובך מכיוון שיש הרבה שורות פעילות, ולכן הפילטר בשני המקרים יהיה על ה-ID ועל סטטוס=1.