none
Deadlock on two updates, on the primary key, both sessions with lockmode='U' only when instead of update trigger exists RRS feed

  • שאלה

  • Hi,

    We are in read committed in Sql Server 2008, and we have concurrent UPDATE's on tables of subsequent rows, which update single records by the primary key column, and we get deadlock on the primary key, while both of the sessions are in lockmode='U'. 

    Now we have an old non-efficient audit mechanism, which can not be replaced, for now and is based on instead of update triggers, which replaces the original updates with full record columns update and does additional inserts to audit tables.

    I saw that when I disable these triggers the deadlock does not happen.

    How can it be explained ? How can two sessions start both of them with lockmode='U' ?

    Can it be a bug ? Is there a workaround for it ?

    I pasted below the deadlock graph from the ERRORLOG after enabling trace 1222.

    Thanks,

    Eyal Sorek

    2012-05-06 15:30:20.73 spid7s      Deadlock encountered .... Printing deadlock information
    2012-05-06 15:30:20.73 spid7s      Wait-for graph
    2012-05-06 15:30:20.74 spid7s      
    2012-05-06 15:30:20.76 spid7s      Node:1
    2012-05-06 15:30:20.76 spid7s      KEY: 7:72057594061062144 (f50041acc16e) CleanCnt:5 Mode:U Flags: 0x1
    2012-05-06 15:30:20.76 spid7s       Grant List 0:
    2012-05-06 15:30:20.78 spid7s         Owner:0x0000000080140280 Mode: U        Flg:0x40 Ref:0 Life:00000002 SPID:94 ECID:0 XactLockInfo: 0x00000000807979B0
    2012-05-06 15:30:20.78 spid7s         SPID: 94 ECID: 0 Statement Type: UPDATE Line #: 20
    2012-05-06 15:30:20.78 spid7s         Input Buf: Language Event: (@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 int,@P5 int,@P6 varchar(8000),@P7 int,@P8 varchar(8000),@P9 datetime,@P10 varchar(8000),@P11 varchar(8000),@P12 int)UPDATE RUN SET RN_STATUS=@P1  , RN_RUN_NAME=@P2  , RN_TESTER_NAME=@P3  , RN_TEST
    2012-05-06 15:30:20.78 spid7s      Requested by: 
    2012-05-06 15:30:20.78 spid7s        ResType:LockOwner Stype:'OR'Xdes:0x00000000B1EF5970 Mode: U SPID:93 BatchID:0 ECID:0 TaskProxy:(0x00000000A60F6538) Value:0x818fb780 Cost:(0/0)
    2012-05-06 15:30:20.78 spid7s      
    2012-05-06 15:30:20.78 spid7s      Node:2
    2012-05-06 15:30:20.78 spid7s      KEY: 7:72057594061062144 (5200a4333bbc) CleanCnt:2 Mode:U Flags: 0x1
    2012-05-06 15:30:20.78 spid7s       Grant List 2:
    2012-05-06 15:30:20.78 spid7s         Owner:0x00000000818FD300 Mode: U        Flg:0x40 Ref:0 Life:02000000 SPID:93 ECID:0 XactLockInfo: 0x00000000B1EF59B0
    2012-05-06 15:30:20.78 spid7s         SPID: 93 ECID: 0 Statement Type: UPDATE Line #: 20
    2012-05-06 15:30:20.78 spid7s         Input Buf: Language Event: (@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 int,@P5 int,@P6 varchar(8000),@P7 int,@P8 varchar(8000),@P9 datetime,@P10 varchar(8000),@P11 varchar(8000),@P12 int)UPDATE RUN SET RN_STATUS=@P1  , RN_RUN_NAME=@P2  , RN_TESTER_NAME=@P3  , RN_TEST
    2012-05-06 15:30:20.78 spid7s      Requested by: 
    2012-05-06 15:30:20.78 spid7s        ResType:LockOwner Stype:'OR'Xdes:0x0000000080797970 Mode: U SPID:94 BatchID:0 ECID:0 TaskProxy:(0x000000008E896538) Value:0x86107340 Cost:(0/0)
    2012-05-06 15:30:20.78 spid7s      
    2012-05-06 15:30:20.78 spid7s      Victim Resource Owner:
    2012-05-06 15:30:20.78 spid7s       ResType:LockOwner Stype:'OR'Xdes:0x00000000B1EF5970 Mode: U SPID:93 BatchID:0 ECID:0 TaskProxy:(0x00000000A60F6538) Value:0x818fb780 Cost:(0/0)
    2012-05-06 15:30:20.78 spid18s     deadlock-list
    2012-05-06 15:30:20.78 spid18s      deadlock victim=processa7f22748
    2012-05-06 15:30:20.78 spid18s       process-list
    2012-05-06 15:30:20.78 spid18s        process id=processa7f22748 taskpriority=0 logused=0 waitresource=KEY: 7:72057594061062144 (f50041acc16e) waittime=3206 ownerId=7572656 transactionname=UPDATE lasttranstarted=2012-05-06T15:30:17.580 XDES=0xb1ef5970 lockMode=U schedulerid=3 kpid=3516 status=suspended spid=93 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-05-06T15:30:17.580 lastbatchcompleted=2012-05-06T15:30:17.573 hostname=127.0.0.1 hostpid=0 loginname=td isolationlevel=read committed (2) xactid=7572656 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
    2012-05-06 15:30:20.78 spid18s         executionStack
    2012-05-06 15:30:20.78 spid18s          frame procname=anna_proj1_db.td.AUTR81515_RUN line=20 stmtstart=1440 stmtend=4840 sqlhandle=0x0300070057d37e568ab8e40045a000000000000000000000
    2012-05-06 15:30:20.79 spid18s     update RUN set

    /נחתך על ידי עמי לוין - הכיל תוי בקרה שגרמו לעמוד לתקלות - נא לצרף כקובץ ולא בהדבקה\

    • נערך על-ידי Eyal Sorek יום שלישי 08 מאי 2012 07:36
    • נערך על-ידי Ami Levin יום שלישי 08 מאי 2012 11:50 גרם לתקלה בהצגת העמוד
    יום שני 07 מאי 2012 20:33

תשובות

  • הי,
    לגבי האנגלית, כן הבנתי מיד אחרי ששאלתי את השאלה, עפ"י התגובות. למדתי להבא...
    לגבי השאלה עצמה, לא היה ממש פתרון. נראה מקרה מאוד מוזר ולא ברור. לא הצלחנו למצוא לזה הסבר מניח את הדעת.
    אנחנו מנסים בינתיים לייצר מעקף פונקציונלי באפליקציה ונקווה שנצליח. בינתיים אין ממש פתרון אמיתי ברמת ה - DB
    תודה,
    אייל
    • סומן כתשובה על-ידי Eran Sharvit יום רביעי 30 מאי 2012 16:09
    יום שני 21 מאי 2012 11:11

כל התגובות

  • * הערה: כתבתי את התשובה אתמול בלילה (היה מאוחר וקיבלתי מייל אז דחיתי את השליחה). אני רואה שערכת את ההודעה כרגע אבל אני שם לב לשינוי. לכן התגובה הזו היא לפי הלוג שהיה כאן אתמול וההודעה המקורית

    בעקרון אני לא מזהה את הבעיה (BUG) והנתונים שאתה מביא דווקא מצביעים על כך ההתנהגות שיש לך deadlock סבירה. אני מקווה שאני מבין אותך נכון, ואולי הנקודות הבאות יעזרו להבהיר כמה דברים

    1. אני אתחיל מכך שלפי מה שאני מבין אתה מציג את lockmode='U' כאילו שזה סיבה לכך שלא יהיו deadlock כי אין נעילות אבל למעשה מוד עדכון לא מונע נעילות אלא אולי מוריד את זמן הנעילה בהתאם לסוג שלה. בזמן עדכון יש פעולה של קריאה (בזמן זה ניתן להסתפק בנעילת shared-S וזה מה שעושה מוד עדכון), אבל עדיין בזמן העדכון עצמו תהיה נעילת exclusive-X. זה המשמעות של מוד עדכון. ולכן בהחלט יכולים להיות deadlock עם יש 2 פעולות במקביל.

    תוכל לקרוא על כך יותר לעומק כאן
    http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx

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

    האם אתה יכול לצרף את השאילתות המלאות ואת הטריגרים שפועלים?

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

    אם הבנתי את המצב טוב אז זה נראה דומה לדרך מהספרים של יצירת deadlocks, כפי שנראה במדריך הבא:
    http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/

    אם נשווה למדריך אז ייתכן שמה שקורה אצלך זה ששלב 1 נעשה על ידי השאילתה הראשונה ושלב 2 על ידי השאילתה השנייה, שלב 3 על ידי הטריגר של השאילתה הראשונה ולכן נוצר deadlocks

    ז"א שאילתה 1 פועלת ומשנה דטא -> שאילתה 2 מופעלת וקוראת את הדטא ששונה על ידי שאילתה 1 -> טריגר של שאילתה 1 מופעל ומשנה את הדטא ולכן שאילתה 2 שעדיין רצה עובדת על נתונים לא נכונים והטריגר שלה שאולי מחייב נעילה לא יכול לעבוד כי הטריגר של השאילתה הראשונה היה צריך לנעול...

    * יכול להיות שדווקא רמת בידוד גבוהה יותר תתאים יותר כי היא תגרום לכך ששאילתה 2 תמתין לסיום שאילתה 1 ולכן לא יהיה סתירה של הטריגרים שמובילה ל deadlocks ?

    ** נסה לתת עוד אינפורמציה אולי על השאילתות והטריגרים שאה מריץ


    signature

    יום שלישי 08 מאי 2012 08:59
    מנחה דיון
  • הי,

    קודם כל תודה על התגובה המהירה והמפורטת :-).

    היו לי כל מיני מקרים של deadlocks, שפתרתי בכל מיני צורות, אבל הסיבה שהפעם המקרה הזה מוזר לי הוא שמדובר ב – 2 עדכונים דומים לרשומות בודדות ושונות (כל פעם רשומה אחרת, שבמקרה הזה היו כנראה עוקבות), עפ"י PK שדה , ולא התרחש Lock Escalation, כיוון שרואים ב – Deadlock Graph בפירוש keylock כך שמדובר על row locks שונים.

    אז מדוע מתבצע פה deadlock ?

    בנוסף מצויין בספרות לגבי lockmode=’U’  ב - http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx (צירפתי למטה את הפיסקה) שרק טרנזקציה אחת יכולה לקבל lockmode=’U’ ושמצב זה הוא לא compatible  עם עוד  U רק עם עוד shared locks. אזי אם זה על אותה רשומה רק session אחד יכול לקבל U, ואם זה על רשומות שונות ,ללא אסקלציה, למה נכנסים פה למצב של deadlock?

    אני טועה באבחנה ? אם כן, מה אני מפספס ואיך אני יכול לעקוף את הבעיה ?

     לגבי פקודת העדכון, הפקודה היא:

     UPDATE RUN SET RN_STATUS=@P1  , RN_RUN_NAME=@P2  , RN_TESTER_NAME=@P3  , RN_TEST_ID=@P4  , RN_TESTCYCL_ID=@P5  , RN_SUBTYPE_ID=@P6  , RN_CYCLE_ID=@P7  , RN_DRAFT=@P8  , RN_EXECUTION_DATE=@P9  , RN_EXECUTION_TIME=@P10 , RN_RUN_VER_STAMP=RN_RUN_VER_STAMP+1, RN_VTS=@P11  where RN_RUN_ID=@P12

     אשר מוחלפת ע"י ה – instead of trigger לעדכון גורף של כל העמודות ברשומה ואח"כ ביצוע AUDIT  של השינויים ברשומה לטבלאות אחרות:

     update RUN set RUN.RN_SUBTYPE_ID=A.RN_SUBTYPE_ID,RUN.RN_DURATION=A.RN_DURATION,RUN.RN_BPTA_CHANGE_DETECTED=A.RN_BPTA_CHANGE_DETECTED,RUN.RN_ASSIGN_RCYC=A.RN_ASSIGN_RCYC,RUN.RN_TEST_ID=A.RN_TEST_ID,RUN.RN_RUN_VER_STAMP=A.RN_RUN_VER_STAMP,RUN.RN_OS_SP=A.RN_OS_SP,RUN.RN_EXECUTION_TIME=A.RN_EXECUTION_TIME,RUN.RN_STATE=A.RN_STATE,RUN.RN_ITERS_PARAMS_VALUES=A.RN_ITERS_PARAMS_VALUES,RUN.RN_VC_LOKEDBY=A.RN_VC_LOKEDBY,RUN.RN_OS_CONFIG=A.RN_OS_CONFIG,RUN.RN_TEST_INSTANCE=A.RN_TEST_INSTANCE,RUN.RN_VC_VERSION_NUMBER=A.RN_VC_VERSION_NUMBER,RUN.RN_BPTA_CHANGE_AWARENESS=A.RN_BPTA_CHANGE_AWARENESS,RUN.RN_TESTCYCL_ID=A.RN_TESTCYCL_ID,RUN.RN_PINNED_BASELINE=A.RN_PINNED_BASELINE,RUN.RN_OS_NAME=A.RN_OS_NAME,RUN.RN_RUN_ID=A.RN_RUN_ID,RUN.RN_TEST_CONFIG_ID=A.RN_TEST_CONFIG_ID,RUN.RN_ATTACHMENT=A.RN_ATTACHMENT,RUN.RN_HOST=A.RN_HOST,RUN.RN_COMMENTS=A.RN_COMMENTS,RUN.RN_OS_BUILD=A.RN_OS_BUILD,RUN.RN_DRAFT=A.RN_DRAFT,RUN.RN_EXECUTION_DATE=A.RN_EXECUTION_DATE,RUN.RN_TEST_VERSION=A.RN_TEST_VERSION,RUN.RN_TESTER_NAME=A.RN_TESTER_NAME,RUN    

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

    תודה,

    אייל שורק

    Update Locks

    Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

    To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.


    Eyal Sorek

    יום שלישי 08 מאי 2012 11:52
  • הי איל

    על טבלת ה- Audit  מוגדרים אינדקסים ו-או Foreign keys?

    במידה וכן תוריד אותם לצורך הבדיקה מראה לי שהבעיה היא ב- deadlock על עדכון האינדקסים כתוצאה מפעולת ה- Update.

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

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

    בהצלחה.


    אסף שלם

    יום שני 14 מאי 2012 22:42
  • הי אסף,

    תודה על התשובה, וכן מוגדרים 2 אינדקסים נוספים על טבלת ה - Audit מעבר ל - PK.

    אבל ה - deadlock, כאשר הוא קורה הוא על ה - PK של טבלת הטריגר RUN ולא קשור לטבלת AUDIT_LOG.

    בנוסף גם ניסינו כל מיני משחקים בצד, על מנת לעשות אלימינציה של הבעיה, וכאשר הפכנו את ה - instead of update triggers ל - after update triggers, אשר מייתר את הצורך של החלפת ה - update המקורי על טבלת הטריגר (RUN), והשארת רק הקוד של ה - audit אז הבעיה לא קרתה.

    כך ששתי הנקודות הללו גורמים לי לחשוב שישנה כאן בעייתיות מסויימת ספציפית להתנהגות של instead of update triggers, אשר מתערבים באופן מסויים בעייתי ב - flow של הטרנזקציה וגורמים לבעיה.

    התחושה שלי, ויכול להיות שאני טועה, שיש פה איזה BUG (אשמח לרעיונות אחרים...), כיוון שגם קורה כאן, עפ"י הבנתי, משהו שלא אמור לקרות עפ"י הספרות, כפי שפירטתי למעלה (2 'lockmode='U במקביל) ובנוסף זה לא קורה ב - after update triggers, כך שלתחושתי, זה ממקד את הבעיה בקונטקסט הספציפי של ה - instead of update triggers.

    תודה,

    אייל שורק

    יום חמישי 17 מאי 2012 11:17
  • הי אייל,

    נשמח להתעדכן בסטטוס של השאלה.

    ואגב להבא, אנא כתוב את השאלה בעברית. לשאלות באנגלית אנא פנה לפורומים של MSDN באנגלית.

    תודה,

    צוות הפורומים.


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

    יום שני 21 מאי 2012 08:15
  • הי,
    לגבי האנגלית, כן הבנתי מיד אחרי ששאלתי את השאלה, עפ"י התגובות. למדתי להבא...
    לגבי השאלה עצמה, לא היה ממש פתרון. נראה מקרה מאוד מוזר ולא ברור. לא הצלחנו למצוא לזה הסבר מניח את הדעת.
    אנחנו מנסים בינתיים לייצר מעקף פונקציונלי באפליקציה ונקווה שנצליח. בינתיים אין ממש פתרון אמיתי ברמת ה - DB
    תודה,
    אייל
    • סומן כתשובה על-ידי Eran Sharvit יום רביעי 30 מאי 2012 16:09
    יום שני 21 מאי 2012 11:11