Deadlock auf gleicher objid

Question

Hallo,

ich habe eine Tabelle mit mehreren Spalten. Auf dem Primarykey ist ein clustered Index und auf einer anderen Spalte (act_id) ist ein non-clustered index.

Bei Serverstart möchten zwei verschiedene Transaktionen die act_id Spalte verändern (update table set act_id = XX where act_id= YY). Dies führt allerdings zu folgendem Deadlock:

<deadlock-list>
 <deadlock victim="process4cfaf24e8">
  <process-list>
   <process id="process4cfaf24e8" taskpriority="0" logused="1320" waitresource="KEY: 5:72057594041139200 (3f811e17f88b)" waittime="2741" ownerId="3134090" transactionname="implicit_transaction" lasttranstarted="2018-09-19T08:58:15.500" XDES="0x4ceb483b0" lockMode="U" schedulerid="3" kpid="2540" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-09-19T08:58:15.503" lastbatchcompleted="2018-09-19T08:58:15.503" lastattention="1900-01-01T00:00:00.503" clientapp="Microsoft JDBC Driver for SQL Server" hostname="hostname" hostpid="0" loginname="name" isolationlevel="read committed (2)" xactid="3134090" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="60" stmtend="200" sqlhandle="0x020000003679d620a4c214ddb864b79194f2912999ce933d0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 decimal(38,0),@P1 bigint)UPDATE table SET ACTION_ID = @P0 WHERE (ACTION_ID = @P1)                    </inputbuf>
   </process>
   <process id="process4cfaf2ca8" taskpriority="0" logused="5696" waitresource="KEY: 5:72057594041139200 (ac8b325516ee)" waittime="2549" ownerId="3134066" transactionname="implicit_transaction" lasttranstarted="2018-09-19T08:58:15.250" XDES="0x4cb0f63b0" lockMode="U" schedulerid="3" kpid="10120" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-09-19T08:58:15.697" lastbatchcompleted="2018-09-19T08:58:15.693" lastattention="1900-01-01T00:00:00.693" clientapp="Microsoft JDBC Driver for SQL Server" hostname="hostname" hostpid="0" loginname="name" isolationlevel="read committed (2)" xactid="3134066" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="60" stmtend="200" sqlhandle="0x020000003679d620a4c214ddb864b79194f2912999ce933d0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 decimal(38,0),@P1 bigint)UPDATE table SET ACTION_ID = @P0 WHERE (ACTION_ID = @P1)                    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041139200" dbid="5" objectname="table" indexname="SYS_C0011055" id="lock4ce772300" mode="X" associatedObjectId="72057594041139200">
    <owner-list>
     <owner id="process4cfaf2ca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4cfaf24e8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041139200" dbid="5" objectname="table" indexname="SYS_C0011055" id="lock4ce76b900" mode="X" associatedObjectId="72057594041139200">
    <owner-list>
     <owner id="process4cfaf24e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4cfaf2ca8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Einen ähnlichen Deadlock habe ich auf einer anderen Spalte festgestellt, auf der nur ein clustered Index auf den Primarykey existiert (Sprache, Text_id und group_id)

