Panduan kebuntuan
Berlaku untuk: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Artikel ini membahas kebuntuan di Mesin Database SQL Server secara mendalam. Kebuntuan disebabkan oleh 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 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 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 instans Mesin Database SQL Server, sesi dapat mengalami kebuntuan saat memperoleh sumber daya non-database, seperti memori atau utas.
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 AUTO, 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. Namun, pengaturan ini 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.
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 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, hasil kebuntuan. 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 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, 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 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, itu 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:
Deteksi kebuntuan
Semua sumber daya yang tercantum di bagian Sumber Daya yang dapat membuat kebuntuan 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 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 terdeteksi, diasumsikan bahwa utas berikutnya yang harus menunggu kunci 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 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 LOW, , NORMALatau HIGH, atau sebagai alternatif 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 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 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
Di SQL Server 2012 (11.x) dan versi yang lebih baru, xml_deadlock_report Extended Event (XEvent) harus digunakan alih-alih kelas peristiwa grafik Kebuntuan di SQL Trace atau SQL Profiler.
Ketika kebuntuan terjadi, system_health sesi sudah mengambil semua xml_deadlock_report XEvent yang berisi grafik kebuntuan. system_health Karena sesi diaktifkan secara default, Anda tidak perlu mengonfigurasi sesi XEvent terpisah untuk menangkap informasi kebuntuan. Tidak diperlukan tindakan tambahan untuk mengambil informasi kebuntuan xml_deadlock_report dengan XEvent.
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.
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:
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 set hasilnya.
Contoh berikut menunjukkan output, setelah memilih tautan di Event_Data baris pertama hasil:
<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 (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 IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), dan X (Exclusive).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 DML (SELECT, , INSERT, UPDATEatau DELETE) tempat utas memiliki izin.Victim Resource Owner Menentukan utas yang berpartisipasi yang dipilih SQL Server sebagai korban untuk memutus siklus kebuntuan. Utas yang dipilih dan semua subthread yang ada dihentikan.Next Branch Mewakili dua subthread 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. Mungkin 0 (nol) dalam kasus kebuntuan yang belum terselesaikan. Tugas yang digulung balik tidak dapat dipilih sebagai korban kebuntuan.executionstack Mewakili kode Transact-SQL yang sedang dijalankan pada saat kebuntuan terjadi.priority 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.owner id ID transaksi yang memiliki kontrol atas permintaan.status Status tugas. Ini adalah salah satu nilai berikut:- pending Menunggu utas pekerja.- runnable Siap untuk menjalankan tetapi menunggu kuantum.- running Saat ini berjalan pada penjadwal.- suspended Eksekusi ditangguhkan.- done Tugas telah selesai.- spinloop Menunggu spinlock menjadi bebas.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 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 | 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 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_idDB: 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 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 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.
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 Melihat 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 mengetahui 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.
Tangani dengan TRY... MENANGKAP
Anda dapat menggunakan TRY... CATCH untuk menangani kebuntuan. Kesalahan korban kebuntuan 1205 dapat ditangkap oleh CATCH blok, dan transaksi dapat digulung balik sampai utas tidak terkunci.
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.
- Gunakan tingkat isolasi yang lebih rendah.
- Gunakan tingkat isolasi berbasis penerapan versi baris.
- Atur
READ_COMMITTED_SNAPSHOTopsi database aktif untuk mengaktifkan transaksi berkomitmen baca untuk menggunakan penerapan versi baris. - Gunakan isolasi salinan bayangan.
- Atur
- 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.
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 sambil 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. Tingkat isolasi yang lebih rendah, seperti penerapan baca, menyimpan kunci bersama untuk durasi yang lebih pendek daripada tingkat isolasi yang lebih tinggi, seperti dapat diserialisasikan. Ini mengurangi ketidakcocokan penguncian.
Menggunakan tingkat isolasi berbasis penerapan versi baris
READ_COMMITTED_SNAPSHOT Saat opsi database diatur ON, 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 perlu menyambungkan dua sesi ke AdventureWorksLT2019 database. Kami 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 TRANSACTION;
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 mengeluarkan kunci pembaruan (U) pada 72 baris pada SalesLT.ProductDescription tabel. 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 pada tabel SalesLT.Product, termasuk baris yang dikunci oleh Sesi A. Sesi B diblokir pada SalesLT.Product.
Kembali ke Sesi A. Jalankan pernyataan T-SQL berikut. Ini menjalankan pernyataan kedua UPDATE sebagai bagian dari transaksi terbuka.
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 akan melihat kebuntuan terjadi, dengan Sesi A dipilih sebagai korban kebuntuan. Sesi B berhasil diselesaikan. Pesan kesalahan muncul di 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 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 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 Anda akan terlihat seperti gambar berikut.
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 Transact-SQL berikut dalam dua sesi membuat kebuntuan pada tabel t2:
Dalam sesi 1:
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
Dalam sesi 2:
--session 2:
BEGIN TRANSACTION 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:
Konten terkait
- Gambaran umum Kejadian yang Diperluas
- sys.dm_tran_locks (T-SQL)
- Kelas Peristiwa Grafik Kebuntuan
- Kebuntuan dengan Tingkat Isolasi Yang Dapat Diulang Baca
- Kunci:Kelas Peristiwa Rantai Kebuntuan
- Kunci:Kelas Kejadian Kebuntuan
- TETAPKAN PRIORITAS_KEBUNTUAN (Transact-SQL)
- Menganalisis dan mencegah kebuntuan di Azure SQL Database
- Membuka, melihat, dan mencetak file kebuntuan di SQL Server Management Studio (SSMS)