Bagikan melalui

Panduan Deadlock

Berlaku untuk:SQL ServerDatabase Azure SQLInstans Terkelola Azure SQLAzure Synapse AnalyticsSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Artikel ini membahas kebuntuan di Mesin Database secara mendalam. Kebuntuan disebabkan oleh bersaing, kunci bersamaan dalam database, sering kali dalam transaksi multi-langkah. Untuk informasi selengkapnya tentang transaksi dan kunci, lihat Panduan penguncian transaksi dan penerapan versi baris.

Untuk informasi lebih spesifik tentang identifikasi dan pencegahan kebuntuan di Azure SQL Database dan database SQL di Fabric, lihat Menganalisis dan mencegah kebuntuan di Azure SQL Database dan database SQL di Fabric.

Memahami kebuntuan

Kebuntuan terjadi ketika dua atau beberapa tugas secara permanen memblokir satu sama lain oleh setiap tugas yang memiliki kunci pada sumber daya yang coba dikunci oleh tugas lain. Contohnya:

  • Transaksi A memperoleh kunci bersama pada baris 1.

  • Transaksi B memperoleh kunci bersama pada baris 2.

  • Transaksi A sekarang meminta kunci eksklusif pada baris 2, dan diblokir hingga transaksi B selesai dan melepaskan kunci bersama yang dimilikinya pada baris 2.

  • Transaksi B sekarang meminta kunci eksklusif pada baris 1, dan diblokir hingga transaksi A selesai dan melepaskan kunci bersama yang dimilikinya pada baris 1.

Transaksi A tidak dapat diselesaikan sampai transaksi B selesai, tetapi transaksi B diblokir oleh transaksi A. Kondisi ini juga disebut dependensi siklik: Transaksi A memiliki dependensi pada transaksi B, dan transaksi B menutup lingkaran dengan memiliki dependensi pada transaksi A.

Kedua transaksi dalam kebuntuan menunggu selamanya, kecuali kebuntuan rusak oleh proses eksternal. Monitor kebuntuan Mesin Database secara berkala memeriksa tugas yang berada dalam kebuntuan. Jika monitor mendeteksi dependensi siklik, monitor memilih salah satu tugas sebagai korban dan mengakhiri transaksinya dengan kesalahan. Ini memungkinkan tugas lain untuk menyelesaikan transaksinya. Aplikasi dengan transaksi yang dihentikan dengan kesalahan dapat mencoba kembali transaksi, yang biasanya selesai setelah transaksi lain yang di-deadlock selesai.

Kebuntuan sering dikacaukan dengan pemblokiran normal. Ketika transaksi meminta kunci pada sumber daya yang dikunci oleh transaksi lain, transaksi yang meminta menunggu hingga kunci dilepaskan. Secara default, transaksi di Mesin Database tidak kehabisan waktu, kecuali LOCK_TIMEOUT diatur. Transaksi yang meminta diblokir, tidak di-deadlock, karena transaksi yang meminta belum melakukan apa pun untuk memblokir transaksi yang memiliki kunci. Akhirnya, transaksi pemilik menyelesaikan dan melepaskan kunci, dan kemudian transaksi yang meminta diberikan kunci dan melanjutkan. Kebuntuan diselesaikan segera, sedangkan pemblokiran dapat, secara teori, bertahan tanpa batas waktu. Kebuntuan terkadang disebut pelukan mematikan.

Kebuntuan dapat terjadi pada sistem apa pun dengan beberapa utas, bukan hanya pada sistem manajemen database relasional, dan dapat terjadi untuk sumber daya selain kunci pada objek database. Misalnya, utas dalam sistem operasi multithreaded mungkin memperoleh satu atau beberapa sumber daya, seperti blok memori. Jika sumber daya yang diperoleh saat ini dimiliki oleh utas lain, utas pertama mungkin harus menunggu utas pemilik untuk merilis sumber daya target. Utas tunggu dikatakan memiliki dependensi pada utas pemilik untuk sumber daya tertentu tersebut. Dalam contoh Mesin Database, sesi dapat mengalami kebuntuan saat memperoleh sumber daya non-database, seperti memori atau utas.

Diagram memperlihatkan kebuntuan transaksi.

Dalam ilustrasi, transaksi T1 memiliki dependensi pada transaksi T2 untuk Part sumber daya kunci tabel. Demikian pula, transaksi T2 memiliki dependensi pada transaksi T1 untuk Supplier sumber daya kunci tabel. Karena dependensi ini membentuk siklus, ada kebuntuan antara transaksi T1 dan T2.

Berikut adalah ilustrasi yang lebih umum tentang kebuntuan:

Diagram memperlihatkan tugas dalam status kebuntuan.

  • Tugas T1 memiliki kunci pada sumber daya R1 (ditunjukkan oleh panah dari R1 ke T1), dan telah meminta kunci pada sumber daya R2 (ditunjukkan oleh panah dari T1 ke R2).

  • Tugas T2 memiliki kunci pada sumber daya R2 (ditunjukkan oleh panah dari R2 ke T2), dan telah meminta kunci pada sumber daya R1 (ditunjukkan oleh panah dari T2 ke R1).

  • Karena tidak ada tugas yang dapat dilanjutkan hingga sumber daya tersedia dan tidak ada sumber daya yang dapat dirilis sampai tugas berlanjut, status kebuntuan ada.

Note

Mesin Database secara otomatis mendeteksi siklus kebuntuan. Ini memilih salah satu transaksi sebagai korban kebuntuan dan mengakhirinya dengan kesalahan untuk memecah kebuntuan.

Sumber daya yang dapat kebuntuan

Setiap sesi pengguna mungkin memiliki satu atau beberapa tugas yang berjalan atas namanya di mana setiap tugas mungkin memperoleh atau menunggu untuk memperoleh sumber daya. Jenis sumber daya berikut dapat menyebabkan pemblokiran yang dapat mengakibatkan kebuntuan.

  • Locks. Menunggu untuk memperoleh kunci pada sumber daya, seperti objek, halaman, baris, metadata, dan aplikasi dapat menyebabkan kebuntuan. Misalnya, transaksi T1 memiliki kunci bersama (S) pada baris r1 dan menunggu untuk mendapatkan kunci eksklusif (X) pada r2. Transaksi T2 memiliki kunci bersama (S) pada r2 dan sedang menunggu untuk mendapatkan kunci eksklusif (X) pada baris r1. Ini menghasilkan siklus penguncian di mana T1 dan T2 saling menunggu untuk melepaskan sumber daya yang terkunci.

  • Utas pekerja. Tugas antrean yang menunggu utas pekerja yang tersedia dapat menyebabkan kebuntuan. Jika tugas yang diantrekan memiliki sumber daya yang memblokir semua utas pekerja, hasil kebuntuan. Misalnya, sesi S1 memulai transaksi dan memperoleh kunci bersama (S) pada baris r1 lalu tidur. Sesi aktif yang berjalan pada semua thread pekerja yang tersedia berusaha mendapatkan kunci eksklusif (X) pada baris r1. Karena sesi S1 tidak dapat memperoleh utas pekerja, sesi tidak dapat melakukan transaksi dan melepaskan kunci pada baris r1. Ini mengalihkan kebuntuan.

  • Memory. Ketika permintaan bersamaan menunggu pemberian memori yang tidak dapat dipenuhi dengan memori yang tersedia, kebuntuan dapat terjadi. Misalnya, dua kueri bersamaan, Q1 dan Q2, dijalankan sebagai fungsi yang ditentukan pengguna yang masing-masing memperoleh memori 10 MB dan 20 MB. Jika setiap kueri membutuhkan 30 MB dan total memori yang tersedia adalah 20 MB, maka Q1 dan Q2 harus menunggu satu sama lain untuk melepaskan memori, yang menghasilkan kebuntuan.

  • Sumber daya terkait eksekusi kueri paralel. Koordinator, produsen, atau utas konsumen yang terkait dengan port pertukaran mungkin memblokir satu sama lain yang menyebabkan kebuntuan biasanya ketika menyertakan setidaknya satu proses lain yang bukan bagian dari kueri paralel. Selain itu, ketika kueri paralel memulai eksekusi, Mesin Database menentukan tingkat paralelisme, dan jumlah utas pekerja yang diperlukan, berdasarkan beban kerja saat ini. Jika beban kerja sistem tiba-tiba berubah, misalnya, di mana kueri baru mulai berjalan di server atau sistem kehabisan utas pekerja, maka kebuntuan dapat terjadi.

  • Beberapa sumber daya Active Result Sets (MARS). Sumber daya ini digunakan untuk mengontrol interleaving beberapa permintaan aktif di bawah MARS. Untuk informasi selengkapnya, lihat Menggunakan Beberapa Set Hasil Aktif (MARS) di SQL Server Native Client.

    • Sumber daya pengguna. Ketika utas menunggu sumber daya yang berpotensi dikontrol oleh aplikasi pengguna, sumber daya dianggap sebagai sumber daya eksternal atau pengguna dan diperlakukan seperti kunci.

    • Mutex sesi. Tugas yang berjalan dalam satu sesi saling terkait, yang berarti bahwa hanya satu tugas yang dapat berjalan di bawah sesi pada waktu tertentu. Sebelum tugas dapat berjalan, tugas harus memiliki akses eksklusif ke mutex sesi.

    • Mutex transaksi. Semua tugas yang berjalan dalam satu transaksi saling terkait, yang berarti bahwa hanya satu tugas yang dapat berjalan di bawah transaksi pada waktu tertentu. Sebelum tugas dapat berjalan, tugas harus memiliki akses eksklusif ke mutex transaksi.

      Agar tugas berjalan di bawah MARS, tugas harus memperoleh mutex sesi. Jika tugas berjalan di bawah transaksi, maka harus memperoleh mutex transaksi. Ini menjamin bahwa hanya satu tugas yang aktif pada satu waktu dalam sesi tertentu dan transaksi tertentu. Setelah mutex yang diperlukan diperoleh, tugas dapat dijalankan. Ketika tugas selesai, atau menghasilkan di tengah permintaan, tugas pertama-tama merilis mutex transaksi, diikuti oleh mutex sesi, dalam urutan terbalik akuisisi. Namun, kebuntuan dapat terjadi dengan sumber daya ini. Dalam pseudocode berikut, dua tugas, permintaan pengguna U1 dan permintaan pengguna U2, berjalan dalam sesi yang sama.

      U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
U2:    Rs2=Command2.Execute("select colA from sometable");

      Prosedur tersimpan yang dijalankan dari permintaan pengguna U1 telah memperoleh mutex sesi. Jika prosedur tersimpan membutuhkan waktu lama untuk dijalankan, diasumsikan oleh Mesin Database bahwa prosedur tersimpan sedang menunggu input dari pengguna. Permintaan pengguna U2 sedang menunggu mutex sesi saat pengguna sedang menunggu hasil yang ditetapkan dari U2, dan U1 sedang menunggu sumber daya pengguna. Ini adalah status kebuntuan yang secara logis diilustrasikan sebagai:

      Diagram alur logis prosedur tersimpan di MARS.

Kebuntuan juga dapat terjadi ketika tabel dipartisi dan LOCK_ESCALATION pengaturan ALTER TABLE diatur ke AUTO. Ketika LOCK_ESCALATION diatur ke AUTO, konkurensi meningkat dengan memungkinkan Mesin Database mengunci partisi tabel di tingkat HoBT alih-alih pada tingkat tabel. Namun, ketika transaksi terpisah menyimpan kunci partisi dalam tabel dan menginginkan kunci di suatu tempat pada partisi transaksi lain, ini menyebabkan kebuntuan. Jenis kebuntuan ini dapat dihindari dengan mengatur LOCK_ESCALATION ke TABLE. Namun, pengaturan ini mengurangi konkurensi dengan memaksa pembaruan besar ke partisi untuk menunggu kunci tabel.

Deteksi kebuntuan

Semua sumber daya yang tercantum di bagian Sumber Daya yang dapat kebuntuan berpartisipasi dalam skema deteksi kebuntuan Mesin Database. Deteksi kebuntuan dilakukan oleh utas monitor kunci yang secara berkala memulai pencarian melalui semua tugas dalam instans Mesin Database. Poin-poin berikut menjelaskan proses pencarian:

  • Interval default adalah 5 detik.

  • Jika utas monitor kunci menemukan kebuntuan, interval deteksi kebuntuan turun dari 5 detik hingga serendah 100 milidetik tergantung pada frekuensi kebuntuan.

  • Jika utas monitor kunci berhenti menemukan kebuntuan, Mesin Database meningkatkan interval antara pencarian menjadi 5 detik.

  • Jika kebuntuan terdeteksi, diasumsikan bahwa utas-utas baru yang harus menunggu untuk mendapatkan kunci sedang memasuki siklus kebuntuan. Beberapa kunci pertama menunggu setelah kebuntuan terdeteksi segera memicu pencarian kebuntuan, daripada menunggu interval deteksi kebuntuan berikutnya. Misalnya, jika interval saat ini adalah 5 detik, dan kebuntuan baru saja terdeteksi, kunci berikutnya menunggu dimulai dari detektor kebuntuan segera. Jika penantian kunci ini adalah bagian dari kebuntuan, kunci tersebut langsung terdeteksi, bukan selama pencarian kebuntuan berikutnya.

Mesin Database biasanya hanya melakukan deteksi kebuntuan berkala. Karena jumlah kebuntuan yang ditemui dalam sistem biasanya kecil, deteksi kebuntuan berkala membantu mengurangi overhead deteksi kebuntuan dalam sistem.

Ketika monitor kunci memulai pencarian kebuntuan untuk utas tertentu, monitor tersebut mengidentifikasi sumber daya tempat utas menunggu. Monitor kunci kemudian menemukan pemilik untuk sumber daya tertentu tersebut dan secara rekursif melanjutkan pencarian kebuntuan untuk utas tersebut sampai menemukan siklus. Siklus yang diidentifikasi dengan cara ini membentuk kebuntuan.

Setelah kebuntuan terdeteksi, Mesin Database mengakhiri kebuntuan dengan memilih salah satu utas sebagai korban kebuntuan. Mesin Database mengakhiri batch yang sedang dieksekusi untuk utas, menggulung balik transaksi korban kebuntuan, dan mengirimkan kesalahan 1205 ke aplikasi. Menggulung balik transaksi untuk korban kebuntuan melepaskan semua kunci yang dipegang oleh transaksi. Ini memungkinkan transaksi utas lain menjadi tidak diblokir dan berlanjut. Kesalahan 1205 (korban kebuntuan) mencatat informasi tentang jenis sumber daya yang terlibat dalam kebuntuan.

Secara default, Mesin Basis Data memilih transaksi yang sedang berjalan yang paling tidak memakan biaya untuk digulung balik sebagai korban kebuntuan. Atau, pengguna dapat menentukan prioritas sesi dalam situasi kebuntuan menggunakan pernyataan .SET DEADLOCK_PRIORITY DEADLOCK_PRIORITY dapat diatur ke LOW, , NORMALatau HIGH, atau sebagai alternatif dapat diatur ke nilai bilangan bulat apa pun dalam rentang dari -10 hingga 10. Pada kasus tertentu, Mesin Basis Data mungkin memilih untuk mengubah prioritas deadlock dalam durasi singkat untuk mencapai kondisi proses bersamaan yang lebih baik.

Prioritas deadlock secara default ditetapkan ke NORMAL, atau 0. Jika dua sesi memiliki prioritas kebuntuan yang berbeda, transaksi pada sesi dengan prioritas yang lebih rendah dipilih sebagai korban kebuntuan. Jika kedua sesi memiliki prioritas kebuntuan yang sama, transaksi yang paling murah untuk dibatalkan dipilih. Jika sesi yang terlibat dalam siklus kebuntuan memiliki prioritas kebuntuan yang sama dan biaya yang sama, korban dipilih secara acak. Tugas yang digulung balik tidak dapat dipilih sebagai korban kebuntuan.

Saat bekerja dengan runtime bahasa umum (CLR), monitor kebuntuan secara otomatis mendeteksi kebuntuan untuk sumber daya sinkronisasi (monitor, kunci pembaca/penulis, dan gabungan utas) yang diakses di dalam prosedur terkelola. Namun, kebuntuan diselesaikan dengan melemparkan pengecualian dalam prosedur yang dipilih untuk menjadi korban kebuntuan. Penting untuk dipahami bahwa pengecualian tidak secara otomatis merilis sumber daya yang saat ini dimiliki oleh korban; sumber daya harus dirilis secara eksplisit. Konsisten dengan perilaku pengecualian, pengecualian yang digunakan untuk mengidentifikasi korban kebuntuan dapat ditangkap dan diberhentikan.

Alat informasi kebuntuan

Untuk melihat informasi kebuntuan, Mesin Basis Data menyediakan alat pemantauan dalam bentuk peristiwa xml_deadlock_report yang diperluas, dua flag jejak, dan peristiwa grafik kebuntuan di SQL Profiler.

Peristiwa xml_deadlock_report yang diperluas adalah metode yang direkomendasikan untuk menangkap informasi kebuntuan.

Peristiwa diperpanjang deadlock

Di SQL Server 2012 (11.x) dan versi yang lebih baru, xml_deadlock_report peristiwa yang diperluas harus digunakan alih-alih kelas peristiwa grafik kebuntuan di SQL Trace atau SQL Profiler.

Sesi kejadian system_health merekam xml_deadlock_report kejadian secara default. Peristiwa ini berisi grafik kebuntuan. Karena sesi system_health diaktifkan secara default, Anda tidak perlu mengonfigurasi sesi acara terpisah untuk menangkap informasi kebuntuan.

Grafik deadlock yang direkam biasanya memiliki tiga node yang berbeda:

  • victim-list. Pengidentifikasi proses korban kebuntuan.
  • process-list. Informasi tentang semua proses yang terlibat dalam kebuntuan.
  • resource-list. Informasi tentang sumber daya yang terlibat dalam kebuntuan.

Anda dapat melihat event_file data system_health target sesi di Management Studio. Jika ada xml_deadlock_report peristiwa yang terjadi, Management Studio menyajikan penggambaran grafis tugas dan sumber daya yang terlibat dalam kebuntuan, seperti yang terlihat dalam contoh berikut:

Cuplikan layar dari SSMS diagram visual XEvent Deadlock Graph.

Query berikut ini dapat menampilkan semua peristiwa kebuntuan yang ditangkap oleh target ring_buffer dari sesi system_health:

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       xdr.query('.') AS event_data
FROM (SELECT CAST ([target_data] AS XML) AS target_data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

Berikut set hasilnya.

Cuplikan layar dari SSMS hasil kueri system_health XEvent.

Contoh berikut menunjukkan output dari kolom event_data.

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Bendera pelacakan 1204 dan bendera pelacakan 1222

Ketika kebuntuan terjadi dan bendera pelacakan 1204 atau bendera pelacakan 1222 diaktifkan, detail kebuntuan dilaporkan dalam log kesalahan SQL Server. Bendera penelusuran 1204 melaporkan informasi kebuntuan yang diformat oleh setiap node yang terlibat dalam kebuntuan. Bendera pelacakan 1222 memformat informasi kebuntuan, pertama menurut proses lalu berdasarkan sumber daya. Dimungkinkan untuk mengaktifkan kedua bendera pelacakan untuk mendapatkan dua representasi dari peristiwa kebuntuan yang sama.

Important

Hindari menggunakan bendera pelacakan 1204 dan 1222 pada sistem intensif beban kerja yang mengalami kebuntuan. Menggunakan bendera pelacakan ini mungkin menimbulkan masalah performa. Sebagai gantinya, gunakan peristiwa deadlock yang diperluas untuk merekam informasi yang diperlukan.

Selain menentukan properti bendera jejak 1204 dan 1222, tabel berikut juga menunjukkan kesamaan dan perbedaan.

Property Bendera pelacakan 1204 dan bendera pelacakan 1222 Bendera pelacakan hanya 1204 Bendera pelacakan 1222 saja
Format keluaran Output diambil dalam log kesalahan SQL Server. Berfokus pada simpul yang terlibat dalam kebuntuan. Setiap simpul memiliki bagian khusus, dan bagian akhir menjelaskan korban kebuntuan. Mengembalikan informasi dalam format seperti XML yang tidak sesuai dengan skema Definisi Skema XML (XSD). Format ini memiliki tiga bagian utama. Bagian pertama menyatakan korban kebuntuan. Bagian kedua menjelaskan setiap proses yang terlibat dalam kebuntuan. Bagian ketiga menjelaskan sumber daya yang sinonim dengan node dalam trace flag 1204.
Mengidentifikasi atribut SPID:<x> ECID:<x>. Mengidentifikasi utas ID sesi dalam kasus proses paralel. Entri SPID:<x> ECID:0, di mana <x> digantikan oleh nilai SPID, mewakili utas utama. Entri SPID:<x> ECID:<y>, di mana <x> digantikan oleh nilai SPID dan <y> lebih besar dari 0, mewakili konteks eksekusi untuk SPID yang sama.

BatchID (sbid untuk bendera pelacakan 1222). Mengidentifikasi batch dari mana eksekusi kode meminta atau menahan kunci. Saat Beberapa Kumpulan Hasil Aktif (MARS) dinonaktifkan, nilai BatchID adalah 0. Ketika MARS diaktifkan, nilai untuk batch aktif adalah 1 hingga n. Jika tidak ada batch aktif dalam sesi, BatchID adalah 0.

Mode Menentukan jenis kunci untuk sumber daya tertentu yang diminta, diberikan, atau ditunggu oleh utas. Mode dapat berupa Intent Shared (IS), Shared (S), Update (U), Intent Exclusive (IX), Shared with Intent Exclusive (SIX), dan Exclusive (X).

Line # (line untuk bendera pelacakan 1222). Mencantumkan nomor baris dalam batch pernyataan saat ini yang sedang dijalankan ketika kebuntuan terjadi.

Input Buf (inputbuf untuk bendera pelacakan 1222). Mencantumkan semua pernyataan dalam batch saat ini.		 Node Mewakili nomor entri dalam rantai kebuntuan.

Lists Pemilik kunci dapat menjadi bagian dari daftar ini:

Grant List Menghitung pemilik sumber daya saat ini.

Convert List Menghitung pemilik saat ini yang mencoba mengonversi kunci mereka ke tingkat yang lebih tinggi.

Wait List Menghitung permintaan kunci baru saat ini untuk sumber daya.

Statement Type Menjelaskan jenis pernyataan (SELECT, INSERT, UPDATE, atau DELETE) yang diizinkan oleh utas.

Victim Resource Owner Menentukan utas yang terlibat yang dipilih oleh Mesin Database sebagai korban untuk memutus siklus kebuntuan. Thread yang dipilih dan semua konteks eksekusinya dihentikan.

Next Branch Mewakili dua konteks eksekusi atau lebih dari SPID yang sama yang terlibat dalam siklus kebuntuan.		 deadlock victim Mewakili alamat memori fisik tugas (lihat sys.dm_os_tasks) yang dipilih sebagai korban kebuntuan. Nilainya mungkin nol dalam kasus kebuntuan yang belum terselesaikan.

executionstack Mewakili tumpukan panggilan Transact-SQL yang sedang dijalankan pada saat kebuntuan terjadi.

priority Mewakili prioritas kebuntuan.

logused Ruang log yang digunakan oleh tugas.

owner id ID transaksi yang memiliki kontrol atas permintaan.

status Status tugas. Untuk informasi selengkapnya, lihat sys.dm_os_tasks.

waitresource Sumber daya yang diperlukan oleh tugas.

waittime Waktu dalam milidetik menunggu sumber daya.

schedulerid Penjadwal yang terkait dengan tugas ini. Lihat sys.dm_os_schedulers.

hostname Nama stasiun kerja.

isolationlevel Tingkat isolasi transaksi saat ini.

Xactid ID transaksi yang memiliki kontrol atas permintaan.

currentdb ID database.

lastbatchstarted Terakhir kali proses klien memulai eksekusi batch.

lastbatchcompleted Terakhir kali proses klien menyelesaikan eksekusi batch.

clientoption1 dan clientoption2 Opsi yang ditetapkan pada sesi ini. Nilai-nilai ini adalah bitmask yang mewakili opsi yang biasanya dikontrol oleh SET pernyataan seperti SET NOCOUNT dan SET XACTABORT. Untuk informasi selengkapnya, lihat @@OPTIONS.

associatedObjectId Mewakili ID HoBT (heap atau B-tree).
Atribut sumber daya RID mengidentifikasi satu baris dalam tabel tempat kunci ditahan atau diminta. RID diwakili sebagai RID: db_id:file_id:page_no:row_no. Contohnya,RID: 6:1:20789:0.

OBJECT mengidentifikasi tabel tempat kunci dipegang atau diminta. OBJECT direpresentasikan sebagai OBJECT: db_id:object_id. Contohnya,TAB: 6:2009058193.

KEY Mengidentifikasi rentang kunci dalam indeks tempat kunci ditahan atau diminta. KEY direpresentasikan sebagai KEY: db_id:hobt_id (nilai hash kunci indeks). Contohnya,KEY: 6:72057594057457664 (350007a4d329).

PAG Mengidentifikasi sumber daya halaman tempat kunci ditahan atau diminta. PAG direpresentasikan sebagai PAG: db_id:file_id:page_no. Contohnya,PAG: 6:1:20789.

EXT Mengidentifikasi struktur yang luas. EXT direpresentasikan sebagai EXT: db_id:file_id:extent_no. Contohnya,EXT: 6:1:9.

DB Mengidentifikasi kunci database. DB diwakili dengan salah satu cara berikut:

DB: db_id

DB: db_id[BULK-OP-DB], yang mengidentifikasi kunci database yang diambil oleh cadangan database.

DB: db_id[BULK-OP-LOG], yang mengidentifikasi kunci yang diambil oleh cadangan log.

APP Mengidentifikasi kunci aplikasi. APP direpresentasikan sebagai APP: lock_resource. Contohnya,APP: Formf370f478.

METADATA Mewakili sumber daya metadata yang terlibat dalam kebuntuan. Karena METADATA memiliki banyak sub-sumber daya, nilai yang dikembalikan tergantung pada sub-sumber daya yang telah mengalami kebuntuan. Misalnya, METADATA.USER_TYPE mengembalikan user_type_id = *integer_value*. Untuk informasi selengkapnya tentang METADATA sumber daya dan sub sumber daya, lihat sys.dm_tran_locks.

HOBT Mewakili timbunan atau pohon B yang terlibat dalam kebuntuan.		 Tidak ada yang eksklusif untuk bendera pelacakan ini. Tidak ada yang eksklusif untuk bendera pelacakan ini.

Contoh bendera pelacakan 1204

Contoh berikut menunjukkan output saat bendera pelacakan 1204 diaktifkan. Dalam hal ini, tabel di Node 1 adalah tumpukan tanpa indeks, dan tabel di Node 2 adalah tumpukan dengan indeks non-klusster. Kunci indeks di Node 2 sedang diperbarui ketika kebuntuan terjadi.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Contoh bendera pelacakan 1222

Contoh berikut menunjukkan output saat bendera pelacakan 1222 diaktifkan. Dalam hal ini, satu tabel adalah tumpukan tanpa indeks, dan tabel lainnya adalah tumpukan dengan indeks nonclustered. Dalam tabel kedua, kunci indeks sedang diperbarui saat kebuntuan terjadi.

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
   transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
   sbid=0 ecid=0 priority=0 transcount=2
   lastbatchstarted=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-05T11:22:42.733
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310444 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380
   waitresource=KEY: 6:72057594057457664 (350007a4d329)
   waittime=5015 ownerId=310462 transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-05T11:22:44.077
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310462 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Peristiwa grafik kebuntuan Profiler

SQL Profiler memiliki peristiwa yang menyajikan penggambaran grafis tugas dan sumber daya yang terlibat dalam kebuntuan. Contoh berikut menunjukkan output dari SQL Profiler saat peristiwa grafik kebuntuan diaktifkan.

Fitur SQL Profiler dan SQL Trace tidak digunakan lagi dan digantikan oleh Extended Events. Extended Events memiliki overhead performa yang lebih kecil, dan lebih dapat dikonfigurasi daripada SQL Trace. Pertimbangkan untuk menggunakan peristiwa kebuntuan Extended Events alih-alih melacak kebuntuan di SQL Profiler.

Cuplikan layar dari SSMS grafik kebuntuan visual dari jejak SQL.

Untuk informasi selengkapnya tentang peristiwa kebuntuan, lihat Kunci:Kelas Peristiwa Kebuntuan. Untuk informasi selengkapnya tentang grafik kebuntuan SQL Profiler, lihat Menyimpan grafik kebuntuan (SQL Server Profiler).

Extended Events menyediakan kelas peristiwa SQL Trace yang setara. Untuk informasi selengkapnya, lihat Menampilkan Persamaan Peristiwa yang Diperluas ke Kelas Peristiwa Pelacakan SQL. Extended Events direkomendasikan melalui SQL Trace.

Menangani kebuntuan

Ketika instans Mesin Database memilih transaksi sebagai korban deadlock, instans tersebut menghentikan batch saat ini, menggulir balik transaksi, dan mengirimkan kesalahan 1205 ke aplikasi. Pesan yang dikembalikan disusun sebagai berikut:

Your transaction (process ID #...) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Karena setiap aplikasi yang mengirimkan kueri Transact-SQL dapat dipilih sebagai korban kebuntuan, aplikasi harus memiliki penanganan kesalahan yang dapat menangani kesalahan 1205. Jika aplikasi tidak menangani kesalahan, aplikasi dapat melanjutkan tidak menyadari bahwa transaksinya telah digulung balik.

Menerapkan handler kesalahan yang menangkap kesalahan 1205 memungkinkan aplikasi untuk menangani kebuntuan dan mengambil tindakan perbaikan (misalnya, mengirim ulang kueri yang terlibat dalam kebuntuan secara otomatis).

Aplikasi harus dijeda sebentar sebelum mengirimkan ulang kuerinya. Ini memberi transaksi lain yang terlibat dalam kebuntuan kesempatan untuk menyelesaikan dan melepaskan kuncinya. Mengacak durasi jeda meminimalkan kemungkinan kebuntuan yang terjadi kembali saat kueri yang dikirim ulang meminta kuncinya. Misalnya, handler kesalahan mungkin dikodekan untuk menjeda selama durasi acak antara satu dan tiga detik.

Tangani dengan TRY... MENANGKAP

Anda dapat menggunakan TRY... CATCH untuk menangani kebuntuan. Kesalahan 1205 dapat ditangkap oleh blok CATCH.

Untuk informasi selengkapnya, lihat Menangani Kebuntuan.

Meminimalkan kebuntuan

Meskipun kebuntuan tidak dapat sepenuhnya dihindari, mengikuti konvensi pengkodean tertentu dapat meminimalkan kemungkinan terjadinya kebuntuan. Meminimalkan kebuntuan dapat meningkatkan throughput transaksi dan mengurangi overhead sistem, karena lebih sedikit transaksi:

  • Digulung balik, membatalkan semua pekerjaan yang dilakukan oleh transaksi.
  • Dikirim ulang oleh aplikasi karena digulung balik saat mengalami kebuntuan.

Untuk membantu meminimalkan kebuntuan:

  • Akses objek dalam urutan yang sama.
  • Hindari interaksi pengguna dalam transaksi.
  • Pastikan transaksi tetap singkat dan dalam satu batch.
  • Hindari tingkat isolasi yang lebih tinggi seperti REPEATABLE READ dan SERIALIZABLE ketika tidak diperlukan.
  • Gunakan tingkat isolasi berbasis penerapan versi baris.
    • Aktifkan opsi database READ_COMMITTED_SNAPSHOT untuk menggunakan versi baris untuk transaksi menggunakan tingkat isolasi READ COMMITTED.
    • Gunakan transaksi isolasi rekam jepret.
  • Gunakan koneksi terikat.

Mengakses objek dalam urutan yang sama

Jika semua transaksi bersamaan mengakses objek dalam urutan yang sama, kebuntuan cenderung tidak terjadi. Misalnya, jika dua transaksi bersamaan mendapatkan kunci pada Supplier tabel dan kemudian pada Part tabel, satu transaksi diblokir pada Supplier tabel sampai transaksi lainnya selesai. Setelah transaksi pertama diterapkan atau digulung balik, yang kedua berlanjut, dan kebuntuan tidak terjadi. Menggunakan prosedur tersimpan untuk semua modifikasi data dapat menstandarkan urutan mengakses objek.

Diagram kebuntuan.

Hindari interaksi pengguna dalam transaksi

Hindari transaksi yang mencakup interaksi pengguna, karena kecepatan batch yang berjalan tanpa intervensi pengguna jauh lebih cepat daripada kecepatan pengguna harus merespons kueri secara manual, seperti membalas permintaan parameter yang diminta oleh aplikasi. Hal ini akan menurunkan throughput sistem karena kunci apa pun yang dipegang oleh transaksi hanya dilepaskan ketika transaksi dilakukan atau digulung balik. Bahkan jika kebuntuan tidak terjadi, transaksi lain yang mengakses sumber daya yang sama diblokir saat menunggu transaksi selesai.

Menjaga transaksi tetap pendek dan dalam satu batch

Kebuntuan biasanya terjadi ketika beberapa transaksi jangka panjang dijalankan secara bersamaan dalam database yang sama. Semakin lama transaksi, semakin lama kunci eksklusif atau pembaruan ditahan, memblokir aktivitas lain dan menyebabkan kemungkinan situasi kebuntuan.

Mengelola transaksi dalam satu batch meminimalkan putaran jaringan selama proses transaksi, mengurangi kemungkinan penundaan dalam menyelesaikan proses transaksi karena pemrosesan klien.

Hindari tingkat isolasi yang lebih tinggi

Tentukan apakah transaksi dapat berjalan pada tingkat isolasi yang lebih rendah. Menggunakan READ COMMITTED memungkinkan transaksi untuk membaca data yang sebelumnya dibaca (tetapi tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi selesai. READ COMMITTED memegang kunci bersama untuk durasi yang lebih pendek daripada tingkat isolasi yang lebih tinggi, seperti SERIALIZABLE. Ini mengurangi ketidakcocokan kunci.

Menggunakan tingkat isolasi berbasis penerapan versi baris

READ_COMMITTED_SNAPSHOT Saat opsi database diatur ON, transaksi yang berjalan di bawah tingkat isolasi READ COMMITTED menggunakan versi baris, bukan menggunakan kunci bersama, selama operasi baca.

Petunjuk / Saran

Microsoft merekomendasikan tingkat isolasi berbasis pembuatan versi baris READ COMMITTED untuk semua aplikasi, kecuali jika suatu aplikasi bergantung pada perilaku pemblokiran tingkat isolasi berbasis kunci READ COMMITTED.

Isolasi rekam jepret juga menggunakan penerapan versi baris, yang tidak menggunakan kunci bersama selama operasi baca. Sebelum transaksi dapat berjalan di bawah isolasi rekam jepret, ALLOW_SNAPSHOT_ISOLATION opsi database harus diatur ON.

Gunakan tingkat isolasi berbasis penerapan versi baris untuk meminimalkan kebuntuan yang dapat terjadi antara operasi baca dan tulis.

Menggunakan koneksi terikat

Dengan menggunakan koneksi terikat, dua koneksi atau lebih yang dibuka oleh aplikasi yang sama dapat bekerja sama satu sama lain. Setiap kunci yang diperoleh oleh koneksi sekunder disimpan seolah-olah mereka diperoleh oleh koneksi utama, dan sebaliknya. Oleh karena itu, mereka tidak saling memblokir.

Menyebabkan kebuntuan

Anda mungkin perlu menyebabkan kebuntuan untuk tujuan pembelajaran atau demonstrasi.

Contoh berikut berfungsi dalam AdventureWorksLT2019 database sampel dengan skema dan data default saat READ_COMMITTED_SNAPSHOT telah diaktifkan. Untuk mengunduh sampel ini, kunjungi database sampel AdventureWorks.

Misalnya yang menyebabkan kebuntuan saat penguncian yang dioptimalkan diaktifkan, lihat Penguncian dan kebuntuan yang dioptimalkan.

Untuk menyebabkan kebuntuan, Anda perlu menyambungkan dua sesi ke AdventureWorksLT2019 database. Kami menyebut sesi ini sebagai Sesi A dan Sesi B. Anda dapat membuat dua sesi ini dengan membuat dua jendela kueri di SQL Server Management Studio (SSMS).

Di Sesi A, jalankan batch berikut. Kode ini memulai transaksi eksplisit dan menjalankan pernyataan yang memperbarui SalesLT.Product tabel. Untuk melakukan ini, transaksi memperoleh kunci pembaruan (U) pada baris yang memenuhi syarat dalam tabel SalesLT.Product yang kemudian dikonversi ke kunci eksklusif (X). Kita membiarkan transaksi terbuka.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Sekarang, di Sesi B, jalankan batch berikut. Kode ini tidak secara eksplisit memulai transaksi. Sebaliknya, kode ini beroperasi dalam mode transaksi penerapan otomatis. Pernyataan ini memperbarui tabel SalesLT.ProductDescription. Pembaruan mengambil kunci pembaruan (U) pada baris yang memenuhi syarat dalam tabel SalesLT.ProductDescription. Kueri bergabung dengan tabel lain, termasuk tabel SalesLT.Product.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Untuk menyelesaikan pembaruan ini, Sesi B memerlukan kunci bersama (S) pada baris dalam tabel SalesLT.Product, termasuk baris yang dikunci oleh Sesi A. Sesi B diblokir pada SalesLT.Product.

Kembali ke Sesi A. Jalankan pernyataan berikut UPDATE . Pernyataan ini dijalankan sebagai bagian dari transaksi yang sudah dibuka sebelumnya.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

Pernyataan pembaruan kedua dalam Sesi A diblokir oleh Sesi B pada SalesLT.ProductDescription.

Sesi A dan Sesi B sekarang saling memblokir satu sama lain. Tidak ada transaksi yang dapat dilanjutkan, karena masing-masing membutuhkan sumber daya yang dikunci oleh yang lain.

Setelah beberapa detik, monitor kebuntuan mengidentifikasi bahwa transaksi di Sesi A dan Sesi B saling memblokir satu sama lain, dan bahwa tidak dapat membuat kemajuan. Anda melihat kebuntuan terjadi, dengan Sesi A dipilih sebagai korban kebuntuan. Sesi B berhasil diselesaikan. Pesan kesalahan muncul di jendela kueri Sesi A dengan teks yang mirip dengan contoh berikut:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Jika kebuntuan tidak dimunculkan, verifikasi bahwa READ_COMMITTED_SNAPSHOT itu diaktifkan dalam database sampel Anda. Kebuntuan dapat terjadi dalam konfigurasi database apa pun, tetapi contoh ini mengharuskan hal READ_COMMITTED_SNAPSHOT tersebut diaktifkan.

Anda dapat melihat detail kebuntuan ring_buffer dalam system_health target sesi peristiwa, yang diaktifkan dan aktif secara default di SQL Server dan Azure SQL Managed Instance. Pertimbangkan kueri berikut:

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

Anda bisa menampilkan XML di kolom di Deadlock_XML dalam SSMS, dengan memilih sel yang muncul sebagai hyperlink. Simpan output ini sebagai .xdl file, tutup, lalu buka .xdl kembali file di SSMS untuk grafik kebuntuan visual. Grafik kebuntuan akan terlihat seperti gambar berikut.

Cuplikan layar grafik kebuntuan visual dalam file .xdl di SQL Server Management Studio.

Penguncian dan kebuntuan yang dioptimalkan

Dengan penguncian yang dioptimalkan, penguncian kunci pada halaman dan baris tidak dilakukan hingga transaksi berakhir. Mereka dirilis segera setelah baris diperbarui. Selain itu, jika READ_COMMITTED_SNAPSHOT diaktifkan, kunci pembaruan (U) tidak digunakan. Akibatnya, kemungkinan kebuntuan berkurang.

Contoh sebelumnya tidak menyebabkan kebuntuan saat penguncian yang dioptimalkan diaktifkan karena bergantung pada kunci pembaruan (U).

Contoh berikut dapat digunakan untuk menyebabkan kebuntuan pada database yang memiliki penguncian yang dioptimalkan.

Pertama, buat contoh tabel dan tambahkan data.

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);

Batch T-SQL berikut, dijalankan secara berurutan dalam dua sesi terpisah, membuat kebuntuan.

Dalam sesi 1:

BEGIN TRANSACTION xactA;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

Dalam sesi 2:

BEGIN TRANSACTION xactB;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

Dalam sesi 1:

UPDATE t2
    SET b = b + 100
WHERE a = 2;

Dalam sesi 2:

UPDATE t2
    SET b = b + 20
WHERE a = 1;

Dalam hal ini, setiap session memegang kunci eksklusif (X) pada sumber daya ID transaksi (TID) sendiri, dan menunggu kunci bersama (S) pada TID lain, yang mengakibatkan deadlock.

Singkatan laporan kebuntuan berikut berisi elemen dan atribut khusus untuk penguncian yang telah dioptimalkan. Di bawah setiap sumber daya dalam laporan <resource-list>kebuntuan , setiap <xactlock> elemen melaporkan sumber daya yang mendasar dan informasi kunci TID dari setiap anggota kebuntuan.

<deadlock>
 <victim-list>
  <victimProcess id="process12994344c58" />
 </victim-list>
 <process-list>
  <process id="process12994344c58" taskpriority="0" logused="272" waitresource="XACT: 23:2476:0 KEY: 23:72057594049593344 (8194443284a0)" waittime="447" ownerId="3234906" transactionname="xactA" lasttranstarted="2025-10-08T21:36:34.063" XDES="0x12984ba0480" lockMode="S" schedulerid="2" kpid="204928" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:40.857" lastbatchcompleted="2025-10-08T21:36:34.063" lastattention="2025-10-08T21:36:11.340" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234906" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 20
WHERE a = 1;
   </inputbuf>
  </process>
  <process id="process1299c969828" taskpriority="0" logused="272" waitresource="XACT: 23:2477:0 KEY: 23:72057594049593344 (61a06abd401c)" waittime="3083" ownerId="3234886" transactionname="xactB" lasttranstarted="2025-10-08T21:36:30.303" XDES="0x12995c84480" lockMode="S" schedulerid="2" kpid="63348" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:38.223" lastbatchcompleted="2025-10-08T21:36:30.303" lastattention="1900-01-01T00:00:00.303" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234886" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 100
WHERE a = 2;
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <xactlock xdesIdLow="2476" xdesIdHigh="0" dbid="23" id="lock1299fa06c00" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process1299c969828" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process12994344c58" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
  <xactlock xdesIdLow="2477" xdesIdHigh="0" dbid="23" id="lock129940b2380" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process12994344c58" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1299c969828" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
 </resource-list>
</deadlock>

Konten terkait

  • Last updated on