Panduan kebuntuan

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

Untuk informasi selengkapnya tentang identifikasi dan pencegahan kebuntuan di Azure SQL Database, lihat Menganalisis dan mencegah kebuntuan di Azure SQL Database.

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 akan menunggu selamanya kecuali kebuntuan rusak oleh proses eksternal. monitor kebuntuan Mesin Database SQL Server secara berkala memeriksa tugas yang mengalami 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 SQL Server 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 akan selesai dan melepaskan kunci, dan kemudian transaksi yang meminta akan diberikan kunci dan melanjutkan. Kebuntuan diselesaikan segera, sedangkan pemblokiran dapat, secara teori, bertahan tanpa batas waktu. Kebuntuan terkadang disebut pelukan mematikan.

Kebuntuan adalah kondisi yang 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 instans Mesin Database SQL Server, sesi dapat mengalami kebuntuan saat memperoleh sumber daya non-database, seperti memori atau utas.

Diagram showing a transaction deadlock.

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.

Kebuntuan juga dapat terjadi ketika tabel dipartisi dan LOCK_ESCALATION pengaturan ALTER TABLE diatur ke AUTO. Ketika LOCK_ESCALATION diatur ke OTOMATIS, konkurensi meningkat dengan memungkinkan Mesin Database SQL Server mengunci partisi tabel di tingkat HoBT alih-alih di 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; meskipun pengaturan ini akan mengurangi konkurensi dengan memaksa pembaruan besar ke partisi untuk menunggu kunci tabel.

Mendeteksi dan mengakhiri 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. Grafik berikut menyajikan tampilan tingkat tinggi dari status kebuntuan di mana:

  • 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.

Diagram showing the tasks in a deadlock state.

Mesin Database SQL Server secara otomatis mendeteksi siklus kebuntuan dalam SQL Server. Mesin Database SQL Server memilih salah satu sesi sebagai korban kebuntuan dan transaksi saat ini dihentikan 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 berbagai sumber daya. Jenis sumber daya berikut dapat menyebabkan pemblokiran yang dapat mengakibatkan kebuntuan.

  • Kunci. 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 sedang 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, kebuntuan akan dihasilkan. Misalnya, sesi S1 memulai transaksi dan memperoleh kunci bersama (S) pada baris r1 lalu tidur. Sesi aktif yang berjalan pada semua utas pekerja yang tersedia mencoba memperoleh 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.

  • Memori. Ketika permintaan bersamaan menunggu peruntukan 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, dan ini 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, SQL Server menentukan tingkat paralelisme, atau jumlah utas pekerja, 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 Kumpulan Hasil Aktif (MARS).

    • 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, pertama-tama akan 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 SQL Server 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 of the logical flow of a stored procedure in MARS.

Deteksi kebuntuan

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

  • Interval default adalah 5 detik.
  • Jika utas monitor kunci menemukan kebuntuan, interval deteksi kebuntuan akan turun dari 5 detik hingga serendah 100 milidetik tergantung pada frekuensi kebuntuan.
  • Jika utas monitor kunci berhenti menemukan kebuntuan, Mesin Database SQL Server meningkatkan interval antara pencarian menjadi 5 detik.
  • Jika kebuntuan baru saja terdeteksi, diasumsikan bahwa utas berikutnya yang harus menunggu kunci memasuki siklus kebuntuan. Beberapa kunci pertama menunggu setelah kebuntuan terdeteksi akan segera memicu pencarian kebuntuan daripada menunggu interval deteksi kebuntuan berikutnya. Misalnya, jika interval saat ini adalah 5 detik, dan kebuntuan baru saja terdeteksi, penantian kunci berikutnya akan segera memulai detektor kebuntuan. Jika tunggu kunci ini adalah bagian dari kebuntuan, kunci akan langsung terdeteksi daripada selama pencarian kebuntuan berikutnya.

Mesin Database SQL Server 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 SQL Server mengakhiri kebuntuan dengan memilih salah satu utas sebagai korban kebuntuan. Mesin Database SQL Server mengakhiri batch saat ini yang dijalankan untuk utas, mengembalikan transaksi korban kebuntuan, dan mengembalikan 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 korban kebuntuan 1205 mencatat informasi tentang utas dan sumber daya yang terlibat dalam kebuntuan dalam log kesalahan.

Secara default, Mesin Database SQL Server memilih sebagai korban kebuntuan sesi yang menjalankan transaksi yang paling murah untuk digulung balik. Atau, pengguna dapat menentukan prioritas sesi dalam situasi kebuntuan menggunakan pernyataan .SET DEADLOCK_PRIORITY DEADLOCK_PRIORITY dapat diatur ke RENDAH, NORMAL, atau TINGGI, atau atau dapat diatur ke nilai bilangan bulat apa pun dalam rentang (-10 hingga 10). Prioritas kebuntuan default ke NORMAL. Jika dua sesi memiliki prioritas kebuntuan yang berbeda, sesi dengan prioritas lebih rendah dipilih sebagai korban kebuntuan. Jika kedua sesi memiliki prioritas kebuntuan yang sama, sesi dengan transaksi yang paling murah untuk digulung balik dipilih. Jika sesi yang terlibat dalam siklus kebuntuan memiliki prioritas kebuntuan yang sama dan biaya yang sama, korban dipilih secara acak.

Saat bekerja dengan 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 Database SQL Server menyediakan alat pemantauan dalam bentuk system_health sesi xEvent, dua bendera pelacakan, dan peristiwa grafik kebuntuan di SQL Profiler.

Catatan

Bagian ini berisi informasi tentang peristiwa yang diperluas, bendera pelacakan, dan jejak, tetapi peristiwa kebuntuan diperluas adalah metode yang direkomendasikan untuk menangkap informasi kebuntuan.

Kejadian yang diperluas kebuntuan

Dimulai dengan SQL Server 2012 (11.x), xml_deadlock_report Extended Event (xEvent) harus digunakan alih-alih kelas peristiwa Grafik kebuntuan di SQL Trace atau SQL Profiler.

Juga dimulai dengan SQL Server 2012 (11.x), ketika kebuntuan terjadi, system_health sesi sudah mengambil semua xml_deadlock_report xEvent yang berisi grafik kebuntuan. Karena sesi system_health diaktifkan secara default, tidak diharuskan bahwa sesi xEvent terpisah dikonfigurasi untuk menangkap informasi kebuntuan. Tidak diperlukan tindakan tambahan untuk mengambil informasi kebuntuan yang berisi xml_deadlock_report xEvent.

Grafik deadlock yang direkam biasanya memiliki tiga node yang berbeda:

  • daftar korban. Pengidentifikasi proses korban kebuntuan.
  • daftar proses. Informasi tentang semua proses yang terlibat dalam kebuntuan.
  • daftar sumber daya. Informasi tentang sumber daya yang terlibat dalam kebuntuan.

Membuka system_health file sesi atau buffer cincin, jika xml_deadlock_report xEvent direkam, Management Studio menyajikan penggambaran grafis tugas dan sumber daya yang terlibat dalam kebuntuan, seperti yang terlihat dalam contoh berikut:

A screenshot from SSMS of a XEvent Deadlock Graph visual diagram.

Kueri berikut ini bisa menampilkan semua peristiwa kebuntuan yang system_health diambil oleh buffer cincin sesi:

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    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 [Date] DESC;

Berikut adalah hasil yang ditetapkan.

A screenshot from SSMS of the system_health xEvent query result.

Contoh berikut menunjukkan output, setelah memilih tautan pertama dari hasil di atas:

<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>

Untuk informasi selengkapnya, lihat Menggunakan Sesi system_health

Bendera Jejak 1204 dan Bendera Pelacakan 1222

Ketika kebuntuan terjadi, Lacak Bendera 1204 dan Lacak Bendera 1222 mengembalikan informasi yang diambil di log kesalahan SQL Server. Lacak Bendera 1204 melaporkan informasi kebuntuan yang diformat oleh setiap simpul yang terlibat dalam kebuntuan. Lacak Bendera 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.

Penting

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 Kejadian Yang Diperluas Kebuntuan untuk mengambil informasi yang diperlukan.

Selain menentukan properti Bendera Jejak 1204 dan 1222, tabel berikut ini juga menunjukkan kesamaan dan perbedaan.

Properti Bendera Jejak 1204 dan Bendera Pelacakan 1222 Lacak Bendera 1204 saja Lacak Bendera 1222 saja
Format output 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 identik dengan simpul di Bendera Pelacakan 1204.
Mengidentifikasi atribut SPID:<x> ECID:<x>. Mengidentifikasi utas ID proses sistem 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 subthreads untuk SPID yang sama.

BatchID (terlarang 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 IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), dan X (Exclusive).

Garis # (garis untuk Bendera Pelacakan 1222). Mencantumkan nomor baris dalam batch pernyataan saat ini yang sedang dijalankan ketika kebuntuan terjadi.

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

Daftar. Pemilik kunci dapat menjadi bagian dari daftar ini:

Daftar Pemberian. Menghitung pemilik sumber daya saat ini.

Konversi Daftar. Menghitung pemilik saat ini yang mencoba mengonversi kunci mereka ke tingkat yang lebih tinggi.

Daftar Tunggu. Menghitung permintaan kunci baru saat ini untuk sumber daya.

Jenis Pernyataan. Menjelaskan jenis pernyataan DML (SELECT, INSERT, UPDATE, atau DELETE) tempat utas memiliki izin.

Pemilik Sumber Daya Korban. Menentukan utas yang berpartisipasi yang dipilih SQL Server sebagai korban untuk memutus siklus kebuntuan. Utas yang dipilih dan semua subthread yang ada dihentikan.

Cabang Berikutnya. Mewakili dua subthread atau lebih dari SPID yang sama yang terlibat dalam siklus kebuntuan.		 korban kebuntuan. Mewakili alamat memori fisik tugas (lihat sys.dm_os_tasks (Transact-SQL)) yang dipilih sebagai korban kebuntuan. Mungkin 0 (nol) dalam kasus kebuntuan yang belum terselesaikan. Tugas yang digulung balik tidak dapat dipilih sebagai korban kebuntuan.

tumpukan eksekusi. Mewakili kode Transact-SQL yang sedang dijalankan pada saat kebuntuan terjadi.

prioritas. Mewakili prioritas kebuntuan. Dalam kasus tertentu, Mesin Database SQL Server mungkin memilih untuk mengubah prioritas kebuntuan dalam durasi singkat untuk mencapai konkurensi yang lebih baik.

logused. Ruang log yang digunakan oleh tugas.

id pemilik. ID transaksi yang memiliki kontrol atas permintaan.

status. Status tugas. Ini adalah salah satu nilai berikut:

>>tertunda. Menunggu utas pekerja.

>>dapat dijalankan. Siap untuk menjalankan tetapi menunggu kuantum.

>>berjalan. Saat ini berjalan pada penjadwal.

>>ditangguhkan. Eksekusi ditangguhkan.

>>selesai. Tugas telah selesai.

>>spinloop. Menunggu spinlock menjadi bebas.

sumber daya waitresource. Sumber daya yang diperlukan oleh tugas.

waktu tunggu. Waktu dalam milidetik menunggu sumber daya.

schedulerid. Penjadwal yang terkait dengan tugas ini. Lihat sys.dm_os_schedulers (Transact-SQL).

nama host. 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.

lastbatch selesai. Terakhir kali proses klien menyelesaikan eksekusi batch.

clientoption1 dan clientoption2. Atur opsi pada koneksi klien ini. Ini adalah bitmask yang mencakup informasi tentang opsi yang biasanya dikontrol oleh pernyataan SET seperti SET NOCOUNT dan SET XACTABORT.

associatedObjectId. Mewakili ID HoBT (heap atau B-tree).
Atribut sumber daya BERESKAN INI. 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.

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

KUNCI. 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 diwakili 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 database cadangan.

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

APP. Mengidentifikasi kunci yang diambil oleh sumber daya 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 sumber daya dan sub sumber daya METADATA, lihat sys.dm_tran_locks (Transact-SQL).

INI 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 Lacak Bendera 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

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

Penting

SQL Profiler membuat jejak, yang tidak digunakan lagi pada tahun 2016 dan digantikan oleh Extended Events. Extended Events memiliki overhead performa yang jauh lebih sedikit dan jauh lebih dapat dikonfigurasi daripada jejak. Pertimbangkan untuk menggunakan peristiwa kebuntuan Extended Events alih-alih jejak.

A screenshot from SSMS of the visual deadlock graph from a SQL trace.

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

Ada yang setara untuk kelas peristiwa SQL Trace di Extended Events, lihat Extended Events Equivalents to SQL Trace Event Classes. Peristiwa yang diperluas direkomendasikan melalui Jejak SQL.

Menangani kebuntuan

Ketika instans Mesin Database SQL Server memilih transaksi sebagai korban kebuntuan, instans mengakhiri batch saat ini, mengembalikan transaksi, dan mengembalikan pesan kesalahan 1205 ke aplikasi.

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

Karena aplikasi apa pun yang mengirimkan kueri Transact-SQL dapat dipilih sebagai korban kebuntuan, aplikasi harus memiliki penangan kesalahan yang dapat menjebak pesan kesalahan 1205. Jika aplikasi tidak menjebak kesalahan, aplikasi dapat melanjutkan tidak menyadari bahwa transaksinya telah digulung balik dan kesalahan dapat terjadi.

Menerapkan penanganan kesalahan yang menjebak pesan kesalahan 1205 memungkinkan aplikasi untuk menangani situasi kebuntuan dan mengambil tindakan perbaikan (misalnya, secara otomatis mengirim ulang kueri yang terlibat dalam kebuntuan). Dengan mengirimkan ulang kueri secara otomatis, pengguna tidak perlu tahu bahwa kebuntuan terjadi.

Aplikasi harus dijeda sebentar sebelum mengirimkan ulang kuerinya. Ini memberi kesempatan kepada transaksi lain yang mengalami kebuntuan untuk menyelesaikan dan melepaskan kuncinya yang membentuk bagian dari siklus kebuntuan. Ini meminimalkan kemungkinan kebuntuan terjadi kembali saat kueri yang dikirim ulang meminta kuncinya.

Meminimalkan kebuntuan

Meskipun kebuntuan tidak dapat sepenuhnya dihindari, mengikuti konvensi pengkodian tertentu dapat meminimalkan kemungkinan menghasilkan 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. - Jaga transaksi tetap singkat dan dalam satu batch.
  • Gunakan tingkat isolasi yang lebih rendah.
  • Gunakan tingkat isolasi berbasis penerapan versi baris.
    • Atur READ_COMMITTED_SNAPSHOT opsi database aktif untuk mengaktifkan transaksi berkomitmen baca untuk menggunakan penerapan versi baris.
    • Gunakan isolasi salinan bayangan.
  • 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.

A diagram of a deadlock.

Hindari interaksi pengguna dalam transaksi

Hindari menulis transaksi yang mencakup interaksi pengguna, karena kecepatan batch yang berjalan tanpa intervensi pengguna jauh lebih cepat daripada kecepatan yang melibatkan pengguna untuk merespons kueri secara manual, seperti membalas permintaan parameter yang diminta oleh aplikasi. Misalnya, jika transaksi menunggu input pengguna dan pengguna pergi makan siang atau bahkan di rumah untuk akhir pekan, pengguna menunda transaksi dari penyelesaian. Hal ini akan menurunkan throughput sistem karena kunci apa pun yang dipegang oleh transaksi hanya dilepaskan ketika transaksi dilakukan atau digulung balik. Bahkan jika situasi kebuntuan tidak muncul, 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.

Melakukan transaksi dalam satu batch meminimalkan bolak-balik jaringan selama transaksi, mengurangi kemungkinan penundaan dalam menyelesaikan transaksi dan melepaskan kunci.

Untuk informasi selengkapnya tentang kunci pembaruan, lihat Panduan penguncian transaksi dan penerapan versi baris.

Menggunakan tingkat isolasi yang lebih rendah

Tentukan apakah transaksi dapat berjalan pada tingkat isolasi yang lebih rendah. Menerapkan penerapan baca memungkinkan transaksi untuk membaca data yang sebelumnya dibaca (tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi pertama selesai. Menggunakan tingkat isolasi yang lebih rendah, seperti penerapan pembacaan, menyimpan kunci bersama dengan durasi yang lebih singkat daripada tingkat isolasi yang lebih tinggi, seperti serializeable. Ini mengurangi ketidakcocokan penguncian.

Menggunakan tingkat isolasi berbasis penerapan versi baris

READ_COMMITTED_SNAPSHOT Saat opsi database diatur AKTIF, transaksi yang berjalan di bawah tingkat isolasi berkomitmen baca menggunakan penerapan versi baris daripada kunci bersama selama operasi baca.

Catatan

Beberapa aplikasi mengandalkan penguncian dan pemblokiran perilaku isolasi yang diterapkan baca. Untuk aplikasi ini, beberapa perubahan diperlukan sebelum opsi ini dapat diaktifkan.

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.

Terapkan tingkat isolasi ini 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.

Menghentikan transaksi

Dalam skenario kebuntuan, transaksi korban secara otomatis dihentikan dan digulung balik. Tidak perlu menghentikan transaksi dalam skenario kebuntuan.

Menyebabkan kebuntuan

Catatan

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

Untuk menyebabkan kebuntuan, Anda harus menghubungkan dua sesi ke database AdventureWorksLT2019. Kita akan menyebut sesi ini sebagai Sesi A dan Sesi B. Anda dapat membuat dua sesi ini hanya dengan membuat dua jendela kueri di SQL Server Management Studio (SSMS).

Di Sesi A, jalankan T-SQL berikut. Kode ini memulai transaksi eksplisit dan menjalankan satu pernyataan yang memperbarui tabel SalesLT.Product. Untuk melakukan ini, transaksi memperoleh kunci pembaruan (U) pada satu baris di tabel SalesLT.Product yang dikonversi menjadi kunci eksklusif (X). Kita membiarkan transaksi terbuka.

BEGIN TRAN

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

Sekarang, di Sesi B, jalankan T-SQL berikut. Kode ini tidak secara eksplisit memulai transaksi. Sebaliknya, kode ini beroperasi dalam mode transaksi penerapan otomatis. Pernyataan ini memperbarui tabel SalesLT.ProductDescription. Pembaruan akan menghapus kunci pembaruan (U) pada 72 baris pada tabel SalesLT.ProductDescription. Kueri bergabung dengan tabel lain, termasuk tabel SalesLT.Product.

UPDATE SalesLT.ProductDescription SET Description = Description
    FROM SalesLT.ProductDescription as pd
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    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 di tabel SalesLT.Product, termasuk baris yang dikunci oleh Sesi A. Sesi B akan diblokir pada SalesLT.Product.

Kembali ke Sesi A. Jalankan pernyataan T-SQL berikut. Hal ini menjalankan pernyataan UPDATE kedua sebagai bagian dari transaksi terbuka.

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

Pernyataan pembaruan kedua di Sesi A akan diblokir oleh Sesi B di 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, pemantau kebuntuan akan mengidentifikasi bahwa transaksi di Sesi A dan Sesi B saling memblokir satu sama lain, dan keduanya tidak dapat membuat kemajuan. Anda akan melihat kebuntuan terjadi, dengan Sesi A dipilih sebagai korban kebuntuan. Sesi B akan berhasil diselesaikan. Pesan kesalahan akan muncul di Sesi A dengan teks yang mirip dengan yang berikut ini:

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 telah diaktifkan dalam database sampel Anda. Kebuntuan dapat terjadi dalam konfigurasi database apa pun, tetapi contoh ini mengharuskan READ_COMMITTED_SNAPSHOT diaktifkan.

Anda kemudian dapat melihat detail kebuntuan di target system_health ring_buffer sesi Extended Events, yang diaktifkan dan aktif secara default di SQL Server. Pertimbangkan kueri berikut:

WITH cteDeadLocks ([Deadlock_XML]) AS (
  SELECT [Deadlock_XML] = CAST(target_data AS 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 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks 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 akan muncul sebagai hyperlink. Simpan output ini sebagai .xdl file, tutup, lalu buka .xdl kembali file di SSMS untuk grafik kebuntuan visual. Grafik kebuntuan Anda akan terlihat seperti gambar berikut.

A screenshot of a visual deadlock graph in an .xdl file in SSMS.

Penguncian dan kebuntuan yang dioptimalkan

Berlaku untuk: Azure SQL Database

Penguncian yang dioptimalkan memperkenalkan metode yang berbeda untuk mengunci mekanika yang mengubah bagaimana kebuntuan yang melibatkan kunci TID eksklusif mungkin dilaporkan. Di bawah setiap sumber daya dalam laporan <resource-list>kebuntuan , setiap <xactlock> elemen melaporkan sumber daya yang mendasar dan informasi spesifik untuk kunci setiap anggota kebuntuan.

Pertimbangkan contoh berikut di mana penguncian yang dioptimalkan diaktifkan:

CREATE TABLE t2 
(a int PRIMARY KEY not null 
,b int null); 

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

Perintah TSQL berikut dalam dua sesi akan membuat kebuntuan pada tabel t2:

Dalam sesi 1:

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1;

Dalam sesi 2:

--session 2:
BEGIN TRAN bar 
UPDATE t2 SET b = b+ 10 WHERE a = 2;

Dalam sesi 1:

--session 1:
UPDATE t2 SET b = b + 100 WHERE a = 2;

Dalam sesi 2:

--session 2:
UPDATE t2 SET b = b + 20 WHERE a = 1;

Skenario pernyataan yang bersaing ini menghasilkan kebuntuan UPDATE . Dalam hal ini, sumber daya keylock, di mana setiap sesi memegang kunci X pada TID sendiri dan menunggu kunci S pada TID lain, mengakibatkan kebuntuan. XML berikut, yang diambil sebagai laporan kebuntuan, berisi elemen dan atribut khusus untuk penguncian yang dioptimalkan:

A screenshot of the XML of a deadlock report showing the UnderlyingResource nodes and keylock nodes specific to optimized locking.

Konten terkait