<deadlock-list>
 <deadlock victim="process4d110a8c8">
  <process-list>
   <process id="process4d110a8c8" taskpriority="0" logused="13672" waitresource="KEY: 5:72057594041008128 (dcbf91d73d9d)" waittime="956" ownerId="3159045" transactionname="implicit_transaction" lasttranstarted="2018-09-19T09:11:12.683" XDES="0x4cd276d80" lockMode="U" schedulerid="2" kpid="10920" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-09-19T09:11:17.677" lastbatchcompleted="2018-09-19T09:11:17.670" lastattention="1900-01-01T00:00:00.670" clientapp="Microsoft JDBC Driver for SQL Server" hostname="name" hostpid="0" loginname="name" isolationlevel="read committed (2)" xactid="3159045" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="94" stmtend="278" sqlhandle="0x0200000065cc891a747b0785f85dd8ba7e172b86527154740000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bit)DELETE FROM dic WHERE (((TEXTID = @P0) AND (GROUPID = @P1)) AND (CHANGED = @P2))                            </inputbuf>
   </process>
   <process id="process4db028ca8" taskpriority="0" logused="151868" waitresource="KEY: 5:72057594041008128 (c22c4e07fd3c)" waittime="954" ownerId="3159275" transactionname="implicit_transaction" lasttranstarted="2018-09-19T09:11:14.327" XDES="0x4ccbb3000" lockMode="U" schedulerid="3" kpid="9768" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-09-19T09:11:17.677" lastbatchcompleted="2018-09-19T09:11:17.677" lastattention="1900-01-01T00:00:00.677" clientapp="Microsoft JDBC Driver for SQL Server" hostname="name" hostpid="0" loginname="name" isolationlevel="read committed (2)" xactid="3159275" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="94" stmtend="278" sqlhandle="0x0200000065cc891a747b0785f85dd8ba7e172b86527154740000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bit)DELETE FROM dic WHERE (((TEXTID = @P0) AND (GROUPID = @P1)) AND (CHANGED = @P2))                            </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041008128" dbid="5" objectname="dic" indexname="dic_PK" id="lock4cdff7780" mode="X" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process4db028ca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4d110a8c8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041008128" dbid="5" objectname="dic" indexname="dic_PK" id="lock4cef3f980" mode="X" associatedObjectId="72057594041008128">
    <owner-list>
     <owner id="process4d110a8c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4db028ca8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Kann dieser Deadlock auf Datenbankseite behoben bzw. verhindert werden? 

Versuche mit einem Non-clustered Index auf den PK haben nichts gebracht, ebenso wenig den PK in den non-clustered Index mit zu inkludieren (soweit ich verstanden hab, kann somit ein Bookmark Lookup Deadlock verhindert werden) 

Vielen Dank!

Answer 1

Hallo, meines Wissen erzeugen Clustered Index und Nonclustered Index auf die selbe Spalte genau das Problem mit dem Bookmark Lookup Deadlock wenn die Zugriffe zeitlich ungünstig laufen.

Hier mal ein Video zum Thema Bookmark Lookup Deadlock

https://www.youtube.com/watch?v=91EZt5y4uiA

---

Benjamin Hoch
MCSE: Data Platform & Data Management and Analytics
MCSA: SQL Server 2012/2014 & 2016 DB Administration
MCSA: Windows Server 2012

Answer 2

Ein klassischer Deadlock kann nur auftreten, wenn 2 Transaktionen Daten in unterschiedlicher Reihenfolge durch die Transaktion sperren.

ALso
Transaktion 1:

Update Tabelle1
Update Tabelle2

Transaktion2:

Update Tabelle2
Update Tabelle1

Da der Update die Zeilen sperrt bremsen sich die Transaktionen nun gegenseitig aus.
Zu prüfen ist daher wie die Reihenfolge der Updates auf die Tabellen dieser beiden parallelen Transaktionen zu korrigieren wäre.

Answer 3

Okay vielen Dank. Dann muss ich wohl schauen wie die Reihenfolge angepasst werden kann. das komische an der Sache ist, dass der selbe Code auf der Oracle Datenbank ohne Probleme läuft...

Answer 4

Das kann u.U. im Milli-/Microsekundenbereich liegen. Wenn Transaktionen kurz genug sind, tritt das Problem eben nie auf.

Auch gibt es u.U. andere Sperrlogiken.

https://technet.microsoft.com/en-us/library/jj856598%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Es kann da schon mal passieren, dass es keinen einzelnen Row-Lock sondern eine ganze Seite gesperrt wird, so dass unterschiedliche Zeilen gesperrt sind obwohl keine Updates vorliegen.
Deshalb ist es ja grundsätzlich immer wichtig, Tabellen in ihrer Abhängigkeitsfolge zu bearbeiten.
Deadlocks waren auch schon in Vor-SQL-Zeiten immer ein Problem.

Answer 5

Oracle arbeitet standardmäßig mit einer Form von Versioning, wodurch es dann Blockaden vermeidet.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)

Answer 6

Sollte mit Read Committed und Read Committed Snapshot = ON das Verhalten nicht gleich zu Oracle sein?

Answer 7

Nein, da der SQL-Server Versioning nur verwendet, wenn innerhalb einer Transaktion bereits geschriebene Daten noch mal verändert werden (z.B. in After-Inserttriggern).

Der Transaktionstyp gilt auch nur für deine eigene Transaktion.
Wenn du also als Transaktion Snapshot verwendest bekommst du nur Daten zu sehen, die zu diesem Zeitpunkt festliegen.
Alle anderen Sitzungen sind nun gezwungen, Versioning durchzuführen, damit deine Transaktion das nicht sieht.
Sobald dein Snapshot aufgehoben wird, führt der SQL-Server wieder alles zusammen.
Insgesamt führt dies zu einem lansameren SQL-Server.

Es gibt eigentlich nur wenige Anwendungen, wo ein Sanpshot tatsächlich erforderlich wäre.

Oracle arbeitet da grundsätzlich mit Versioning um Sperren weitestgehend zu vermeiden, was aber dann durchaus bei konkurierenden Updates mangels Sperre dann zum Fehler "Daten wurden zwischenzeitlich verändert" führen kann. Um dies dann zu umgehen, muss man u.U. explizite Locks setzen (Select ... for update), was das Lock-Problem dann allerdings wieder einführt.

Answer 8

<inkorrekt>

Nein, da der SQL-Server Versioning nur verwendet, wenn innerhalb einer Transaktion bereits geschriebene Daten noch mal verändert werden (z.B. in After-Inserttriggern).
</inkorrekt>
<inkorrekt>
Der Transaktionstyp gilt auch nur für deine eigene Transaktion.
Wenn du also als Transaktion Snapshot verwendest bekommst du nur Daten zu sehen, die zu diesem Zeitpunkt festliegen.
Alle anderen Sitzungen sind nun gezwungen, Versioning durchzuführen, damit deine Transaktion das nicht sieht.
Sobald dein Snapshot aufgehoben wird, führt der SQL-Server wieder alles zusammen.
Insgesamt führt dies zu einem lansameren SQL-Server.
</inkorrekt>
<inkorrekt>
Es gibt eigentlich nur wenige Anwendungen, wo ein Sanpshot tatsächlich erforderlich wäre.
</inkorrekt>

Oracle arbeitet da grundsätzlich mit Versioning um Sperren weitestgehend zu vermeiden, was aber dann durchaus bei konkurierenden Updates mangels Sperre dann zum Fehler "Daten wurden zwischenzeitlich verändert" führen kann. Um dies dann zu umgehen, muss man u.U. explizite Locks setzen (Select ... for update), was das Lock-Problem dann allerdings wieder einführt.

Ich habe oben der Einfachheit halber die inkorrekten oder irreführenden Formulierungen durchgestrichen/Update: Leider wird das tag "strike" oder "del" nicht erkannt. Daher mit "inkorrekt" umklammert. Sorry für das schlchte Layout dadurch

Die Version Store wird ab dem Moment ab immer gepflegt, ab dem man die Datenbank dafür aktiviert. Das ist letztlich auch ein Grund, warum man das auf Datenbank-Ebene aktivieren muss, und es in der Session direkt nicht ausreicht, wie für andere Isolationsstufen.

Es wird nichts "zusammengeführt", sondern die Daten werden wirklich direkt in die entsprechende Page der Tabelle geschrieben. Nur zusätzlich kommt der Pointer auf die Vor-Version in der Tempdb.

Die Tempdb erhält nun mehr IO. Aber damit hat man ja nicht einen "langsameren" SQL Server, sondern wenn man alles richtig designt hat, ist die Workload am Ende schneller, weil sie Wartezustäde vermeidet.

Man muss natürlich das Bottleneck richtig identifiziert haben. Versioning bei IO oder CPU Problemen einzuführen, das wäre in der Tat destruktiv und man würde "den SQL Server verlangsamen". Vielleicht erklärt das Ihre Beobachtungen.

Ansonsten ist das Verhalten von Oracles Implementierung sehr ähnlich derer von Microsoft SQL Server. Auch bei SQL Server kann es die besagten Probleme beim Commit eines Update geben, und die Lösung kann genau die selbe wie bei Oracle ein "early locking" sein.

ich hoffe, das klärt etwas auf

der

Andreas

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)

Answer 9

Beim Versioning gibt es immer ein Performanceproblem beim Lesen, da zu der aktiven Transaktion mittels TempDB die dazu passenden Daten jedes mal neu ermittelt werden müssen. Somit schafft dies zusätzlichen IO-Overhead und verlangsamt den SQL-Server tatsächlich.
Die meisten Anwendung halten durch die offenen Sitzungen nämlich immer eine Transaktion offen, die das Aufräumen der TempDB bzgl. der aufgelaufenen Versionen durchaus verhindert.

Answer 10

Beim Versioning gibt es immer ein Performanceproblem beim Lesen, da zu der aktiven Transaktion mittels TempDB die dazu passenden Daten jedes mal neu ermittelt werden müssen. Somit schafft dies zusätzlichen IO-Overhead und verlangsamt den SQL-Server tatsächlich.
Die meisten Anwendung halten durch die offenen Sitzungen nämlich immer eine Transaktion offen, die das Aufräumen der TempDB bzgl. der aufgelaufenen Versionen durchaus verhindert.

Das ist nicht richtig so

Richtig ist, dass die Daten aus der Tempdb gelesen werden müssen. Diese liegt aber normalerweise im RAM.

Ebenso wie die Verweise darauf.

Wenn man sein System also richtig dimensioniert hat, bekommt man KEIN Problem.

Und ja, es gibt schlecht programmierte Applikationen, die Transaktionen nicht zeitnah schließen. Aber "die meisten" ist dann doch übertrieben. Es ist zwar selten, dass ich Softwarecode verteidige, aber so schlecht ist die Welt dann doch wieder nicht. ;-)

Aber wir sind nun auch sehr vom Ursprungsthema auf. Gerne eine neue Diskussion eröffnen, wenn Sie sich über Vermutungen austauschen möchten.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)

Answer 11

Vielen Dank für die ganzen Antworten. Aus den Antworten entnehme ich, dass es nicht möglich ist nur über eine andere Konfiguration der Indizes diesen Deadlock zu verhindern sondern der Programmcode der die Transaktionen durchführt zu überprüfen ist richtig?

Answer 12

Das Grundprinzip die Reihenfolge zusammenhängender Tabellen bei Updates in Transaktionen einzuhalten bleibt davon unberührt.

Du kannst natürlich mit Lock-Timouts ebenso Deadlocks verhindern:

https://docs.microsoft.com/de-de/sql/t-sql/statements/set-lock-timeout-transact-sql?view=sql-server-2017

Bei Auftreten des Lock-Timeouts kann man die Transaktion zurückdrehen (Rollback) und halt wiederholen, wobei man die Anzahl Wiederholungen natürlich nicht unendlich machen sollte.

Aber an meinem obigen Beispiel würde der 1. Update der 2. Transaktion zurückgedreht, die Sperre somit aufgehoben und die 1. Transaktion kann abgeschlossen werden. Bei der Wiederholung in der 2. Sitzung wäre nun alles frei.
Damit kann man die selteneren Fälle von Deadlocks schneller umschiffen ohne das Problem eigentlich zu lösen.
Aber bei ein paar wenigen Fällen kann das vertretbar sein.

Was anderes kannst du im Fall von Updatekonflikt bei Versioning ja auch nicht machen, da du dann auf jeden Fall eine neue Transaktion benötigst.

Answer 13

Klar liegt im Endeffekt ja alles im RAM, da jeder Prozess nun mal erst die Daten in den RAM einlesen muss.
Aber da die TempDB durchaus auch größer sein kann als der aktuell unterstützte RAM kann das auch schon mal wieder verdrängt und muss erneut gelesen werden.
Und je nach dem wieviele Versionen sich halt so aufschaukeln kann auch das überlesen schon mal eine Weile dauern.

"Wenn man sein System also richtig dimensioniert hat, bekommt man KEIN Problem."

Ist das nicht eine Grundvoraussetzung für vernünftiges Datenbankdesign?
Wenn sich jeder daran halten würde, bräuchte man ja die Foren nicht.

Und was die "schlechten" Anwendungen angeht, so liegt dies i.d.R daran, dass mit wenigen Daten getestet wird. Spätestens wenn dann mehrere 100.000 oder Millionen von Daten entstehen und 100 oder 1000 Transaktion pro Minute benötigt werden zeigt sich die Qualität des Designs.