none
נעילות בבסיס הנתונים RRS feed

  • שאלה

  • שלום רב,

    השאלה שלי היא כזאת יש לי פונקציה שמחזירה  משתנה מסוג טבלה  (table value function)

    לפונקציה  הזאת יש כל הזמן קריאות -- הפונקציה עושה חישוב כלשהוא ומחזירה משתנה מסוג טבלה.

    הבעיה :

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

    ( כמבון שתישאלתי את טבלאות המערכת  המתאימות כדי לראות את הנעילות )

     הפונקציה בכלל לא ניגשת לשום טבלה כלומר אין פעולת   IO  (אין update,select או insert ) רק מחשבת כמה נתונים ומחזירה תשובה.

    הייתכן הדבר :) ?

    תודה רבה

    שרון

    שרון

    יום רביעי 17 אפריל 2013 11:57

תשובות

  • אהלן שרון

    אני מנחש שאתה מערבב 2 מושגים:
    * נעילה של אלמנט במסד הנתונים
    * כניסה למצב המתנה

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

    דוגמה פשוטה להבנת העניין:

    תכינו פונקציית CLR שלא עושה כלום במסד הנתונים אלא רק מחזירה ערך קבוע של המספר 1

    בפונקציה תכניסו פקודה (C#):

    Thread.Sleep(2000);

    עתה תריץ את הפונקציה על ידי SELECT ואפילו תוסיף HINT לא לבצע נעילה. נסה לרוץ בטבלה עם 10 רשומות ותקבל שהשאילצתה לוקחת בערך 20 שניות לרוץ מכיוון שיש המתנה של 2 שניות בכל רשומה (בכל הפעלה של הפונקציה). אם תנטר את הנושא של כניסה למצב המתנה תגלה שהשאילתה נכנסת כל פעם למצב המתנה למרת שאין שום נעילות כמובן.

    * אני לא יודע אם זה המקרה שלך ולא נכנסתי לנושא שלך לעומק. אבל בניחוש מהיר אני יכול להעריך שיש בילבול בין 2 המושגים ואולי הבעיה קשורה להמתנה ולא לנעילה בהכרח. אתה יכול לקרוא על כניסה למצב המתנה כאן:
    http://ariely.info/Blog/tabid/83/EntryId/109/SQL-Server-Wait-Stats-and-Types.aspx
    יש שם קישורים בסיום שיכולים לעזור לך לנטר ולהבין מה קורה וכמה שאילתות.

    בנוסף מומלץ לעבור על ההרצאה של Noam Brezis שהוא העביר בפגישת קבוצת ה SQL לפני חודש והיא דנה בנושא זה


    signature

    • נערך על-ידי pituachMVP, Editor יום שישי 19 אפריל 2013 10:47
    • סומן כתשובה על-ידי sharonof יום ראשון 21 אפריל 2013 11:16
    יום שישי 19 אפריל 2013 10:28
    מנחה דיון
  • בהחלט כן :-)

    עבור על הבלוג ששמתי לך אם אתה באמת רוצה להשקיע הרבה יותר, ותוכל לנטר בדיוק מה קורה :-)

    wait for delay הוא בפירוש מקור להמתנה דרך אגב.


    signature

    • סומן כתשובה על-ידי sharonof יום ראשון 21 אפריל 2013 15:00
    יום ראשון 21 אפריל 2013 12:09
    מנחה דיון

כל התגובות

  • הי שרון,

    כמה שאלות:

    1. איך אתה יודע שהפונקציה היא זו שגורמת לנעילות? איך אתה בודק את הנעילות?

    2. איזה סוגי נעילות אתה רואה? יש נעילות שמתבצעות גם כשאתה לא מריץ שום פקודת DML...

    3. האם אתה יכול לפרסם כאן את הפונקציה עצמה?

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום רביעי 17 אפריל 2013 12:14
    מנחה דיון
  •  היי גיא ,

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

    use master
    select  sp.loginame,db.name [Database], sp.spid SPID, sp.blocked [Blocking SPID], sql.text [Event Info], sql2.text [Blocking Event Info],
    sp.cpu [CPU Time], sp2.cpu [Blocking CPU Time], sp.physical_io [Disk IO], sp2.physical_io [Blocking Disk IO]
    from sys.sysprocesses sp
    join sys.sysdatabases db
    on sp.dbid = db.dbid
    outer apply sys.dm_exec_sql_text(sp.sql_handle) sql
    outer apply
    (
    select spi.spid, spi.sql_handle, spi.physical_io, spi.cpu
    from sys.sysprocesses spi
    where spi.spid = sp.blocked
    ) sp2
    outer apply sys.dm_exec_sql_text(sp2.sql_handle) sql2  --  where sp.loginame like '%WebSiteTele%' 
    where sp.blocked > 0

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

    ALTER FUNCTION  aaa
    (
    aaa bigint,
    bbb datetime,
    ccc bigint, 
    ddd datetime,
    eee int,
    fff int
    , zzz int
    ) returns @values table (STATUS int,COLOR varchar(10),CLASS varchar(20))
    as
    begin
    declare @status int;
    select bbb = isnull(bbb,'1899-01-01');
    if fff=0
    	begin
    		set @status=0;
    		if bbb < dateadd(hour,zzz / 60 - 24,getutcdate())
    			set @status=-1;
    		else
    			if not (ccc in (32,33))
    				if ddd>dateadd(minute, zzz - 5,getutcdate())
    					set @status=9;
    	end;
    else
    	begin
    		if eee>0
    			set @status=1;
    		else
    			set @status=2;
    		-- check connection
    		if bbb < dateadd(hour,zzz / 60 - 2,getutcdate())
    			set @status=-1;
    		else
    			if not (ccc in (32,33) and fff>0)
    				if ddd>dateadd(minute, zzz - 5, getutcdate())
    					set @status=9;
    	end;
    	declare @color varchar(10),@class varchar(20);
    	select @color = 
    		case @status 
    			when 1 then '0xCCF1FD' 
    			when 2 then '0x00FF00' 
    			when 9 then '0xFF0000'
    			when -1 then '0xFFFF00'
    			else '0xC0C0C0'
    		end,@class = 
    		case @status 
    			when 1 then 'a'
    			when 2 then 'b'
    			when 9 then 'c'
    			when -1 then 'd'
    			else 'e'
    		end;
    	insert into @values
    	select @status,@color,@class;
    	return;
    end;
    תודה שרון
    יום רביעי 17 אפריל 2013 13:26
  • הי שרון,

    תודה על האינפורמציה!

    עדיין לא ברור לי איך אתה יודע שהפונקציה היא זו שגורמת לנעילות ואיזה סוגי נעילות אתה רואה...

    אתה יכול לפרסם כאן את תוצאות השאילתה שלך?

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 18 אפריל 2013 06:15
    מנחה דיון
  • אהלן שרון

    אני מנחש שאתה מערבב 2 מושגים:
    * נעילה של אלמנט במסד הנתונים
    * כניסה למצב המתנה

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

    דוגמה פשוטה להבנת העניין:

    תכינו פונקציית CLR שלא עושה כלום במסד הנתונים אלא רק מחזירה ערך קבוע של המספר 1

    בפונקציה תכניסו פקודה (C#):

    Thread.Sleep(2000);

    עתה תריץ את הפונקציה על ידי SELECT ואפילו תוסיף HINT לא לבצע נעילה. נסה לרוץ בטבלה עם 10 רשומות ותקבל שהשאילצתה לוקחת בערך 20 שניות לרוץ מכיוון שיש המתנה של 2 שניות בכל רשומה (בכל הפעלה של הפונקציה). אם תנטר את הנושא של כניסה למצב המתנה תגלה שהשאילתה נכנסת כל פעם למצב המתנה למרת שאין שום נעילות כמובן.

    * אני לא יודע אם זה המקרה שלך ולא נכנסתי לנושא שלך לעומק. אבל בניחוש מהיר אני יכול להעריך שיש בילבול בין 2 המושגים ואולי הבעיה קשורה להמתנה ולא לנעילה בהכרח. אתה יכול לקרוא על כניסה למצב המתנה כאן:
    http://ariely.info/Blog/tabid/83/EntryId/109/SQL-Server-Wait-Stats-and-Types.aspx
    יש שם קישורים בסיום שיכולים לעזור לך לנטר ולהבין מה קורה וכמה שאילתות.

    בנוסף מומלץ לעבור על ההרצאה של Noam Brezis שהוא העביר בפגישת קבוצת ה SQL לפני חודש והיא דנה בנושא זה


    signature

    • נערך על-ידי pituachMVP, Editor יום שישי 19 אפריל 2013 10:47
    • סומן כתשובה על-ידי sharonof יום ראשון 21 אפריל 2013 11:16
    יום שישי 19 אפריל 2013 10:28
    מנחה דיון
  • היי פיתוח,

    תודה שוב  :) על התשובה העניינית  , במערכת אצלי אני משתשמש מפעם לפעם בעיקר בלולאות    ב wait for delay    

    לצורך פינוי של משאבי מערכת  האם יכול להיות שזאת  אחת מהסיבות שאני מפרש כנעילה מצב של המתנה ?

    תודה שרון

    יום ראשון 21 אפריל 2013 11:17
  • בהחלט כן :-)

    עבור על הבלוג ששמתי לך אם אתה באמת רוצה להשקיע הרבה יותר, ותוכל לנטר בדיוק מה קורה :-)

    wait for delay הוא בפירוש מקור להמתנה דרך אגב.


    signature

    • סומן כתשובה על-ידי sharonof יום ראשון 21 אפריל 2013 15:00
    יום ראשון 21 אפריל 2013 12:09
    מנחה דיון