Menganalisis dan mencegah kebuntuan di Azure SQL Database
Berlaku untuk:
Azure SQL Database
Artikel ini mengajarkan Anda cara mengidentifikasi kebuntuan di Azure SQL Database, menggunakan grafik kebuntuan dan Query Store untuk mengidentifikasi kueri dalam kebuntuan, serta merencanakan dan menguji perubahan untuk mencegah kebuntuan terulang kembali.
Artikel ini berfokus pada mengidentifikasi dan menganalisis kebuntuan karena ketidaksesuaian kunci. Pelajari selengkapnya tentang jenis kebuntuan lainnya di sumber daya yang dapat mengakibatkan kebuntuan.
Bagaimana kebuntuan terjadi di Azure SQL Database
Setiap database baru di Azure SQL Database memiliki pengaturan database read committed snapshot (RCSI) yang diaktifkan secara default. Pemblokiran antara sesi membaca data dan sesi menulis data diminimalkan di bawah RCSI, yang menggunakan penerapan versi baris untuk meningkatkan konkurensi. Namun, pemblokiran dan kebuntuan mungkin masih terjadi dalam database di Azure SQL Database karena:
- Kueri yang mengubah data dapat memblokir satu dengan yang lain.
- Kueri dapat berjalan di bawah tingkat isolasi yang meningkatkan pemblokiran. Tingkat isolasi dapat ditentukan melalui metode pustaka klien, petunjuk kueri, atau pernyataan SET di T-SQL.
- RCSI dapat dinonaktifkan, menyebabkan database menggunakan kunci bersama (S) untuk melindungi pernyataan SELECT yang dijalankan di bawah tingkat isolasi penerapan baca. Ini dapat meningkatkan pemblokiran dan kebuntuan.
Contoh kebuntuan
Kebuntuan terjadi ketika dua atau lebih tugas secara permanen memblokir satu sama lain karena setiap tugas memiliki kunci pada sumber daya yang coba dikunci oleh tugas lain. Kebuntuan juga disebut dependensi siklik: dalam kasus kebuntuan dua tugas, transaksi A memiliki dependensi pada transaksi B, dan transaksi B menutup lingkaran dengan memiliki dependensi pada transaksi A.
Contohnya:
- Sesi A memulai transaksi eksplisit dan menjalankan pernyataan pembaruan yang memperoleh kunci pembaruan (U) pada satu baris pada tabel
SalesLT.Productyang dikonversi menjadi kunci eksklusif (X). - Sesi B menjalankan pernyataan pembaruan yang mengubah tabel
SalesLT.ProductDescription. Pernyataan pembaruan bergabung ke tabelSalesLT.Productuntuk menemukan baris yang benar untuk diperbarui.- Sesi B memperoleh kunci pembaruan (U) pada 72 baris pada tabel
SalesLT.ProductDescription. - Sesi B memerlukan penguncian bersama pada baris di tabel
SalesLT.Product, termasuk baris yang dikunci oleh Sesi A. Sesi B diblokir padaSalesLT.Product.
- Sesi B memperoleh kunci pembaruan (U) pada 72 baris pada tabel
- Sesi A melanjutkan transaksinya, dan sekarang menjalankan pembaruan terhadap tabel
SalesLT.ProductDescription. Sesi A diblokir oleh Sesi B padaSalesLT.ProductDescription.
Semua transaksi dalam kebuntuan akan menunggu tanpa batas waktu kecuali salah satu transaksi yang berpartisipasi digulung balik, misalnya, karena sesinya dihentikan.
Pemantau kebuntuan mesin database secara berkala memeriksa tugas-tugas yang berada dalam kebuntuan. Jika pemantauan kebuntuan mendeteksi dependensi siklik, pemantauan memilih salah satu tugas sebagai korban dan mengakhiri transaksi tugas dengan kesalahan 1205, "Transaksi (ID Proses N) mengalami kebuntuan pada sumber daya kunci dengan proses lain dan telah dipilih sebagai korban kebuntuan. Jalankan kembali transaksi." Mengatasi kebuntuan dengan cara ini memungkinkan tugas atau tugas lain dalam kebuntuan untuk menyelesaikan transaksi mereka.
Catatan
Pelajari selengkapnya tentang kriteria untuk memilih korban kebuntuan di bagian Daftar proses kebuntuan dari artikel ini.
Aplikasi dengan transaksi yang dipilih sebagai korban kebuntuan harus mencoba kembali transaksi, yang biasanya selesai setelah transaksi atau transaksi lain yang terlibat dalam kebuntuan selesai.
Ini merupakan praktik terbaik untuk memperkenalkan penundaan singkat dan acak sebelum mencoba kembali agar terhindar dari kebuntuan yang sama lagi. Pelajari selengkapnya tentang cara merancang logika percobaan kembali untuk kesalahan sementara.
Tingkat isolasi default di Azure SQL Database
Database baru di Azure SQL Database mengaktifkan snapshot penerapan pembacaan (RCSI) secara default. RCSI mengubah perilaku tingkat isolasi penerapan pembacaan untuk menggunakan penerapan versi baris guna memberikan konsistensi tingkat pernyataan tanpa menggunakan kunci bersama (S) untuk pernyataan SELECT.
Dengan RCSI yang diaktifkan:
- Pernyataan yang membaca data tidak memblokir pernyataan yang mengubah data.
- Pernyataan yang mengubah data tidak memblokir pernyataan yang membaca data.
Tingkat isolasi snapshot juga diaktifkan secara default untuk database baru di Azure SQL Database. Isolasi snapshot adalah tingkat isolasi berbasis baris tambahan yang menyediakan konsistensi tingkat transaksi untuk data dan menggunakan versi baris untuk memilih baris yang akan diperbarui. Untuk menggunakan isolasi snapshot, kueri atau koneksi harus secara eksplisit mengatur tingkat isolasi transaksi mereka ke SNAPSHOT. Ini hanya dapat dilakukan ketika isolasi snapshot diaktifkan untuk database.
Anda dapat mengidentifikasi apakah isolasi RCSI dan/atau snapshot diaktifkan dengan Transact-SQL. Sambungkan ke database Anda di Azure SQL Database dan jalankan kueri berikut:
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO
Jika RCSI diaktifkan, kolom is_read_committed_snapshot_on akan mengembalikan nilai 1. Jika isolasi snapshot diaktifkan, kolom snapshot_isolation_state_desc akan mengembalikan nilai ON.
Jika RCSI telah dinonaktifkan untuk database di Azure SQL Database, selidiki mengapa RCSI dinonaktifkan sebelum mengaktifkannya kembali. Kode aplikasi mungkin telah ditulis dengan harapan bahwa kueri yang membaca data akan diblokir oleh kueri yang menulis data, yang mengakibatkan hasil yang salah dari kondisi bersaing saat RCSI diaktifkan.
Menafsirkan peristiwa kebuntuan
Peristiwa kebuntuan muncul setelah manajer kebuntuan di Azure SQL Database mendeteksi kebuntuan dan memilih transaksi sebagai korban. Dengan kata lain, jika Anda menyiapkan peringatan untuk kebuntuan, pemberitahuan akan diaktifkan setelah setiap kebuntuan diselesaikan. Tidak ada tindakan pengguna yang perlu diambil untuk kebuntuan tersebut. Aplikasi harus ditulis untuk menyertakan logika percobaan kembali sehingga secara otomatis melanjutkan setelah menerima kesalahan 1205, "Transaksi (ID Proses N) mengalami kebuntuan pada sumber daya kunci dengan proses lain dan telah dipilih sebagai korban kebuntuan. Jalankan kembali transaksi."
Tetapi, penting untuk menyiapkan peringatan, karena kebuntuan dapat terjadi kembali. Peringatan kebuntuan memungkinkan Anda menyelidiki apakah pola kebuntuan berulang terjadi di database Anda, dalam kasus ini Anda dapat memilih untuk mengambil tindakan untuk mencegah kebuntuan terulang kembali. Pelajari selengkapnya tentang peringatan di bagian Memantau dan memperingatkan tentang kebuntuan dari artikel ini.
Metode teratas untuk mencegah kebuntuan
Pendekatan risiko terendah untuk mencegah terjadinya kembali kebuntuan umumnya adalah menyetel indeks non-kluster untuk mengoptimalkan kueri yang terlibat dalam kebuntuan.
- Risiko rendah untuk pendekatan ini karena menyetel indeks non-kluster tidak memerlukan perubahan pada kode kueri itu sendiri, mengurangi risiko kesalahan pengguna saat menulis kembali T-SQL yang menyebabkan data yang salah diberikan ke pengguna.
- Penyetelan indeks non-kluster yang efektif membantu kueri menemukan data untuk dibaca dan diubah dengan lebih efisien. Dengan mengurangi jumlah data yang perlu diakses oleh kueri, kemungkinan pemblokiran berkurang dan kebuntuan sering kali dapat dicegah.
Dalam beberapa kasus, membuat atau menyetel indeks berkluster dapat mengurangi pemblokiran dan kebuntuan. Karena indeks berkluster termasuk dalam semua definisi indeks non-kluster, membuat atau mengubah indeks berkluster dapat menjadi operasi yang banyak menggunakan IO dan memakan waktu lama pada tabel yang lebih besar dengan indeks non-kluster yang ada. Pelajari selengkapnya tentang Panduan desain indeks berkluster.
Ketika penyetelan indeks tidak berhasil mencegah kebuntuan, metode lain tersedia:
- Jika kebuntuan hanya terjadi saat rencana tertentu dipilih untuk salah satu kueri yang terlibat dalam kebuntuan, memaksa rencana kueri dengan Query Store dapat mencegah kebuntuan terulang kembali.
- Menulis kembali T-SQL untuk satu atau lebih transaksi yang terlibat dalam kebuntuan juga dapat membantu mencegah kebuntuan. Memecah transaksi eksplisit menjadi transaksi yang lebih kecil membutuhkan pengodean dan pengujian yang cermat untuk memastikan validitas data saat terjadi perubahan bersamaan.
Pelajari selengkapnya tentang masing-masing pendekatan ini di bagian Mencegah kebuntuan agar tidak terulang kembali di artikel ini.
Memantau dan memperingatkan kebuntuan
Dalam artikel ini, kita akan menggunakan database sampel AdventureWorksLT guna menyiapkan peringatan untuk kebuntuan, membuat contoh kebuntuan, menganalisis grafik kebuntuan untuk contoh kebuntuan, dan menguji perubahan untuk mencegah kebuntuan terulang kembali.
Kita akan menggunakan klien SQL Server Management Studio (SSMS) dalam artikel ini, karena SQL Server Management Studio berisi fungsionalitas untuk menampilkan grafik kebuntuan dalam mode visual interaktif. Anda dapat menggunakan klien lain seperti Azure Data Studio untuk mengikuti contoh, tetapi Anda mungkin hanya dapat melihat grafik kebuntuan sebagai XML.
Membuat database AdventureWorksLT
Untuk mengikuti contoh, buat database baru di Azure SQL Database dan pilih data Sampel sebagai Sumber data.
Untuk instruksi terperinci tentang cara membuat AdventureWorksLT dengan portal Microsoft Azure, Azure CLI, atau PowerShell, pilih pendekatan pilihan Anda di Mulai Cepat: Membuat database tunggal Azure SQL Database.
Menyiapkan peringatan kebuntuan di portal Microsoft Azure
Guna menyiapkan peringatan untuk peristiwa kebuntuan, ikuti langkah-langkah dalam artikel Membuat peringatan untuk Azure SQL Database dan Azure Synapse Analytics menggunakan portal Microsoft Azure.
Pilih Kebuntuan sebagai nama sinyal untuk peringatan. Konfigurasikan Grup tindakan untuk memberi tahu Anda menggunakan metode pilihan Anda, seperti jenis tindakan Email/SMS/Push/Suara.
Mengumpulkan grafik kebuntuan di Azure SQL Database dengan Extended Events
Grafik kebuntuan adalah sumber informasi yang kaya mengenai proses dan kunci yang terlibat dalam kebuntuan. Untuk mengumpulkan grafik kebuntuan dengan Extended Events (XEvents) di Azure SQL Database, ambil peristiwa sqlserver.database_xml_deadlock_report.
Anda dapat mengumpulkan grafik kebuntuan dengan XEvents menggunakan target ring buffer atau target file peristiwa. Pertimbangan untuk memilih jenis target yang sesuai diringkas dalam tabel berikut:
| Pendekatan | Keuntungan | Pertimbangan | Skenario penggunaan |
|---|---|---|---|
| Target ring buffer |
|
|
|
| Target file peristiwa |
|
|
|
Pilih jenis target yang ingin Anda gunakan:
Target ring buffer praktis dan mudah disiapkan, tetapi memiliki kapasitas terbatas, yang dapat menyebabkan peristiwa lama hilang. Ring buffer tidak menyimpan peristiwa ke penyimpanan dan target ring buffer dihapus saat sesi XEvents dihentikan. Hal ini berarti bahwa XEvents yang dikumpulkan tidak akan tersedia saat mesin database dihidupkan ulang karena alasan apa pun, seperti failover. Target ring buffer paling cocok untuk pembelajaran dan kebutuhan jangka pendek jika Anda tidak memiliki kemampuan untuk menyiapkan sesi XEvents ke target file peristiwa dengan segera.
Kode sampel ini membuat sesi XEvents yang mengambil grafik kebuntuan di memori menggunakan target ring buffer. Memori maksimum yang diizinkan untuk target ring buffer adalah 4 MB, dan sesi akan berjalan secara otomatis saat database online, seperti setelah failover.
Untuk membuat dan kemudian memulai sesi XEvents untuk peristiwa sqlserver.database_xml_deadlock_report yang menulis ke target ring buffer, hubungkan ke database Anda dan jalankan T-SQL berikut:
CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO
Menyebabkan kebuntuan di AdventureWorksLT
Catatan
Contoh ini berfungsi dalam AdventureWorksLT database dengan skema dan data default saat RCSI telah diaktifkan. Lihat Membuat database AdventureWorksLT untuk instruksi membuat database.
Untuk menyebabkan kebuntuan, Anda harus menghubungkan dua sesi ke database AdventureWorksLT. Kita akan menyebut sesi ini sebagai Sesi A dan Sesi B.
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. Pesan kesalahan akan muncul di Sesi A dengan teks yang mirip dengan yang berikut ini:
Pesan 1205, Tingkat 13, Status 51, Transaksi Jalur 7 (ID Proses 91) mengalami kebuntuan pada sumber daya kunci dengan proses lain dan telah dipilih sebagai korban kebuntuan. Jalankan kembali transaksi.
Sesi B akan berhasil diselesaikan.
Jika Anda menyiapkan peringatan kebuntuan di portal Microsoft Azure, Anda akan menerima pemberitahuan segera setelah kebuntuan terjadi.
Melihat grafik kebuntuan dari sesi XEvents
Jika Anda telah menyiapkan sesi XEvents untuk mengumpulkan kebuntuan dan kebuntuan telah terjadi setelah sesi dimulai, Anda dapat melihat tampilan grafik interaktif dari grafik kebuntuan serta XML untuk grafik kebuntuan.
Metode yang berbeda tersedia untuk mendapatkan informasi kebuntuan untuk target ring buffer dan target file peristiwa. Pilih target yang Anda gunakan untuk sesi XEvents Anda:
Jika Anda menyiapkan penulisan sesi XEvents ke ring buffer, Anda dapat mengkueri informasi kebuntuan dengan T-SQL berikut. Sebelum menjalankan kueri, ganti nilai @tracename dengan nama sesi xEvents Anda.
DECLARE @tracename sysname = N'deadlocks';
WITH ring_buffer AS (
SELECT CAST(target_data AS XML) as rb
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
WHERE s.name = @tracename and
t.target_name = N'ring_buffer'
), dx AS (
SELECT
dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
)
SELECT
d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO
Melihat dan menyimpan grafik kebuntuan dalam XML
Melihat grafik kebuntuan dalam format XML memungkinkan Anda menyalin inputbuffer dari pernyataan T-SQL yang terlibat dalam kebuntuan. Anda mungkin juga lebih suka menganalisis kebuntuan dalam format berbasis teks.
Jika Anda telah menggunakan kueri T-SQL guna menampilkan informasi grafik kebuntuan, untuk melihat XML grafik kebuntuan, pilih nilai di kolom deadlock_xml dari baris mana pun untuk membuka XML grafik kebuntuan di jendela baru di SQL Server Management Studio.
XML untuk contoh grafik kebuntuan ini adalah:
<deadlock>
<victim-list>
<victimProcess id="process24756e75088" />
</victim-list>
<process-list>
<process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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' </inputbuf>
</process>
<process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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'; </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
<owner-list>
<owner id="process2476d07d088" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process24756e75088" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
<owner-list>
<owner id="process24756e75088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2476d07d088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Untuk menyimpan grafik kebuntuan sebagai file XML:
- Pilih File dan Simpan Sebagai....
- Biarkan nilai Simpan sebagai jenis sebagai File XML (*.xml) default
- Atur Nama file ke nama pilihan Anda.
- Pilih Simpan.
Simpan grafik kebuntuan sebagai file XDL yang dapat ditampilkan secara interaktif di SQL Server Management Studio
Melihat representasi interaktif dari grafik kebuntuan dapat berguna untuk mendapatkan gambaran umum singkat tentang proses dan sumber daya yang terlibat dalam kebuntuan, dan dengan cepat mengidentifikasi korban kebuntuan.
Untuk menyimpan grafik kebuntuan sebagai file yang dapat ditampilkan secara grafis oleh SQL Server Management Studio:
Pilih nilai di kolom
deadlock_xmldari baris mana pun untuk membuka XML grafik kebuntuan di jendela baru di SQL Server Management Studio.Pilih File dan Simpan Sebagai....
Atur Simpan sebagai jenis ke Semua File.
Atur Nama file ke nama pilihan Anda, dengan ekstensi yang diatur ke .xdl.
Pilih Simpan.
Tutup file dengan memilih X pada tab di bagian atas jendela, atau dengan memilih File, lalu Tutup.
Buka kembali file di SQL Server Management Studio dengan memilih File, lalu Buka, lalu File. Pilih file yang Anda simpan dengan ekstensi
.xdl.Grafik kebuntuan sekarang akan ditampilkan di SQL Server Management Studio dengan representasi visual dari proses dan sumber daya yang terlibat dalam kebuntuan.
Menganalisis kebuntuan untuk Azure SQL Database
Grafik kebuntuan biasanya memiliki tiga node:
- Daftar korban. Pengidentifikasi proses korban kebuntuan.
- Daftar proses. Informasi tentang semua proses yang terlibat dalam kebuntuan. Grafik kebuntuan menggunakan istilah 'proses' untuk mewakili sesi yang menjalankan transaksi.
- Daftar sumber daya. Informasi tentang sumber daya yang terlibat dalam kebuntuan.
Saat menganalisis kebuntuan, menelusuri node ini adalah langkah yang berguna.
Daftar korban kebuntuan
Daftar korban kebuntuan menunjukkan proses yang dipilih sebagai korban kebuntuan. Dalam representasi visual dari grafik kebuntuan, proses diwakili oleh oval. Proses korban kebuntuan memiliki tanda "X" yang digambar di atas oval.
Dalam tampilan XML grafik kebuntuan, node victim-list memberikan ID untuk proses yang menjadi korban kebuntuan.
Dalam contoh kebuntuan kita, ID proses korban adalah process24756e75088. Kita dapat menggunakan ID ini saat memeriksa node daftar proses dan daftar sumber daya untuk mempelajari selengkapnya tentang proses korban dan sumber daya yang dikunci atau diminta untuk dikunci.
Daftar proses kebuntuan
Daftar proses kebuntuan adalah sumber informasi yang kaya tentang transaksi yang terlibat dalam kebuntuan.
Representasi grafik dari grafik kebuntuan hanya menunjukkan subset informasi yang terkandung dalam XML grafik kebuntuan. Oval dalam grafik kebuntuan mewakili proses, dan menunjukkan informasi yang mencakup:
ID proses server, juga dikenal sebagai ID sesi atau SPID.
Prioritas kebuntuan sesi. Jika dua sesi memiliki prioritas kebuntuan yang berbeda, sesi dengan prioritas lebih rendah dipilih sebagai korban kebuntuan. Dalam contoh ini, kedua sesi memiliki prioritas kebuntuan yang sama.
Jumlah log transaksi yang digunakan oleh sesi dalam byte. Jika kedua sesi memiliki prioritas kebuntuan yang sama, pemantauan kebuntuan memilih sesi yang lebih murah untuk digulung balik sebagai korban kebuntuan. Biaya ditentukan dengan membandingkan jumlah byte log yang ditulis ke titik tersebut dalam setiap transaksi.
Dalam contoh kebuntuan kita, session_id 89 telah menggunakan jumlah log transaksi yang lebih rendah, dan dipilih sebagai korban kebuntuan.
Selain itu, Anda dapat melihat buffer input untuk pernyataan terakhir yang dijalankan di setiap sesi sebelum kebuntuan dengan mengarahkan mouse ke setiap proses. Buffer input akan muncul di tipsalat.
Informasi tambahan tersedia untuk proses dalam tampilan XML dari grafik kebuntuan, termasuk:
- Mengidentifikasi informasi untuk sesi, seperti nama klien, nama host, dan ID masuk.
- Hash rencana kueri untuk pernyataan terakhir yang dijalankan oleh setiap sesi sebelum kebuntuan. Hash rencana kueri berguna untuk mengambil lebih banyak informasi tentang kueri dari Query Store.
Dalam contoh kebuntuan kita:
- Kita dapat melihat bahwa kedua sesi dijalankan menggunakan klien SQL Server Management Studio di bawah info masuk chrisqpublic.
- Hash rencana kueri dari pernyataan terakhir yang dijalankan sebelum kebuntuan oleh korban kebuntuan kita adalah 0x02b0f58d7730f798. Kita dapat melihat teks pernyataan ini di buffer input.
- Hash rencana kueri dari pernyataan terakhir yang dijalankan oleh sesi lain dalam kebuntuan kita juga 0x02b0f58d7730f798. Kita dapat melihat teks pernyataan ini di buffer input. Dalam kasus ini, kedua kueri memiliki hash rencana kueri yang sama karena kueri identik, kecuali untuk nilai harfiah yang digunakan sebagai predikat persamaan.
Kita akan menggunakan nilai ini nanti dalam artikel ini untuk menemukan informasi tambahan di Query Store.
Keterbatasan buffer input dalam daftar proses kebuntuan
Ada beberapa batasan yang harus diperhatikan terkait informasi buffer input dalam daftar proses kebuntuan.
Teks kueri mungkin terpotong di buffer input. Buffer input terbatas pada 4.000 karakter pertama dari pernyataan yang dijalankan.
Selain itu, beberapa pernyataan yang terlibat dalam kebuntuan mungkin tidak disertakan dalam grafik kebuntuan. Dalam contoh kita, Sesi A menjalankan dua pernyataan pembaruan dalam satu transaksi. Hanya pernyataan pembaruan kedua, pembaruan yang menyebabkan kebuntuan, yang termasuk dalam grafik kebuntuan. Pernyataan pembaruan pertama yang dijalankan oleh Sesi A berperan dalam kebuntuan dengan memblokir Sesi B. Buffer input, query_hash, dan informasi terkait untuk pernyataan pertama yang dijalankan oleh Sesi A tidak disertakan dalam grafik kebuntuan.
Untuk mengidentifikasi keseluruhan T-SQL yang dijalankan dalam transaksi multipernyataan yang terlibat dalam kebuntuan, Anda perlu menemukan informasi yang relevan dalam prosedur tersimpan atau kode aplikasi yang menjalankan kueri, atau menjalankan pelacakan menggunakan Extended Events untuk mengambil pernyataan lengkap yang dijalankan oleh sesi yang terlibat dalam kebuntuan saat terjadi. Jika pernyataan yang terlibat dalam kebuntuan telah terpotong dan hanya sebagian T-SQL yang muncul di buffer input, Anda dapat menemukan T-SQL untuk pernyataan di Query Store dengan Rencana Eksekusi.
Daftar sumber daya kebuntuan
Daftar sumber daya kebuntuan menunjukkan sumber daya kunci mana yang dimiliki dan ditunggu oleh proses dalam kebuntuan.
Sumber daya diwakili oleh persegi dalam representasi visual kebuntuan:
Catatan
Anda mungkin memperhatikan bahwa nama database direpresentasikan sebagai pengidentifikasi unik dalam grafik kebuntuan untuk database di Azure SQL Database. Ini merupakan physical_database_name untuk database yang tercantum dalam tampilan manajemen dinamis sys.databases dan sys.dm_user_db_resource_governance.
Dalam contoh kebuntuan ini:
Korban kebuntuan, yang kita sebut sebagai Sesi A:
- Memiliki kunci eksklusif (X) pada kunci di indeks
PK_Product_ProductIDpada tabelSalesLT.Product. - Meminta kunci pembaruan (U) pada kunci di indeks
PK_ProductDescription_ProductDescriptionIDpada tabelSalesLT.ProductDescription.
- Memiliki kunci eksklusif (X) pada kunci di indeks
Proses lainnya, yang kita sebut sebagai Sesi B:
- Memiliki kunci pembaruan (U) pada kunci di indeks
PK_ProductDescription_ProductDescriptionIDpada tabelSalesLT.ProductDescription. - Meminta kunci bersama (S) pada kunci di indeks
PK_ProductDescription_ProductDescriptionIDpada tabelSalesLT.ProductDescription.
- Memiliki kunci pembaruan (U) pada kunci di indeks
Kita dapat melihat informasi yang sama di XML grafik kebuntuan dalam node daftar sumber daya.
Menemukan rencana eksekusi kueri di Query Store
Sering kali berguna untuk memeriksa rencana eksekusi kueri untuk pernyataan yang terlibat dalam kebuntuan. Rencana eksekusi ini sering dapat ditemukan di Query Store menggunakan hash rencana kueri dari tampilan XML dari daftar proses grafik kebuntuan.
Kueri T-SQL ini mencari rencana kueri yang cocok dengan hash rencana kueri yang kita temukan untuk contoh kebuntuan kita. Hubungkan ke database pengguna di Azure SQL Database untuk menjalankan kueri.
DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798
SELECT
qrsi.end_time as interval_end_time,
qs.query_id,
qp.plan_id,
qt.query_sql_text,
TRY_CAST(qp.query_plan as XML) as query_plan,
qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO
Anda mungkin tidak dapat memperoleh rencana eksekusi kueri dari Query Store, bergantung pada pengaturan CLEANUP_POLICY atau QUERY_CAPTURE_MODE Query Store Anda. Dalam kasus ini, Anda sering kali dapat memperoleh informasi yang diperlukan dengan menampilkan perkiraan rencana eksekusi untuk kueri.
Mencari pola yang meningkatkan pemblokiran
Saat memeriksa rencana eksekusi kueri yang terlibat dalam kebuntuan, cari pola yang mungkin berkontribusi pada pemblokiran dan kebuntuan.
Pemindaian tabel atau indeks. Saat kueri yang mengubah data dijalankan di bawah RCSI, pilihan baris yang akan diperbarui dilakukan menggunakan pemindaian pemblokiran tempat kunci pembaruan (U) diambil pada baris data saat nilai data dibaca. Jika baris data tidak memenuhi kriteria pembaruan, kunci pembaruan dirilis dan baris berikutnya dikunci dan dipindai.
Menyetel indeks untuk membantu kueri perubahan menemukan baris dengan lebih efisien mengurangi jumlah kunci pembaruan yang dikeluarkan. Hal ini mengurangi kemungkinan pemblokiran dan kebuntuan.
Tampilan terindeks yang merujuk lebih dari satu tabel. Saat Anda mengubah tabel yang direferensikan dalam tampilan terindeks, mesin database juga harus mempertahankan tampilan terindeks. Hal ini membutuhkan mengeluarkan lebih banyak kunci dan dapat menyebabkan peningkatan pemblokiran dan kebuntuan. Tampilan terindeks juga dapat menyebabkan operasi pembaruan dijalankan secara internal di bawah tingkat isolasi penerapan pembacaan.
Perubahan kolom yang direferensikan dalam batasan kunci asing. Saat Anda mengubah kolom dalam tabel yang direferensikan dalam batasan FOREIGN KEY, mesin database harus mencari baris terkait di tabel referensi. Versi baris tidak dapat digunakan untuk pembacaan ini. Dalam kasus saat pembaruan atau penghapusan kaskade diaktifkan, tingkat isolasi dapat ditingkatkan menjadi dapat diserialisasi selama durasi pernyataan untuk melindungi dari sisipan bayangan.
Petunjuk kunci. Cari petunjuk tabel yang menentukan tingkat isolasi yang membutuhkan lebih banyak kunci. Petunjuk ini termasuk
HOLDLOCK(yang setara dengan dapat diserialisasi),SERIALIZABLE,READCOMMITTEDLOCK(yang menonaktifkan RCSI), danREPEATABLEREAD. Selain itu, petunjuk sepertiPAGLOCK,TABLOCK,UPDLOCK, danXLOCKdapat meningkatkan risiko pemblokiran dan kebuntuan.Jika petunjuk ini ada, selidiki mengapa petunjuk tersebut diterapkan. Petunjuk ini dapat mencegah kondisi bersaing dan memastikan validitas data. Anda dapat membiarkan petunjuk ini tetap di tempatnya dan mencegah kebuntuan di masa mendatang menggunakan metode alternatif di bagian Mencegah kebuntuan agar tidak terulang kembali di artikel ini jika perlu.
Catatan
Pelajari selengkapnya tentang perilaku saat mengubah data menggunakan penerapan versi baris di Panduan penguncian transaksi dan penerapan versi baris.
Saat memeriksa kode lengkap untuk transaksi, baik dalam rencana eksekusi atau dalam kode kueri aplikasi, cari pola bermasalah tambahan:
Interaksi pengguna dalam transaksi. Interaksi pengguna di dalam transaksi multi pernyataan eksplisit secara signifikan meningkatkan durasi transaksi. Hal ini mengakibatkan transaksi ini lebih mungkin menjadi tumpang tindih dan muncul pemblokiran serta kebuntuan.
Demikian pula, menahan transaksi terbuka dan mengkueri database atau sistem yang tidak terkait di tengah transaksi secara signifikan meningkatkan kemungkinan pemblokiran dan kebuntuan.
Transaksi mengakses objek dalam urutan yang berbeda. Kebuntuan kecil kemungkinannya terjadi ketika transaksi multi pernyataan eksplisit secara bersamaan mengikuti pola yang sama dan mengakses objek dalam urutan yang sama.
Mencegah agar kebuntuan tidak terulang kembali
Ada beberapa teknik yang tersedia untuk mencegah terulangnya kembali kebuntuan, termasuk penyetelan indeks, memaksa rencana dengan Query Store, dan mengubah kueri T-SQL.
Tinjau indeks berkluster tabel. Sebagian besar tabel mendapat keuntungan dari indeks berkluster, tetapi sering kali, tabel diterapkan sebagai timbunan secara tidak sengaja.
Salah satu cara untuk memeriksa indeks berkluster adalah dengan menggunakan prosedur tersimpan sistem sp_helpindex. Misalnya, kita dapat melihat ringkasan indeks pada tabel
SalesLT.Productdengan menjalankan pernyataan berikut:exec sp_helpindex 'SalesLT.Product'; GOTinjau kolom index_description. Tabel hanya dapat memiliki satu indeks berkluster. Jika indeks berkluster telah diterapkan untuk tabel, index_description akan berisi kata 'berkluster'.
Jika tidak ada indeks berkluster, tabel adalah timbunan. Dalam kasus ini, tinjau apakah tabel sengaja dibuat sebagai timbunan untuk memecahkan masalah performa tertentu. Pertimbangkan untuk menerapkan indeks berkluster berdasarkan panduan desain indeks berkluster.
Dalam beberapa kasus, membuat atau menyetel indeks berkluster dapat mengurangi atau menghilangkan pemblokiran di kebuntuan. Dalam kasus lain, Anda mungkin perlu menggunakan teknik tambahan seperti yang lain dalam daftar ini.
Buat atau ubah indeks non-kluster. Menyetel indeks non-kluster dapat membantu kueri perubahan Anda menemukan data untuk diperbarui lebih cepat, yang mengurangi jumlah kunci pembaruan yang diperlukan.
Dalam contoh kebuntuan kita, rencana eksekusi kueri ditemukan di Query Store yang berisi pemindaian indeks berkluster terhadap indeks
PK_Product_ProductID. Grafik kebuntuan menunjukkan bahwa kunci bersama (S) yang menunggu pada indeks ini adalah komponen dalam kebuntuan.
Pemindaian indeks ini dilakukan karena kueri pembaruan kita perlu mengubah tampilan terindeks bernama
vProductAndDescription. Seperti disebutkan di bagian Mencari pola yang meningkatkan pemblokiran dari artikel ini, tampilan terindeks yang merujuk beberapa tabel dapat meningkatkan pemblokiran dan kemungkinan kebuntuan.Jika kita membuat indeks non-kluster berikut di database
AdventureWorksLTyang "mencakup" kolom dariSalesLT.Productyang dirujuk oleh tampilan terindeks, hal ini membantu kueri menemukan baris dengan lebih efisien:CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID); GOSetelah membuat indeks ini, kebuntuan tidak lagi terjadi.
Ketika kebuntuan melibatkan perubahan ke kolom yang direferensikan dalam batasan kunci asing, pastikan bahwa indeks pada tabel referensi FOREIGN KEY mendukung pencarian baris terkait secara efisien.
Sementara indeks dapat secara dramatis meningkatkan performa kueri dalam beberapa kasus, indeks juga memiliki biaya operasional dan manajemen. Tinjau panduan desain indeks umum untuk membantu menilai keuntungan indeks sebelum membuat indeks, terutama indeks lebar dan indeks pada tabel besar.
Beri nilai tampilan terindeks. Opsi lain untuk mencegah contoh kebuntuan terulang kembali adalah dengan menghapus tampilan terindeks
SalesLT.vProductAndDescription. Jika tampilan terindeks tersebut tidak digunakan, hal ini akan mengurangi biaya pemeliharaan tampilan terindeks dari waktu ke waktu.Gunakan isolasi Snapshot. Dalam beberapa kasus, mengatur tingkat isolasi ke snapshot untuk satu atau beberapa transaksi yang terlibat dalam kebuntuan dapat mencegah pemblokiran dan kebuntuan terulang kembali.
Teknik ini kemungkinan besar akan berhasil jika digunakan pada pernyataan SELECT ketika snapshot penerapan pembacaan dinonaktifkan dalam database. Saat snapshot penerapan pembacaan dinonaktifkan, kueri SELECT yang menggunakan tingkat isolasi penerapan pembacaan memerlukan kunci bersama (S). Menggunakan isolasi snapshot pada transaksi ini menghilangkan kebutuhan akan kunci bersama, yang dapat mencegah pemblokiran dan kebuntuan.
Dalam database tempat isolasi snapshot penerapan pembacaan telah diaktifkan, kueri SELECT tidak memerlukan kunci bersama (S), sehingga kebuntuan lebih mungkin terjadi di antara transaksi yang mengubah data. Jika terjadi kebuntuan antara beberapa transaksi yang mengubah data, isolasi snapshot dapat mengakibatkan konflik pembaruan, bukan kebuntuan. Hal ini juga membutuhkan salah satu transaksi untuk mencoba kembali operasinya.
Paksakan rencana dengan Query Store. Anda mungkin menemukan bahwa salah satu kueri dalam kebuntuan memiliki beberapa rencana eksekusi, dan kebuntuan hanya terjadi ketika rencana tertentu digunakan. Anda dapat mencegah agar kebuntuan tidak terulang kembali dengan memaksa rencana di Query Store.
Ubah T-SQL. Anda mungkin perlu mengubah T-SQL untuk mencegah terulangnya kembali kebuntuan. Mengubah T-SQL harus dilakukan dengan hati-hati dan perubahan harus diuji secara ketat untuk memastikan bahwa data benar ketika perubahan dijalankan secara bersamaan. Saat menulis kembali T-SQL, pertimbangkan:
- Mengurutkan pernyataan dalam transaksi sehingga pernyataan mengakses objek dalam urutan yang sama.
- Memecah transaksi menjadi transaksi yang lebih kecil jika memungkinkan.
- Menggunakan petunjuk kueri, jika perlu, untuk mengoptimalkan performa. Anda dapat menerapkan petunjuk tanpa mengubah kode aplikasi menggunakan Query Store.
Temukan lebih banyak cara untuk meminimalkan kebuntuan di panduan Kebuntuan.
Catatan
Dalam beberapa kasus, Anda mungkin ingin menyesuaikan prioritas kebuntuan dari satu atau beberapa sesi yang terlibat dalam kebuntuan jika prioritas kebuntuan penting agar salah satu sesi berhasil diselesaikan tanpa mencoba kembali, atau jika salah satu kueri terlibat dalam kebuntuan tidak penting dan harus selalu dipilih sebagai korban. Meskipun prioritas kebuntuan tidak mencegah terulangnya kembali kebuntuan, prioritas kebuntuan dapat mengurangi dampak kebuntuan di masa mendatang.
Menghilangkan sesi XEvents
Anda mungkin ingin membiarkan sesi XEvents mengumpulkan informasi kebuntuan yang berjalan di database penting untuk waktu yang lama. Perlu diketahui bahwa jika Anda menggunakan target file peristiwa, hal ini dapat menghasilkan file besar jika terjadi banyak kebuntuan. Anda dapat menghapus file blob dari Azure Storage untuk pelacakan aktif, kecuali untuk file yang sedang ditulis.
Saat Anda ingin menghapus sesi XEvents, T-SQL menghilangkan sesi merupakan hal yang sama, terlepas dari jenis target yang dipilih.
Untuk menghapus sesi XEvents, jalankan T-SQL berikut. Sebelum menjalankan kode, ganti nama sesi dengan nilai yang sesuai.
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO
DROP EVENT SESSION [deadlocks] ON DATABASE;
GO
Gunakan Azure Storage Explorer
Penjelajah Penyimpanan Azure adalah aplikasi mandiri yang menyederhanakan bekerja dengan target file kejadian yang disimpan dalam blob di Azure Storage. Anda dapat menggunakan Storage Explorer untuk:
- Membuat kontainer blob untuk menyimpan data sesi XEvent.
- Mendapatkan tanda tangan akses bersama (SAS) untuk kontainer blob.
- Seperti disebutkan dalam Mengumpulkan grafik kebuntuan di Azure SQL Database dengan Extended Events, izin baca, tulis, dan daftar diperlukan.
- Menghapus semua karakter
?di depan dariQuery stringuntuk menggunakan nilai sebagai rahasia saat membuat kredensial cakupan database.
- Melihat dan mengunduh file peristiwa yang diperluas dari kontainer blob.
Mengunduh Azure Storage Explorer..
Langkah berikutnya
Pelajari selengkapnya tentang performa di Azure SQL Database:
- Memahami dan mengatasi masalah pemblokiran Azure SQL Database
- Penguncian Transaksi dan Panduan Penerapan Versi Baris
- Panduan kebuntuan
- ATUR TINGKAT ISOLASI TRANSAKSI
- Azure SQL Database: meningkatkan penyetelan performa dengan penyetelan otomatis
- Memberikan performa yang konsisten dengan Azure SQL
- Logika percobaan kembali untuk kesalahan sementara.
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk