Heur\u00e9ka! :)<\/p>\n
El\u0151zm\u00e9ny<\/a>.<\/p>\n Az el\u0151zm\u00e9nyben l\u00e1that\u00f3 els\u0151 deadlock lenyomata xmlben \u00edgy n\u00e9z ki:<\/p>\n Figyelj\u00fck meg, hogy mindk\u00e9t processz a “KEY: 6:72057594959101952 (8194443284a0)” kulcson v\u00e1rakozik, ezen vesztek \u00f6ssze.<\/p>\n Viszont az xmlben nem az van, mint a k\u00e9pen!!!<\/p>\n Az xml v\u00e9ge fel\u00e9 l\u00e1tszik ki-mit ownol. owner-list\/owner elemek:<\/p>\n A m\u00e1sodik processz nem U lockot tart az er\u0151forr\u00e1son, hanem S-t, Shared lockot! De mi\u00e9rt U l\u00e1tszik a k\u00e9pen? A bugos kutya \u00e9letbe! Tehet, mit l\u00e1tunk itt tk\u00e9ppen? Az egyik processz tart U lockot a k\u00f6z\u00f6s er\u0151forr\u00e1sra (owner id=”processbe41288″ mode=”U”), a m\u00e1sik pedig egy Shared lockot (owner id=”process3c3dc8″ mode=”S”). Mi\u00e9rt shared az egyik? Mivel valamelyik kor\u00e1bbi sorban (ez nem l\u00e1tszik az xmlb\u0151l, m\u00e1r lefutott) lemaradt a with(updlock) a select ut\u00e1ni t\u00e1bla m\u00f6g\u00fcl. Heur\u00e9ka! :) El\u0151zm\u00e9ny. Az el\u0151zm\u00e9nyben l\u00e1that\u00f3 els\u0151 deadlock lenyomata xmlben \u00edgy n\u00e9z ki: <deadlock-list> <deadlock victim="process3c3dc8"> <process-list> <process id="process3c3dc8" taskpriority="0" logused="236" waitresource="KEY:…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,21,58,77,78],"tags":[],"class_list":["post-1289","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2005","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1289"}],"version-history":[{"count":6,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1289\/revisions"}],"predecessor-version":[{"id":1293,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1289\/revisions\/1293"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\n<deadlock-list>\r\n\t<deadlock victim="process3c3dc8">\r\n\t\t<process-list>\r\n\t\t\t<process id="process3c3dc8"\r\n\t\t\t\t\t taskpriority="0"\r\n\t\t\t\t\t logused="236"\r\n\t\t\t\t\t waitresource="KEY: 6:72057594959101952 (8194443284a0)"\r\n\t\t\t\t\t waittime="8196"\r\n\t\t\t\t\t ownerId="724347"\r\n\t\t\t\t\t transactionname="user_transaction"\r\n\t\t\t\t\t lasttranstarted="2012-07-17T13:30:06.643"\r\n\t\t\t\t\t XDES="0x9dec3b0"\r\n\t\t\t\t\t lockMode="U"\r\n\t\t\t\t\t schedulerid="3"\r\n\t\t\t\t\t kpid="2744"\r\n\t\t\t\t\t status="suspended"\r\n\t\t\t\t\t spid="85"\r\n\t\t\t\t\t sbid="0"\r\n\t\t\t\t\t ecid="0"\r\n\t\t\t\t\t priority="0"\r\n\t\t\t\t\t trancount="1"\r\n\t\t\t\t\t lastbatchstarted="2012-07-17T13:30:06.643"\r\n\t\t\t\t\t lastbatchcompleted="2012-07-17T13:30:06.643"\r\n\t\t\t\t\t clientapp=".Net SqlClient Data Provider"\r\n\t\t\t\t\t hostname="AlmaDEVWEB02"\r\n\t\t\t\t\t hostpid="1312"\r\n\t\t\t\t\t loginname="BuildAgentUser"\r\n\t\t\t\t\t isolationlevel="repeatable read (3)"\r\n\t\t\t\t\t xactid="724347"\r\n\t\t\t\t\t currentdb="6"\r\n\t\t\t\t\t lockTimeout="4294967295"\r\n\t\t\t\t\t clientoption1="673316896"\r\n\t\t\t\t\t clientoption2="128056">\r\n\t\t\t\t<executionStack>\r\n\t\t\t\t\t<frame procname="AlmaDev.dbo.spExecuteTransaction"\r\n\t\t\t\t\t\t line="136"\r\n\t\t\t\t\t\t stmtstart="7378"\r\n\t\t\t\t\t\t stmtend="7554"\r\n\t\t\t\t\t\t sqlhandle="0x0300060069e52875f254de0091a000000100000000000000">\r\n\t\t\t\t\t\tSELECT @toBalance = Balance FROM Account WITH (UPDLOCK) WHERE Id = @toAccountID\r\n\t\t\t\t\t<\/frame>\r\n\t\t\t\t<\/executionStack>\r\n\t\t\t\t<inputbuf>\r\n\t\t\t\t\tProc [Database Id = 6 Object Id = 1965614441]\r\n\t\t\t\t<\/inputbuf>\r\n\t\t\t<\/process>\r\n\t\t\t<process id="processbe41288"\r\n\t\t\t\t\t taskpriority="0"\r\n\t\t\t\t\t logused="236"\r\n\t\t\t\t\t waitresource="KEY: 6:72057594959101952 (8194443284a0)"\r\n\t\t\t\t\t waittime="2505"\r\n\t\t\t\t\t ownerId="724314"\r\n\t\t\t\t\t transactionname="user_transaction"\r\n\t\t\t\t\t lasttranstarted="2012-07-17T13:30:06.610"\r\n\t\t\t\t\t XDES="0x9af4e80"\r\n\t\t\t\t\t lockMode="X"\r\n\t\t\t\t\t schedulerid="2"\r\n\t\t\t\t\t kpid="4716"\r\n\t\t\t\t\t status="suspended"\r\n\t\t\t\t\t spid="108"\r\n\t\t\t\t\t sbid="0"\r\n\t\t\t\t\t ecid="0"\r\n\t\t\t\t\t priority="0"\r\n\t\t\t\t\t trancount="2"\r\n\t\t\t\t\t lastbatchstarted="2012-07-17T13:30:06.610"\r\n\t\t\t\t\t lastbatchcompleted="2012-07-17T13:30:06.610"\r\n\t\t\t\t\t clientapp=".Net SqlClient Data Provider"\r\n\t\t\t\t\t hostname="AlmaDEVWEB02"\r\n\t\t\t\t\t hostpid="1312"\r\n\t\t\t\t\t loginname="BuildAgentUser"\r\n\t\t\t\t\t isolationlevel="repeatable read (3)"\r\n\t\t\t\t\t xactid="724314"\r\n\t\t\t\t\t currentdb="6"\r\n\t\t\t\t\t lockTimeout="4294967295"\r\n\t\t\t\t\t clientoption1="673316896"\r\n\t\t\t\t\t clientoption2="128056">\r\n\t\t\t\t<executionStack>\r\n\t\t\t\t\t<frame procname="AlmaDev.dbo.spExecuteTransaction"\r\n\t\t\t\t\t\t line="138"\r\n\t\t\t\t\t\t stmtstart="7556"\r\n\t\t\t\t\t\t stmtend="7830"\r\n\t\t\t\t\t\t sqlhandle="0x0300060069e52875f254de0091a000000100000000000000">\r\n\t\t\t\t\t\tUPDATE Account SET\r\n\t\t\t\t\t\tBalance += (@amount - @commissionAmount)\r\n\t\t\t\t\t\tWHERE Id = @toAccountID\r\n\t\t\t\t\t<\/frame>\r\n\t\t\t\t<\/executionStack>\r\n\t\t\t\t<inputbuf>\r\n\t\t\t\t\tProc [Database Id = 6 Object Id = 1965614441]\r\n\t\t\t\t<\/inputbuf>\r\n\t\t\t<\/process>\r\n\t\t<\/process-list>\r\n\t\t<resource-list>\r\n\t\t\t<keylock hobtid="72057594959101952"\r\n\t\t\t\t\t dbid="6"\r\n\t\t\t\t\t objectname="AlmaDev.dbo.Account"\r\n\t\t\t\t\t indexname="PK_Account"\r\n\t\t\t\t\t id="lockc99fc00"\r\n\t\t\t\t\t mode="U"\r\n\t\t\t\t\t associatedObjectId="72057594959101952">\r\n\t\t\t\t<owner-list>\r\n\t\t\t\t\t<owner id="processbe41288"\r\n\t\t\t\t\t\t mode="U"\/>\r\n\t\t\t\t<\/owner-list>\r\n\t\t\t\t<waiter-list>\r\n\t\t\t\t\t<waiter id="process3c3dc8"\r\n\t\t\t\t\t\t\tmode="U"\r\n\t\t\t\t\t\t\trequestType="convert"\/>\r\n\t\t\t\t<\/waiter-list>\r\n\t\t\t<\/keylock>\r\n\t\t\t<keylock hobtid="72057594959101952"\r\n\t\t\t\t\t dbid="6"\r\n\t\t\t\t\t objectname="AlmaDev.dbo.Account"\r\n\t\t\t\t\t indexname="PK_Account"\r\n\t\t\t\t\t id="lockc99fc00"\r\n\t\t\t\t\t mode="U"\r\n\t\t\t\t\t associatedObjectId="72057594959101952">\r\n\t\t\t\t<owner-list>\r\n\t\t\t\t\t<owner id="process3c3dc8"\r\n\t\t\t\t\t\t mode="S"\/>\r\n\t\t\t\t<\/owner-list>\r\n\t\t\t\t<waiter-list>\r\n\t\t\t\t\t<waiter id="processbe41288"\r\n\t\t\t\t\t\t\tmode="X"\r\n\t\t\t\t\t\t\trequestType="convert"\/>\r\n\t\t\t\t<\/waiter-list>\r\n\t\t\t<\/keylock>\r\n\t\t<\/resource-list>\r\n\t<\/deadlock>\r\n<\/deadlock-list>\r\n<\/pre>\n
\r\n\t\t<resource-list>\r\n\t\t\t\t<owner-list>\r\n\t\t\t\t\t<owner id="processbe41288" mode="U"\/>\r\n\t\t\t\t<\/owner-list>\r\n\t\t\t\t<waiter-list>\r\n\t\t\t\t\t<waiter id="process3c3dc8" mode="U" requestType="convert"\/>\r\n\t\t\t\t<\/waiter-list>\r\n\t\t\t<\/keylock>\r\n\t\t\t\t<owner-list>\r\n\t\t\t\t\t<owner id="process3c3dc8" mode="S"\/> <!-- Ez nem U !!! -->\r\n\t\t\t\t<\/owner-list>\r\n\t\t\t\t<waiter-list>\r\n\t\t\t\t\t<waiter id="processbe41288" mode="X" requestType="convert"\/>\r\n\t\t\t\t<\/waiter-list>\r\n\t\t\t<\/keylock>\r\n\t\t<\/resource-list>\r\n<\/pre>\n
\n\u00cdgy m\u00e1r krist\u00e1lytiszta a dolog. Azt nem \u00e9rtettem, hogy tudott kiadni az SQL Server k\u00e9t U lockot ugyanarra az er\u0151forr\u00e1sra. Sehogy, az elm\u00e9let j\u00f3. Csak az SSMS bugos.<\/p>\n
\nEz az alap\u00e1ll\u00e1s. Azt\u00e1n az egyik Update lockra akar konvert\u00e1lni (waiter id=”process3c3dc8″ mode=”U” requestType=”convert”, ez nem teljesen vil\u00e1gos mi\u00e9rt, mivel eleve az van neki. A m\u00e1sik Sharedr\u0151l eXclusive-ra akar konvert\u00e1lni. Ez m\u00e1r a nem megy, mivel az X nem kompatibilis az U-val, \u00edgy az nem adhat\u00f3 ki, a m\u00e1sodik processznek v\u00e1rnia kell, de mivel az els\u0151 \u00e1ltal k\u00e9rt U sem a U-val, \u00edgy \u00f6sszeakadnak.
\nM\u00edg most sem teljesen tiszta minden, az XML-ben sem l\u00e1tni az \u00f6sszes r\u00e9szletet, pl. mi\u00e9rt van az U-U konverzi\u00f3, de a megold\u00e1s viszonylag 100%-ra a k\u00f6vetkez\u0151: \u00e1t kell n\u00e9zni a beteg t\u00e1rolt elj\u00e1r\u00e1st, \u00e9s meg kell keresni benne olyan select-et, amely az Account t\u00e1bl\u00e1t nem updlockkal \u00e9rik el. Ezekre fel kell rakni az updlockot, \u00e9s val\u00f3sz\u00edn\u0171leg kiesik a deadlock.
\nTanuls\u00e1g: nem el\u00e9g a deadlock graph grafikus n\u00e9zet\u00e9t megn\u00e9zni, bele kell kukkantani az xml-be is, ha valami nem logikus.
\nEzt tudva m\u00e1r m\u00e1s is tal\u00e1lkozott ezzel: http:\/\/rusanu.com\/2010\/05\/12\/the-puzzle-of-u-locks-in-deadlock-graphs\/<\/a>
\nSz\u00f3val m\u00e9g nem teljesen tiszta minden, de az IT m\u00e1r csak ilyen, f\u00e9linform\u00e1ci\u00f3k alapj\u00e1n is tudni kell hib\u00e1t keresni.<\/p>\n","protected":false},"excerpt":{"rendered":"