הבלוג של גרי רשף

17/11/2011

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

Filed under: Uncategorized — גרי רשף @ 17:37

במקום העבודה שלי יש טבלה ובה רשימת תהליכים שמריצים מדי פעם. יכול להיות שמדובר בטבלת התהליכים עצמה, ויכול להיות שמדובר בטבלת לוג בה מצויין איזה תהליך רץ מתי. מה שחשוב לענייננו הוא שיש שם עמודת סטטוס שמציינת איזה תהליך רץ כעת, ומותר רק לתהליך אחד לרוץ בכל רגע נתון, כלומר- שורה אחת בלבד עם סטטוס 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;

clip_image002

פתרון אחד הוא שימוש ב-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;

clip_image004

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

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

Update T_Jobs
Set    Status=1
Where  Id=2;

Update T_Jobs
Set    Status=1
Where  Id=3;

clip_image006

העדכון הראשון הסתיים בהצלחה (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;

clip_image008

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

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

מודעות פרסומת

2 תגובות »

  1. שאלה: למה לא ליצור Unique על הטבלה ישירות אלא על ה-VIEW? האם יש בזה יתרון?

    תגובה של איתי — 20/11/2011 @ 11:01

    • אי אפשר ליצור Unique Index "רגיל" על העמודה כי הערך 0 מופיע בה מספר פעמים.
      הערך 1 לעומת זאת מופיע רק פעם אחת, ולכן רק עליו יש ליצור אינדקס יוניקי.
      הצעתי שתי דרכים כיצד לעשות זאת.

      תגובה של גרי רשף — 20/11/2011 @ 20:14


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: