en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יול26

Written by: ronen ariely
26/07/2014 23:21 RssIcon

רקע:

Serializableרמת בידוד סדרתיות (Serializable) דורשת כי כל שאילתה שבוצעה במהלך עסקה חייבת לקבל את אותו הסט של שורות בכל פעם שהיא מתבצעת במהלך העסקה. השימוש ברמז לקביעת רמת בידוד של סדרתיות (TRANSACTION ISOLATION LEVEL) קובע את האפיונים הבאים:

• הוראות לא יכולות לקרוא נתונים, ששונו בעסקאות (transactions) אחרות ועדיין לא בוצע עליהן פעולת התחייבות (committed).

• עסקאות אחרות לא יכולות לשנות נתונים שנקראו על ידי העסקה הנוכחית, עד שהיא תסיים לבצע התחייבות.

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

נעילת טווח

על פי הגדרת ה BOL, נעילת טווח נועדה להגן על שורות אשר בצורה עקיפה או ישירה נמצאות באוסף השורות שנקראות על ידי הוראת שאילתה, בזמן שאנחנו עובדים ברמת בידוד סדרתית. נעילת טווח מפתח (key) מבטיחה לנו שמירת הדרישות של רמת הבידוד, על ידי מניעת שינויים במפתחות אשר עלולים להיות בטווח המפתחות שהשאילתה קוראת.

נעילת טווח מבוצעת רק על נעילת מפתחות (KEY locks). כפי שהשם מרמז, נעילת טווח נועלת את המפתחות בין שני מפתחות הנמצאים באינדקס, על ידי הגדרת הערכים של הערכים של ערכי המפתחות. נעילה זו מונעת הכנסה, עדכון או מחיקה של שורות בטווח זה.

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

* דוגמאות נוספות ניתן למצוא בקישור הבא:
http://technet.microsoft.com/en-us/library/ms191272(v=sql.105).aspx

מפתח ffffffffffff

על מנת לנעול את המפתחות בטווח בו הגבול העליון גדול יותר מהערך המקסימלי הנמצא באינדקס שלנו,  יש שימוש במפתח ייחודי שיש לו את הערך FFFFFFFFFFFF אשר מייצג את הערך המקסימלי של מפתח אינדקס (נחשב כ infinity key).

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

LockRequestMode

כל נעילת טווח מסווגת על ידי שני ערכים של מצב הנעילה (lock modes):

1. מצב נעילת טווח, 2. מצב נעילת שורה.

LockRequestMode הוא ערך המציג את מצב הנעילה בעזרת שימוש שני ערכים אלו. למשל אם יש לנו שורה שננעלת על ידי נעילה שיתופית S ומצב נעילת השורה שלנו גם הוא שיתופי, אז הערך של LockRequestMode יהיה RangeS-S. באופן דומה RangeI-N יהיה נעילת טווח של INSERT ונעילת שורה של NULL (אין שורה שצריך לנעול בזמן הכנסת שורה חדשה), RangeS-U יגדיר נעילה טווח שיתופי ונעילת שורה לשם ביצוע עדכון, RangeX-X מגדיר נעילת ייחודית של טווח וכן נעילת שורה של טווח, וישמש בזמן עדכון רשומה בטווח.

Unexpected Behavior using Serializable

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

ניצור טבלה פשוטה ונכניס 2 רשומות אל הטבלה.

use tempdb;
go
/************************************************ DDL+DML */
IF OBJECT_ID('Test') IS NOT NULL DROP TABLE Test
create table Test (k int);
create unique clustered index Test on Test (k);
truncate table test
insert into Test
select 2 union all
select 3;

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

/******************************************* Select all data */
begin tran;
select k from Test with (serializable) where k between 2 and 3 order by k;
exec sp_lock @@spid;

שימו לב שאנחנו עדיין בתוך העסקה ולא ביצענו פעולת התחייבות או ביטול (commit/rollback). עתה פתח חלון חדש של שאילתות על מנת לעבוד תחת Session שונה ונסה להריץ את השאילתה הבאה:

insert Test (k) values (4)
GO

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

בואו נבחן את הסיבות לחסימה. נחזור לחלון הראשון ונריץ את השאילתה הבאה (עדיין בתוך העסקה שלנו):

exec sp_lock @@spid;

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

האם היה לנו צורך בנעילה זו על מנת לעמוד בדרישות הבידוד שלנו?

חד משמעית לא! הרי שאנחנו מבקשים לקרוא רק שורות בהן הערך של הטור k הוא בין 2 לבין 3, לכן לפי חוקי הבידוד אין מניעה להכניס שורה חדשה לטבלה שערכו של k יהיה 4 למשל.

בואו נשחרר את הנעילה על ידי ביצוע ההתחייבות (commit) של העסקה לשם המשך הדיון. בחלון הראשי שלנו (זה שיוצר את הנעילה) הריצו את ההוראה:

commit;

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

הפתרון

נריץ את אותה שאילתה בדיוק בהבדל קטן על ידי שינוי סדר הרשומות,  ז"א במקום למשוך את הנתונים בסדר עולה, נמשוך אותם בסדר יורד. חושבים שזה אמור להשפיע?!? בואו נראה :-)

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

/************************************************ DDL+DML */
IF OBJECT_ID('Test') IS NOT NULL DROP TABLE Test 
create table Test (k int);
create unique clustered index Test on Test (k);
truncate table test
insert into Test
select 2 union all
select 3;
GO
 
/******************************************* Select all data */
begin tran;
select k from Test with (serializable) where k between 2 and 3 order by k desc;

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

insert Test (k) values (4)
GO

הרשומה נכנסת ללא בעיה! לא מאמינים? תנסו לבד :-)

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

exec sp_lock @@spid;

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

לפני שנעבור לדיון קצר וסיכום, בואו ננסה להציג תרגיל נוסף, ונבדוק מה אנחנו מקבלים. הפעם נעבוד עם השאילתה שההתנהגות שלה התאימה למצופה (השאילתה האחרונה), ז"א לא נוצרה לנו נעילה מיותרת. בחלק זה אני רוצה לוודא שאכן רמת הבידוד שהגדרנו עובדת כראוי. על מנת לבצע בדיקה שוב נבנה את הטבלה מחדש והפעם נכניס רשומות עם רווח בערך של k.

 *** המאמר עדיין בכתיבה, אני רוצה להוסיף מספר דוגמאות להבהרה של מצבים יותר או פחות מצופים.  ***

 *** עם זה אני מעלה אותו כפי שהוא מכיוון שניתן כבר לעות בו שימוש ולהעזר בו להבנת הנושא ***

 *** קריאה מהנה, בקרו שוב בעוד יומיים שלוש לבדוק את את ההתקדמות ***

.