חקירת deadlocks ב- SQL Server (חלק 2)

אביאל אילוז

MCT, מומחה ב-SQL Server, בעל 15 שנות ניסיון בתחום ומנהל אקדמי של מכללת נאיה

במאמר הראשון בסדרה זו הכרנו את תופעת ה-deadlock בפירוט והמחשנו אותה באמצעות דוגמא פשוטה. הופעתו של אירוע deadlock דורש את התערבותו של צוות הפיתוח או ה-DBA כדי להתחקות אחר מקורותיו ולעשות את הנדרש כדי למנוע את הישנותו. לשם כך מספק לנו SQL Server כלים שונים לתחקור התופעה. במאמר זה נכיר את אחד מן הכלים האלה ונלמד כיצד לנתח נכון את המידע המופק ממנו אודות מקורותיו של deadlock.

עקוב אחרי!

הכלי הראשון שזורה אור על מקורות אירוע ה-deadlock הוא Trace Flags, 'דגלי מעקב' במינוח העברי, שהם כמו מתגי הפעלה שונים ששולטים על התנהגותו של SQL Server בפרמטרים שונים. ישנם דגלי מעקב רבים מאוד ורובם אינם מתועדים בספרות הרשמית. רשימת דגלי המעקב המותעדים ב-Books On Line איננה ארוכה, אך כוללת את השניים שמעניינים אותנו: 1204 ו-1222. שניהם מאלצים את SQL Server לדבר קצת יותר אל תוך הלוג שלו (Error Log) בעת אירוע deadlock ולתת לנו מידע מפורט על מה שקרה.

ראשית, כדי להפעיל דגל מעקב כל שהוא יש להריץ ע"י פקודת DBCC המקבלת שני פרמטרים:

·         מס' הדגל המופעל.

·         הטווח (Scope) שלו; (0) עבור החיבור (session) הנוכחי בלבד, (1-) הפעלה גלובלית.

כמו כן, ישנן עוד 2 פקודות חשובות: אחת לביטול הפעלת הדגל והשניה לבדיקת סטטוס ההפעלה שלו. בקטע הקוד הבא מופעל דגל 1222 באופן גלובלי לניטור deadlock, וכן מודגמת פקודת הביטול (TRACEOFF) והבדיקה (TRACESTATUS).

--turn on trace flag 1222 globally

DBCC TRACEON (1222, -1)

GO

--view all trace flags status

DBCC TRACESTATUS

GO

--turn off trace flag 1222 globally

DBCC TRACEOFF (1222, -1)

GO

אם הרצתם את הקוד הנ"ל כולו, הריצו שוב פקודת ההפעלה לדגל 1222 כדי לנטר את אירועי ה-deadlock.

כדי להפעיל דגלי מעקב עם הפעלתו של SQL Server יש לקבוע זאת בפרמטרי האיתחול של SQL Server ולהוסיף את הפרמטר (-T) ואחריו את מספר הדגל. לקריאה כיצד לבצע זאת ראו ב-Startup Options.

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

בדיקת סטטוס ההפעלה באמצעות TRACESTATUS תראה את הפלט הבא, המעיד שדגל 1222 הופעל באופן גלובלי (הכולל כמובן את רמת ה-session, אף על פי שהיא מסומנת ב-0):

TraceFlag Status Global Session

--------- ------ ------ -------

1222      1      1      0

הפעלת דגל המעקב 1222 מתועדת ב-Error Log של SQL Server בנוסח הבא:

DBCC TRACEON 1222, server process ID (SPID) 51. This is an informational message only; no user action is required.

אנטומיה של אירוע

נחזור כעת לקוד הטרנזקציות שהרצנו במאמר הראשון. נריץ אותו שוב ונראה מהו המידע המופק אל תוך הלוג של SQL Server בעקבות ההפעלה של דגל 1204. כפי שבצענו בפעם הקודמת, נריץ את הקוד של טרנזקציה A בחלון שאילתא אחד ונעבור מייד להריץ את קוד טרנזקציה B בחלון שני:

--Transaction A

USE AdventureWorks2008

GO

BEGIN TRANSACTION

--Statement 1

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT

GO

--Transaction B

USE AdventureWorks2008

GO

BEGIN TRANSACTION

--Statement 1

UPDATE HumanResources.Department

SET Name = Name + ' added text'

WHERE DepartmentID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

COMMIT

GO

כמצופה, בחלון השני בוטלה הטרנזקציה והתהליך כולו נבחר כ-victim עם הודעת השגיאה מס' 1205 המודיעה על deadlock.

נבחן כעת את הפלט ב-Error Log בעקבות הפעלת דגל המעקב 1222. את המידע הזה ניתן לראות בשני אופנים:

1. בחלון הצגת רשומות הלוג של SQL Server ב-SSMS כמו בתמונה הבאה (ניתן למיין בסדר תאריך עולה רק בגרסה 2012):

2. בקובץ הלוג האחרון (Current = ERROROG) עצמו הנמצא בספרייתLOG  של SQL Server (שם הקובץ ומיקום ברירת המחדל:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG

באפשרות זאת, אפשר לראות את המידע במלואו ולהעתיקו למקום אחר. במקרה שלנו מתקבל הפלט הבא על אירוע ה-deadlock כתוצה מהפעלת דגל המעקב 1222 (בצירוף עריכה קלה והדגשות שונות לצורך תצוגה ברורה):

deadlock-list

 deadlock victim=process89bc78

  process-list

   process id=process89bc78 taskpriority=0 logused=992 waitresource=KEY: 41:72057594059096064 (88d989970b21) waittime=1295 ownerId=1461972 transactionname=user_transaction lasttranstarted=2012-02-01T14:16:17.460 XDES=0x6e68280 lockMode=S schedulerid=1 kpid=9792 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-02-01T14:16:17.330 lastbatchcompleted=2012-02-01T14:16:17.330 clientapp=Microsoft SQL Server Management Studio - Query hostname=COMP hostpid=8648 loginname=COMP\User isolationlevel=read committed (2) xactid=1461972 currentdb=41 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

    executionStack

     frame procname=adhoc line=12 stmtstart=38 sqlhandle=0x0200000062bf1a367c223b2515295d4ab887a612948fe8f8

SELECT * FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID]=@1 AND [SalesOrderDetailID]=@2    

     frame procname=adhoc line=12 stmtstart=390 stmtend=578 sqlhandle=0x02000000081dbe017f1c3ce264b5c71a5af5ba8fc624e486

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1    

    inputbuf

BEGIN TRANSACTION

--Statement 1

UPDATE HumanResources.Department

SET Name = Name + ' added text'

WHERE DepartmentID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

COMMIT

   process id=process5d89558 taskpriority=0 logused=3292 waitresource=KEY: 41:72057594044612608 (e1784bd73cba) waittime=4120 ownerId=1461715 transactionname=user_transaction lasttranstarted=2012-02-01T14:16:14.063 XDES=0x5797b30 lockMode=S schedulerid=2 kpid=6612 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-02-01T14:16:13.963 lastbatchcompleted=2012-02-01T14:16:13.963 clientapp=Microsoft SQL Server Management Studio - Query hostname=COMP hostpid=8648 loginname=COMP\User isolationlevel=read committed (2) xactid=1461715 currentdb=41 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

    executionStack

     frame procname=adhoc line=12 stmtstart=24 sqlhandle=0x02000000f1566f31cd4b8116cb213bf6a38d6f62e123f693

SELECT * FROM [HumanResources].[Department] WHERE [DepartmentID]=@1    

     frame procname=adhoc line=12 stmtstart=438 stmtend=570 sqlhandle=0x0200000083fb582b676b2b8e21b5289e7168a5a5e6b45d03

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1    

    Inputbuf

BEGIN TRANSACTION

--Statement 1

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT

  resource-list

   keylock hobtid=72057594059096064 dbid=41 objectname=AdventureWorks2008.Sales.SalesOrderDetail indexname=PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID id=lock55ddf40 mode=X associatedObjectId=72057594059096064

    owner-list

     owner id=process5d89558 mode=X

    waiter-list

     waiter id=process89bc78 mode=S requestType=wait

   keylock hobtid=72057594044612608 dbid=41 objectname=AdventureWorks2008.HumanResources.Department indexname=PK_Department_DepartmentID id=lockadd2380 mode=X associatedObjectId=72057594044612608

    owner-list

     owner id=process89bc78 mode=X

    waiter-list

     waiter id=process5d89558 mode=S requestType=wait

הפלט מציג נתונים מפורטים על התהליכים המעורבים באירוע ה-deadlock באופן הירארכי כמו שמוצג בטבלה הבאה:

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

·         את המעגליות של הנעילה הגורמת ל-deadlock נוכל לראות בסעיף resource-list המציג את המשאבים הנעולים ביחס לכל אחד משני התהליכים.

·         את מספרי התהליכים המופיעים בסעיף זה נשווה למידע המופיע למעלה יותר בסעיף process-list ונוכל לשייך כל אחד מהם לקוד המקורי שלנו.

לשם הבהירות, נמצה מן הפלט כולו רק את המידע העיקרי שיתן לנו את מבוקשנו ונארגן כעת את הנתונים כך שנראה את מצב המשאב הראשון לעומת מצב המשאב השני כפי שעולה מהסעיף resource-list, כמו שמופיע בטבלה הבאה:

מהטבלה נחשפים בבירור מקורותיו של אירוע ה-deadlock כפי שתיארנו אותם בראשית הדברים:

·         טרנזקציה A, בתהליך process5d89558, לאחר שביצעה UPDATE בטבלה SalesOrderDetails ועל כן נועלת את המפתח שלה (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID) בנעילת X, ממתינה כעת בשאילתת SELECT מהטבלה Department המבקשת נעילת S על המפתח שלה (PK_Department_DepartmentID).

ולעומתה, בדיוק המקרה ההפוך:

·         טרנזקציה B, בתהליך process89bc78, לאחר שביצעה UPDATE בטבלה Department ועל כן נועלת את המפתח שלה (PK_Department_DepartmentID) בנעילת X, ממתינה כעת בשאילתת SELECT מהטבלה SalesOrderDetails המבקשת נעילת S על המפתח שלה (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID).

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

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

סיכום

במאמר הזה ראינו כיצד לנטר מידע על אירועי deadlock באמצעות דגל המעקב (trace flag) מספר 1222. המידע המופק מדגל המעקב מספר 1204 איננו שונה באופן עקרוני, אך הוא פחות מפורט, וישDBA  המעדיפים להשתמש בו. בכל אופן, ניתוח המידע וההתחקות אחר מקורות ה-deadlock איננו פשוט כל כך,  אך הכרת מבנה פלט המידע על האירוע וניתוח מסודר ומובנה שלו יחשוף בפנינו בסופו של דבר את גורמי התופעה.

במאמר הבא בסדרה, נראה כיצד לתחקר אירוע deadlock באמצעות SQL Server Profiler.

נעילה באמצעות Application Lock

שמוליק כהן

DBA  בפרוייקט ממשלתי מטעם נאיה טכנולוגיות.

רובנו מכירים את מנגנון הנעילות של SQL Server. זהו המנגנון המונע קונפליקטים בזמן הגישה לנתונים. כך למשל, כל זמן שמשתמש מעדכן רשומה מסוימת, משתמש אחר לא יוכל לעדכן אותה או למחוק אותה, וברוב המקרים גם לא יוכל לקרוא אותה. מנגנון זה נועל ישויות בבסיס הנתונים:  רשומה,key  באינדקס, page, extent, טבלה ובסיס נתונים. 

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

הקוד יראה כך:

declare @NewCustomer int

set @NewCustomer = 12345678

if not exists (select 1

               from customers

               where ID = 12345678)

begin

      insert customers

      values (@NewCustomer)

end

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

הפתרון במקרה שכזה שונה: במקום לנעול נתונים, ננעל את קטע הקוד האפליקטיבי המדובר, ואז אפשר להריץ אותו רק פעם אחת בו זמנית.  זוהי המשמעות של נעילת application lock, והיא מתבצעת באמצעות פקודת sp_getapplock. בסיום הרצת הקוד יש צורך לשחרר את הנעילה באמצעות sp_releaselock. 

נעילת קטע הקוד

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

declare @LockResults int

exec @LockResults = sp_getapplock  @Resource =  'CodeName', @LockMode =  'Exclusive',  @LockOwner =  'Session',  @LockTimeout =  '3000'

נפרט את הפרמטרים לפרוצדורה:

LockResults@ - משתנה מסוג int, שמקבל את תוצאות הנעילה. אם ערכו 1 או 0 – הנעילה הצליחה. אם ערכו שלילי – הנעילה נכשלה.  העצלנים שבחבורה יכולים פשוט לבדוק אם הערך שהתקבל הוא שלילי ולטפל בכל סוגי השגיאות באותו אופן. המשקיענים יכולים לוודא את הסיבה הספציפית, ואם הסיבה היא time out הערך שיתקבל יהיה -1. במקרה זה, אפשר לנסות ולבצע את הנעילה מספר פעמים נוספות.  (כמובן שכדאי להגביל את מספר הפעמים שמנסים להשיג את הנעילה...) .

Resource@ - משתנה מסוג nvarchar(255) המגדיר את שם קטע הקוד, ועליו להיות בעל שם יחודי.

LockMode@  - משתנה מסוג nvarchar(32) המגדיר סוג הנעילה.  סוגי הנעילה האפשריים הם:  Shared, Update, IntentShared, IntentExclusive, Exclusive. ל- LockMode@ אין ברירת מחדל וחייבים לקבוע ערך עבורו.

LockOwner@ – משתנה מסוג nvarchar(32) שיכול לקבל אחד משני ערכים:  Transaction או Session.  במקרה שהוא מוגדר כ- Transaction, ניתן לקבל את הנעילה רק בתוך טרנזקציה.  ברירת המחדל של הערך היא Transaction.

LockTimout@ – משתנה מסוג nvarchar הקובע את הזמן (טקסט המציין אלפיות שניה) ש- sp_getapplock ימתין לפני שיחזיר timout. ערך ברירת המחדל הוא ערכו של משתנה המערכת LOCK_TIMEOUT@@. אם ערך זה לא אותחל הוא יקבע על -1, שמשמעותו המתנה ללא הגבלת זמן להשגת הנעילה.

שחרור קטע הקוד

לאחר ביצוע הקוד יש לזכור לשחרר אותו, אחרת הוא נשאר נעול כל זמן שה- session אינו מתנתק.  שחרור הנעילה מתבצע באמצעות הפרוצדורה sp_releaselock.  הפרוצדורה מקבלת כפרמטר את שם ה- application lock שאותה צריך לשחרר. 

כך יראה קטע הקוד שהוצג למעלה, לאחר הוספת קוד לנעילה באמצעות applicative lock:

create proc InserNewCustomer

      @CustomerID int

as

begin

      declare @LockResults int

      begin  tran

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Exclusive', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            end

            if not exists (select 1

                           from customers

                           where ID = 12345678)

            begin

                  insert customers

                  values (@NewCustomer)

                  exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            end

      commit

end

ניתן להריץ יותר מפקודת sp_getapplock אחת בפרוצדורה, אך צריך להקפיד להפעיל את sp_realeaselock כמספר פעמים שהריצו את פקודת sp_getapplock. 

שימוש בנעילה אפליקטיבית בריבוי משתמשים.

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

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

במקרה שכזה אנו נתחיל את הפרוצדורה בהפעלת  sp_getapplockכדי לקבל נעילה מסוג shared. בהתקיים התנאי הרלוונטי, נשדרג את הנעילה ל- Exclusive (באמצעות שחרור הנעילה ונעילה מחדש כ- exclusive תחת אותו השם שהוגדר במשתנה Resource@). אם יש על קטע הקוד נעילה מסוג Shared (בשל הרצת אותה הפרוצדורה בו-זמנית ע"י משתלשים אחרים), הבקשה לנעילת ה- Exclusive. 

קוד הפרוצדורה המממש את המנגנון הזה יראה אם כן כך:

create proc InserNewCustomer

      @CustomerID int

as

begin

      declare @LockResults int

      begin  tran

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Shared', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            END

            /*

            write non critical code here

            */

           --Releasing Shared lock and aquiring exclusive app

           --lock for critical code

            exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Exclusive', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            END

            if not exists (select 1

                           from customers

                           where ID = 12345678)

            begin

                  insert customers

                  values (CustomerID)

                  exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            end

      commit

end

כדאי, כמובן, לנסות ולמנוע כישלון של הנעילה על ידי הגדרת timeout ארוך מעט או ע"י מספר נסיונות חוזרים להשיג את הנעילה. לאחר קבלת נעילת ה- Exclusiveבהצלחה , כל נסיון להריץ את הפרוצדורה בו זמנית ע"י משתמשים אחרים יכשל, משום שלא ניתן יהיה לקבל נעילת Shared כל עוד לא שחררנו את נעילת ה- Exclusive. 

מה נראה בפרוצדורת sp_lock

sp_lock מראה את מצב הנעילות הקיימות כרגע במערכת.  בין סוגי הנעילות שניתן לראות באמצעות הפרוצדורה, ניתן לראות גם application lock.  בעמודת ה- Type יופיע הקיצור APP.

נקודות נוספות

1.       לנעילה אפליקטיבית אין מנגנון deadlock, ולכן האחריות עלינו, המפתחים, למנוע אפשרות של deadlock.

2.       ROLLBACK אינו משחרר נעילה אפליקטיבית. במידה שהגענו למצב בו עלינו לבצע ROLLBACK חובה עלינו גם לטפל בשחרור הנעילה.

3.       אם בנעילה קובעים את הערך session עבור הפרמטר LockOwner@, אזי יש להוסיף את הפרמטר הזה גם בפקודת השחרור, משום שברירת המחדל היא הערך Transaction וללא קביעת הערך המתאים בפרמטר זה, השחרור יכשל.

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

שימוש בפרוצדורות המערכת sp_msforeach

עדי כהן

מומחה SQL Server בעל כ-15 שנות ניסיון מגוון בתחום. ר"צ DBA בחברה למסחר פיננסי באינטרנט מטעם נאיה טכנולוגיות.

כמו שכולנו יודעים, יש הרבה דברים שלא מתועדים ע"י מייקרוסופט, אבל קיימים ב SQL Server. אובייקטים לא מתועדים יכולים להשתנות ללא אזהרה בגרסת שרת חדשה, בהתקנה של Service pack או בהתקנה של CU. מבחינת מייקרוסופט מי שמשתמש ב-undocumented object לוקח על עצמו את הסיכון שהקוד שלו יפסיק לעבוד ללא שום אזהרה. זאת סיבה מעולה לא להשתמש בשום אובייקט לא מתועד בקוד אפליקטיבי. 

לעומת זאת בסקריפטים אדמיניסטרטיבים, יש לנו הרבה מקרים שכדאי להשתמש בהם. הסיבה להבדל בגישה היא, שכאשר אפליקציה נופלת בגלל שימוש באוביקט לא מתועד שהשתנה, או שאפילו לא קיים יותר לאחר ביצוע שינוי בשרת (upgrade, התקנת SP וכד'), משתמשי הקצה מושפעים מכך ישירות. תיקון המצב דורש הפעלה של מפתח, או של DBA, או של אנשי QA ולא תמיד ניתן להעלות במהירות גרסה מתוקנת.  כמובן, שעד שיוצאת גרסה מתוקנת העבודה של משתמשי הקצה נפגעת.  לעומת זאת נפילה של סקריפט אדמיניסטרטיבי שלנו, לרוב לא ישפיע על משתמש קצה, הוא לא מצריך עבודה של צוות אחר חוץ מצוות ה-DBA וניתן לבצע שינוי במהירות.

שתי פרוצדורות לא מתועדות שקיימות כבר הרבה שנים  הן sp_msforeachdb ו-sp_msforeachtable.  שתי הפרוצדורות מקבלות כפרמטר מחרוזת טקסט (String) שמכיל קוד SQL, ומריצות אותו על כל בסיסי הנתונים בשרת (במקרה של sp_msforeachdb) או על כל טבלה בבסיס נתונים ספציפי (במקרה של sp_msforeachtable).  מאחר ששתי הפרוצדורות רצות על מספר אובייקטים (או על כל בסיס נתונים או על כל טבלה) הדרך שלנו לטפל באובייקט הנוכחי היא להשתמש בסימן שאלה (?) אשר יחליף בתוך הקוד את שם בסיס הנתונים או הטבלה. 

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

לדוגמא הפקודה:

exec sp_spaceused 'MyTable'

מחזירה פרטים על הטבלה MyTable.

אם אני רוצה להוציא את הפרטים על כל הטבלאות בבסיס הנתונים הפקודה שאני אכתוב תיראה כך:

exec sp_MSforeachtable 'exec sp_spaceused ''?'''

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

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

בדוגמא הבאה אין צורך לעטוף את סימן השאלה בגרשיים, כי פשוט מבצעים select top 1 מכל טבלה:

exec sp_MSforeachtable 'select top 1 * from ?'

סימן השאלה הוא שומר מקום (place holder) לשם הטבלה. מאחר שבמשפט select אין צורך לעטוף את שם הטבלה בגרשיים,  לא עטפתי במקרה זה את שם הטבלה בגרשיים.

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

לדוגמא, אם יש לכם בשרת 20 בסיסי נתונים ופתחתם חלון שאילתא ב-master ואז הרצתם את הפקודה הבאה:

exec sp_MSforeachdb 'exec sp_spaceused'

תקבלו 20 פעמים את אותה תשובה (שמתאימה לmaster).  על מנת שבכל פעם תקבל באמת תשובה לבסיס נתונים אחר, צריך להוסיף לקוד את הפקודה USE המשנה את הקונטקסט למסד הנתונים הרצוי:

exec sp_MSforeachdb 'use ?; exec sp_spaceused'

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

דרך אגב, הפרוצדורה sp_MSforeachdb איננה מוצלחת כל כך ונופלת במקרים רבים.  לכן Aaron Bartrand (אחד הMVP של SQL Server) כתב פרוצדורה חליפית לפרוצדורה של מייקרוספט.  הפרוצדורה שלו אכן יותר מוצלחת, ואני משתמש בה במקום הפרוצדורה של מייקרוסופט. 

ניתן למצוא את הפרוצדורה שהוא כתב ב http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx 

טרנזאקציות אוטונומיות (Autonomous Transactions)

צח פניגשטיין

יועץ SQL בכיר, בעל 10 שנות נסיון בתחום התוכנה, ר"צ DBA בפרוייקט ממשלתי מטעם נאיה טכנולוגיות

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

כל קריאה ל BEGIN TRANSACTION פותחת טרנזאקציה מפורשת, ומעלה את ערכו של TRANCOUNT@@ באחד. כל קריאה ל COMMIT סוגרת את הטרנזאקציה האחרונה שנפתחה, ומחסירה אחד מערכו של TRANCOUNT@@.

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

• קריאה ל COMMIT שמאפסת את TRANCOUNT@@ (כלומר סגירה של הטרנזאקציה הראשונה שפתחנו בקטע הקוד). הטרנזאקציה מסתיימת במלואה, והשינויים הופכים לקבועים.
•  קריאה ל ROLLBACK מסיימת את כל הטרנזאקציות שנפתחו ב-scope. פעולה זו מבטלת את כל השינויים שבוצעו מרגע שנפתחה הטרנזאקציה הראשונה בקטע הקוד, ומאפסת את TRANCOUNT@@.

מכך עולה שאם ביצענו COMMIT לטרנזאקציה מקוננת, ולאחר מכן בוצע ROLLBACK, וזאת עוד לפני שהטרנזאקציה העליונה ביותר בקטע הקוד עברה COMMIT – אזי השינויים שבוצעו בתוך הטרנזאקציה המקוננת יתבטלו.

לדוגמה:

SELECT @@trancount [Start @@trancount]

-- @@trancount = 0, no open transactions

-- Open Main Transaction

BEGIN TRANSACTION

SELECT @@trancount [Open Main Transaction @@trancount]

-- @@trancount = 1, one open transaction

INSERT TestTable VALUES  (N'Insert from Main Transaction')

      -- Open Nested Transaction

      BEGIN TRANSACTION

      SELECT @@trancount [Open Nested Transaction @@trancount]

      -- @@trancount = 2, Main Transaction & Nested Transaction are open

      INSERT TestTable VALUES  (N'Insert from Nested Transaction')

      -- COMMIT Nested Transaction

      COMMIT

      SELECT @@trancount [COMMIT Nested Transaction @@trancount]

      -- @@trancount = 1, Main Transaction is open

-- ROLLBACK Main Transaction *AND* Nested Transaction

ROLLBACK   

SELECT @@trancount [ROLLBACK @@trancount]

-- @@trancount = 0

-- TestTable is empty, although the Nested Transaction was commited

SELECT * FROM TestTable 

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

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

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

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

כדי לפתור את הבעיה הזו SQL Server 2008 מציג מאפיין חדש ל Linked Server שמאפשר להריץ קוד, מחוץ לטרנזאקציה שממנו הוא נקרא.
Linked Server הוא מאפיין שמאפשר לנו לגשת לשרת מרוחק כדי לתשאל מידע או להריץ פרוצדורות. עד לגרסת 2008 טרנזאקציות שבוצעו במהלך גישה לשרת מרוחק תמיד נוהלו בעזרת רכיב בשם DTC (Distributed Transaction Coordinator).
DTC  הוא שירות של מערכת ההפעלה שמאפשר לנהל טרנזאקציות בין משאבים שונים. לדוגמה: ניתן להשתמש ב DTC  בתוכנת .Net כדי להבטיח שפעולה אטומית אחת תעדכן קובץ אקסל, וגם טבלה במסד נתונים. ניתן להשתמש ב DTC גם כדי לעדכן מידע בשני שרתיSQL  שונים.

בגרסת 2008 נוסף מאפיין חדש להגדרת ה Linked Server בשם "remote proc transaction promotion", שמאפשר לנו לקבוע האם קריאה לפרוצדורה בשרת המרוחק תשתמש ב  DTCאו לא. המשמעות של קריאה לפרוצדורה מרוחקת, ללא שימוש ב DTC, היא שהפרוצדורה מתנהלת מחוץ לטרנזאקציה שקראה לה, ולכן יכולה לנהל טרנזאקציה "פרטית" באופן אוטונומי.

אז איך נהפוך טרנזאקציה מקוננת לטרנזאקציה אוטונומית?

1.       נגדיר Loopback Linked Server. Loopback Linked Server הוא למעשה הפניה של השרת לעצמו דרך מנגנון ה Linked Server.

2.       נגדיר את שרת ה Loopback שלא ישתמש במנגנון ה DTC בזמן קריאה לפרוצדורות מרוחקות.

3.       נכתוב פרוצדורה שתכיל את הלוגיקה של הטרנזאקציה המקוננת.

4.       נקרא לפרוצדורה שכתבנו דרך הגדרת ה Loopback.

באופן מעשי:

הגדרת Loopback Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'LoopBack',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME

שימוש במאפיין החדש:

EXEC master.dbo.sp_serveroption @server=N'LoopBack', @optname=N'remote proc transaction promotion', @optvalue=N'false'

לאחר הרצת שני השלבים הללו מסך מאפייני שרת LoopBack אמור להיראות כך:

שימו לב שערך שני המאפיינים RPC ו RPC Out הוא True, וזאת כדי שלשרת ה Linked Server תהייה גישה לפרוצדורות. כמו כן יש לוודא שהגדרתם נכון את הרשאות האבטחה.

נכתוב פרוצדורה שמיישמת את הלוגיקה של הטרנזאקציה המקוננת:

CREATE PROCEDURE [dbo].[InsertData]

(

      @Msg NVARCHAR(255)

)

AS

BEGIN

      SET NOCOUNT ON;

      DECLARE @ ErrorMsg NVARCHAR(255) = '@@trancount = ' +  

                                          CONVERT(NVARCHAR(255),@@trancount)

RAISERROR (@ErrorMsg,1,1)

      INSERT TestTable VALUES (@Msg)

END

קטע הקוד שמחולל שגיאה (RAISERROR) נועד רק כדי לבחון את מצב הטרנזאקציה בעזרת SQL Server Profiler. כמובן שאין בו צורך במימוש אמיתי. שימו לב שמאחר שהפרוצדורה מבצעת פעולה פשוטה אחת, אין צורך לנהל בה טרנזאקציה מפורשת, מפני שפעולת ה INSERT מבוצעת, כמו כל פקודת DML  בודדת, בטרנזאקציה לא מפורשת (IMPLICIT TRANSACTION, ללא שימוש ב-BEGIN TRANSACTION).

נחבר את כל החלקים:

SELECT @@trancount [Start @@trancount]

-- @@trancount = 0, no open transactions

-- Open Main Transaction

BEGIN TRANSACTION

SELECT @@trancount [Open Main Transaction @@trancount]

-- @@trancount = 1, one open transaction

EXEC [dbo].[InsertData] N'Insert from Main Transaction'

EXEC [LoopBack].[Tests].[dbo].[InsertData] N'Insert from no-DTC RPC'

-- ROLLBACK Main Transaction

ROLLBACK   

SELECT @@trancount [ROLLBACK @@trancount]

-- @@trancount = 0

-- TestTable is not empty, although the Main Transaction was rolled-back

SELECT * FROM [dbo].[TestTable]

מעקב בעזרת SQL Server Profiler:

שימו לב שהקריאה לשתי הפרוצדורות בוצעה תחת טרנזאקציה אחת. אך במקרה של הרצת הפרוצדורה דרך ה LoopBack Linked Server, ניתן לראות שערכו של TRANCOUNT@@ הוא 0, למרות שהפרוצדורה נקראה מתוך טרנזאקציה פעילה. כלומר הפרוצדורה מתנהלת בצורה טרנזאקטיבית אוטונומית, שאינה חלק מהטרנזאקציה העליונה בקטע הקוד.

כמה נקודות לגבי Linked Server

יש דברים בעולמנו שרצוי להתייחס אליהם על-פי האמרה "כבדהו וחשדהו". לצערי, אני מתייחס אל כל הגדרת Linked Server בצורה "חשדהו וחשדהו". מניסיוני, התכונה הזו של SQL Server לא מתנהלת בצורה חלקה תחת עומס, ומתחילה לגמגם ברגעים הכי בעייתיים ולא צפויים. אני משער שהדבר נובע משני מאפיינים עיקריים ש Linked Server משתמש בהם לצורך פעילותו:

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

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

מהסיבות הללו אני משתדל להשתמש ב Linked Server רק לצרכים אדמיניסטרטיביים, ולעולם לא כחלק מתהליכים קריטיים. אם בכל זאת אני נאלץ להשתמש ב Linked Server אני משתמש ב-SQL Authentication ולא ב- Windows Authentication.

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

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

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

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

סיכום

SQL Server 2008 מכיל מאפיין חדש בהגדרת Linked Server שנועד לתמוך בטרנזאקציות אוטונומיות. בראש ובראשונה מאפיין זה פותח כדי לאפשר המרת אפליקציות מבוססות Oracle לסביבת SQL Server. כמו רבים מהמאפיינים שפותחו מסיבה זו, נראה שגם מאפיין זה אינו משתלב בצורה טבעית בארגז הכלים של ה-DBA של SQL Server. 
לטעמי, מימוש של טרנזאקציות אוטונומיות בצורה זו הוא מעט מסורבל. בנוסף, יש סיכוי סביר שפתרון זה לא יתנהל בצורה חלקה תחת עומס. מהסיבות הללו אני ממליץ למי שצריך לנהל טרנזאקציות אוטונומיות להשתמש באחת מהדרכים החלופיות שהצגתי בסוף הדברים.