Memahami dan mengatasi masalah pemblokiran Azure SQL Database

  • Artikel

Berlaku untuk:Azure SQL Database

Tujuan

Artikel ini menjelaskan pemblokiran dalam database Azure SQL dan menunjukkan cara memecahkan masalah dan mengatasi pemblokiran.

Dalam artikel ini, istilah koneksi mengacu pada satu sesi log-on database. Setiap koneksi muncul sebagai ID sesi (SPID) atau session_id di banyak DMV. Masing-masing SPID ini sering disebut sebagai proses, meskipun itu bukan konteks proses yang terpisah dalam arti biasa. Sebaliknya, setiap SPID terdiri dari sumber daya server dan struktur data yang diperlukan untuk melayani permintaan koneksi tunggal dari klien tertentu. Satu aplikasi klien mungkin memiliki satu atau beberapa koneksi. Dari perspektif Azure SQL Database, tidak ada perbedaan antara beberapa koneksi dari satu aplikasi klien pada satu komputer klien dan beberapa koneksi dari beberapa aplikasi klien atau beberapa komputer klien; mereka atomik. Satu koneksi dapat memblokir koneksi lain, terlepas dari sumber.

Untuk informasi tentang pemecahan masalah kebuntuan, lihat Menganalisis dan mencegah kebuntuan di Azure SQL Database.

Catatan

Konten ini difokuskan pada Azure SQL Database. Azure SQL Database didasarkan pada versi stabil terbaru dari mesin database Microsoft SQL Server, sehingga banyak konten serupa meskipun pilihan dan alat untuk menyelesaikan masalahnya mungkin berbeda. Untuk informasi selengkapnya tentang pemblokiran di SQL Server, lihat Memahami dan mengatasi masalah pemblokiran SQL Server.

Memahami pemblokiran

Pemblokiran adalah karakteristik yang tidak dapat dihindari dari setiap sistem manajemen database relasional (RDBMS) dengan konkurensi berbasis kunci. Pemblokiran database di Azure SQL Database terjadi ketika satu sesi memegang kunci sumber daya tertentu dan SPID kedua mencoba untuk mendapatkan jenis kunci yang tidak sesuai pada sumber daya yang sama. Biasanya, jangka waktu penguncian sumber SPID pertama tersebut pendek. Ketika sesi pemilik melepaskan kunci, koneksi kedua kemudian bebas untuk memperoleh kuncinya sendiri pada sumber dan melanjutkan pemrosesan. Ini normal dan dapat terjadi berkali-kali sepanjang hari tanpa efek nyata pada performa sistem.

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 dalam string koneksi aplikasi, petunjuk kueri, atau pernyataan SET dalam Transact-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.

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.

Durasi dan konteks transaksi kueri menentukan berapa lama kuncinya ditahan dan efeknya pada kueri lain. Pernyataan SELECT yang dijalankan di bawah RCSI tidak memperoleh kunci bersama (S) pada data yang dibaca, dan oleh karena itu tidak memblokir transaksi yang memodifikasi data. Untuk pernyataan INSERT, UPDATE, dan DELETE, kunci ditahan selama kueri, baik untuk konsistensi data maupun untuk memungkinkan kueri digulung balik jika perlu.

Untuk kueri yang dijalankan dalam transaksi eksplisit, jenis kunci dan durasi yang menahan kunci ditentukan oleh jenis kueri, tingkat isolasi transaksi, dan apakah petunjuk kunci digunakan dalam kueri. Untuk deskripsi penguncian, petunjuk kunci, dan tingkat isolasi transaksi, lihat artikel berikut ini:

Ketika penguncian dan pemblokiran berlanjut ke titik di mana ada efek yang merugikan pada performa sistem, itu disebabkan oleh salah satu alasan berikut:

  • SPID mengunci seperangkat sumber daya untuk jangka waktu yang lama sebelum melepaskannya. Jenis pemblokiran ini selesai sendiri dari waktu ke waktu tetapi dapat menyebabkan penurunan performa.

  • SPID mengunci seperangkat sumber daya dan tidak pernah melepaskannya. Jenis pemblokiran ini tidak selesai sendiri dan mencegah akses ke sumber daya yang terpengaruh tanpa batas waktu.

Pada skenario pertama, situasinya bisa sangat fleksibel karena SPID yang berbeda menyebabkan pemblokiran pada sumber daya yang berbeda dari waktu ke waktu, membuat target yang bergerak. Masalah pada situasi ini sulit dipecahkan menggunakan SQL Server Management Studio untuk mempersempit masalah ke kueri individual. Sebaliknya, situasi kedua yang konsisten dapat lebih mudah didiagnosis.

Penguncian yang dioptimalkan

Penguncian yang dioptimalkan adalah fitur Mesin Database baru secara drastis mengurangi memori kunci dan jumlah kunci yang diperlukan secara bersamaan untuk penulisan. Penguncian yang dioptimalkan menggunakan dua komponen utama: Penguncian ID Transaksi (TID) (juga digunakan dalam fitur penerapan versi baris lainnya) dan kunci setelah kualifikasi (LAQ). Ini tidak memerlukan konfigurasi tambahan.

Artikel ini saat ini berlaku untuk perilaku Mesin Database tanpa penguncian yang dioptimalkan.

Untuk informasi selengkapnya dan untuk mempelajari tempat penguncian yang dioptimalkan tersedia, lihat Penguncian yang dioptimalkan.

Aplikasi dan pemblokiran

Mungkin ada kecenderungan untuk fokus pada masalah penyetelan sisi server dan platform saat menghadapi masalah pemblokiran. Namun, perhatian yang hanya diberikan pada database mungkin tidak mengarah pada penyelesaian, dan dapat menyerap waktu dan energi yang lebih baik diarahkan untuk memeriksa aplikasi klien dan kueri yang diajukannya. Tidak peduli tingkat visibilitas apa yang diekspos aplikasi mengenai panggilan database yang dibuat, masalah pemblokiran tetap sering memerlukan pemeriksaan pernyataan SQL yang tepat yang disampaikan oleh aplikasi dan perilaku pasti aplikasi mengenai pembatalan kueri, manajemen koneksi, mengambil semua baris hasil, dan sebagainya. Jika alat pengembangan tidak memungkinkan kontrol eksplisit pada manajemen koneksi, pembatalan kueri, kueri yang waktunya habis, pengambilan hasil, dan sebagainya, masalah pemblokiran mungkin tidak dapat diselesaikan. Potensi ini harus diperiksa secara ketat sebelum memilih alat pengembangan aplikasi untuk Azure SQL Database, terutama untuk lingkungan OLTP dengan performa sensitif.

Perhatikan performa database selama fase desain dan konstruksi database dan aplikasi. Khususnya, konsumsi sumber daya, tingkat isolasi, dan panjang jalur transaksi harus dievaluasi untuk setiap kueri. Setiap kueri dan transaksi harus sesingkat mungkin. Disiplin manajemen koneksi yang baik harus dilakukan, tanpa itu, aplikasi mungkin tampak memiliki performa yang dapat diterima saat jumlah pengguna sedikit, tetapi performanya dapat menurun secara signifikan seiring meningkatnya jumlah pengguna.

Dengan aplikasi dan desain kueri yang tepat, Azure SQL Database mampu mendukung ribuan pengguna secara simultan di satu server, dengan sedikit pemblokiran.

Catatan

Untuk panduan pengembangan aplikasi lainnya, lihat Pemecahan masalah konektivitas dan kesalahan lainnya dengan Azure SQL Database dan Azure SQL Managed Instance dan Transient Fault Handling.

Memecahkan masalah pemblokiran

Terlepas dari situasi pemblokiran apa pun, metode untuk pemecahan masalah penguncian yang digunakan adalah sama. Pemisahan logis inilah yang akan menentukan sisa komposisi artikel ini. Konsepnya adalah menemukan kepala pemblokir dan mengidentifikasi apa yang dilakukan kueri itu dan mengapa itu memblokir. Setelah kueri bermasalah diidentifikasi (yaitu apa yang mengunci untuk waktu yang lama), langkah berikutnya adalah menganalisis dan menentukan mengapa pemblokiran terjadi. Setelah kita memahami alasannya, kita kemudian dapat membuat perubahan dengan mendesain ulang kueri dan transaksi.

Langkah-langkah dalam pemecahan masalah:

  1. Identifikasi sesi pemblokiran utama (kepala pemblokir)

  2. Temukan kueri dan transaksi yang menyebabkan pemblokiran (apa yang mengunci untuk jangka waktu yang lama)

  3. Analisis/pahami mengapa pemblokiran berkepanjangan terjadi

  4. Atasi masalah pemblokiran dengan mendesain ulang kueri dan transaksi

Sekarang mari kita dalami dan membahas cara menentukan sesi pemblokiran utama dengan tangkapan data yang sesuai.

Mengumpulkan informasi pemblokiran

Untuk mengatasi kesulitan pemecahan masalah pemblokiran, administrator database dapat menggunakan skrip SQL yang terus memantau keadaan penguncian dan pemblokiran dalam database dia Azure SQL Database. Untuk mengumpulkan data ini, pada dasarnya ada dua metode.

Yang pertama adalah mengkueri objek manajemen dinamis (DMOs) dan menyimpan hasilnya sebagai perbandingan secara berkala. Beberapa objek yang direferensikan dalam artikel ini adalah tampilan manajemen dinamis (DMV) dan beberapa di antaranya adalah fungsi manajemen dinamis (DMF). Metode kedua adalah menggunakan XEvents untuk mengetahui apa yang sedang berjalan.

Mengumpulkan informasi dari DMV

Merujuk DMV untuk memecahkan masalah bertujuan untuk mengidentifikasi SPID (ID sesi) di kepala rantai pemblokiran dan Pernyataan SQL. Carilah SPID korban yang sedang diblokir. Jika ada SPID yang diblokir oleh SPID lain, maka selidiki SPID yang memiliki sumber (SPID pemblokiran). Apakah pemilik SPID diblokir juga? Anda dapat menyusuri untuk menemukan kepala pemblokir kemudian menyelidiki mengapa ia mempertahankan kuncinya.

Pastikan untuk menjalankan setiap skrip ini dalam database target di Azure SQL Database.

  • Perintah sp_who dan sp_who2 adalah perintah yang lebih lama untuk memperlihatkan semua sesi saat ini. DMV sys.dm_exec_sessions menampilkan lebih banyak data dalam set hasil yang lebih mudah untuk dikueri dan difilter. Anda akan menemukan sys.dm_exec_sessions di inti kueri lain.

  • Jika Anda sudah memiliki sesi tertentu yang telah teridentifikasi, Anda dapat menggunakan DBCC INPUTBUFFER(<session_id>) untuk menemukan pernyataan terakhir yang dikirimkan oleh sesi. Hasil serupa dapat ditampilkan dengan fungsi manajemen dinamis (DMF) sys.dm_exec_input_buffer dalam set hasil yang lebih mudah untuk dikueri dan difilter, yang menyajikan session_id dan request_id. Misalnya, untuk menampilkan kueri terbaru yang dikirimkan session_id 66 dan request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Lihat kolom blocking_session_id di sys.dm_exec_requests. Jika blocking_session_id = 0, sesi tidak sedang diblokir. Saat sys.dm_exec_requests hanya berisikan daftar permintaan yang saat ini dijalankan, koneksi apa pun (aktif atau tidak) akan dicantumkan di sys.dm_exec_sessions. Bangun dari gabungan bersama antara sys.dm_exec_requests dan sys.dm_exec_sessions ini di kueri berikutnya.

  • Jalankan kueri sampel ini untuk menemukan kueri yang dieksekusi secara aktif dan teks batch SQL atau teks buffer input saat ini menggunakan sys.dm_exec_sql_text atau sys.dm_exec_input_buffer DMV. Jika data yang ditampilkan oleh bidang textsys.dm_exec_sql_text adalah NULL, kueri saat ini tidak dijalankan. Dalam hal ini, bidang event_infosys.dm_exec_input_buffer berisi untai (karakter) perintah terakhir yang diteruskan ke mesin SQL. Kueri ini juga dapat digunakan untuk mengidentifikasi sesi yang memblokir sesi lain, termasuk session_ids diblokir per session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Jalankan kueri sampel yang lebih rumit ini, yang disediakan oleh Dukungan Microsoft, untuk mengidentifikasi kepala rantai pemblokiran beberapa sesi, termasuk teks kueri sesi yang terlibat dalam rantai pemblokiran.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Merujuklah pada sys.dm_os_waiting_tasks yang ada di lapisan alur/tugas SQL. Ini menampilkan informasi tentang permintaan jenis tunggu SQL apa yang saat ini sedang berjalan. Seperti sys.dm_exec_requests, hanya permintaan aktif yang ditampilkan oleh sys.dm_os_waiting_tasks.

Catatan

Untuk informasi lebih lanjut tentang jenis tunggu termasuk statistik tunggu agregat dari waktu ke waktu, lihat DMV sys.dm_db_wait_stats. DMV ini menampilkan statistik tunggu agregat untuk database saat ini saja.

  • Gunakan DMV sys.dm_tran_locks untuk informasi yang lebih terperinci tentang kunci apa yang telah ditempatkan oleh kueri. DMV ini dapat mengembalikan data dalam jumlah besar pada database produksi, dan berguna untuk mendiagnosis kunci apa yang saat ini ditahan.

Karena INNER JOIN pada sys.dm_os_waiting_tasks, kueri berikut membatasi output dari sys.dm_tran_locks hanya untuk permintaan yang saat ini diblokir, status tunggu, dan penguncian:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';
  • Dengan DMV, menyimpan hasil kueri dari waktu ke waktu akan menyediakan poin data yang akan memungkinkan Anda untuk meninjau pemblokiran selama interval waktu yang ditentukan untuk mengidentifikasi pemblokiran atau tren yang terus-menerus.

Mengumpulkan informasi dari Peristiwa yang Diperpanjang

Selain informasi sebelumnya, sering diperlukan untuk menangkap jejak aktivitas di server untuk menyelidiki masalah pemblokiran secara menyeluruh di Azure SQL Database. Misalnya, jika sesi menjalankan beberapa pernyataan dalam transaksi, hanya pernyataan terakhir yang dikirimkan yang akan diwakili. Namun, salah satu pernyataan sebelumnya mungkin menjadi alasan kunci masih ditahan. Pelacakan akan memungkinkan Anda untuk melihat semua perintah yang dijalankan oleh sesi dalam transaksi saat ini.

Ada dua cara untuk memperoleh jejak di SQL Server; Extended Events (XEvents) dan Profiler Traces. Namun, SQL Server Profiler adalah teknologi pelacakan yang tidak didukung untuk Azure SQL Database. Extended Events adalah teknologi pelacakan yang lebih baru yang memungkinkan lebih banyak fleksibilitas dan lebih sedikit dampak ke sistem yang diamati, dan antarmukanya diintegrasikan ke dalam SQL Server Management Studio (SSMS).

Lihat dokumen yang menjelaskan cara menggunakan Extended Events New Session Wizard di SQL Server Management Studio. Namun untuk database Azure SQL, SQL Server Management Studio menyediakan subfolder Extended Events di bawah setiap database di Object Explorer. Gunakan panduan sesi Extended Events untuk mengambil kejadian yang berharga ini:

  • Kesalahan Kategori:

    • Perhatian
    • Error_reported
    • Execution_warning

  • Kategori Peringatan:

    • Missing_join_predicate

  • Kategori Eksekusi:

    • Rpc_completed
    • Rpc_starting
    • sql_batch_completed
    • Sql_batch_starting

  • Kategori deadlock_monitor

    • database_xml_deadlock_report

  • Sesi kategori

    • Existing_connection
    • Masuk
    • Keluar

Catatan

Untuk informasi detail tentang kebuntuan, lihat Menganalisis dan mencegah kebuntuan di Azure SQL Database.

Mengidentifikasi dan mengatasi skenario pemblokiran umum

Dengan memeriksa informasi sebelumnya, Anda dapat menentukan penyebab sebagian besar masalah pemblokiran. Sisa artikel ini adalah diskusi tentang cara menggunakan informasi ini untuk mengidentifikasi dan menyelesaikan beberapa skenario pemblokiran umum. Diskusi ini mengasumsikan Anda telah menggunakan skrip pemblokiran (rujukan sebelumnya) untuk mengambil informasi tentang SPID pemblokiran dan telah menangkap aktivitas aplikasi menggunakan sesi XEvent.

Menganalisis pemblokiran data

  • Periksa output sys.dm_exec_requests dan sys.dm_exec_sessions DMV untuk menentukan kepala dari rantai pemblokiran, menggunakan blocking_these dan session_id. Ini akan mengidentifikasi dengan jelas permintaan mana yang diblokir dan mana yang memblokir. Lihat lebih jauh ke sesi yang diblokir dan diblokir. Apakah ada kesamaan atau akar untuk rantai pemblokiran? Kemungkinannya akan mirip, dan satu atau lebih sesi yang terlibat dalam rantai pemblokiran melakukan operasi penulisan.

  • Periksa output sys.dm_exec_requests dan sys.dm_exec_sessions DMV untuk informasi tentang SPID di kepala rantai pemblokiran. Cari bidang berikut:

    • sys.dm_exec_requests.status
      Kolom ini memperlihatkan status permintaan tertentu. Biasanya, status tidur menunjukkan bahwa SPID telah menyelesaikan eksekusi dan sedang menunggu aplikasi untuk mengirimkan kueri atau batch lain. Status yang dapat dijalankan atau berjalan menunjukkan bahwa SPID sedang memproses kueri. Tabel berikut ini memberikan penjelasan singkat tentang berbagai nilai status.
    Status Arti
    Latar belakang SPID menjalankan tugas latar belakang, seperti deteksi kebuntuan, penulis log, atau pos pemeriksaan.
    Tidur SPID saat ini tidak sedang berjalan. Ini biasanya menunjukkan bahwa SPID sedang menunggu perintah dari aplikasi.
    Berjalan SPID saat ini berjalan pada penjadwal.
    Dapat dijalankan SPID berada dalam antrean yang bisa dijalankan dari penjadwal dan menunggu untuk mendapatkan waktu penjadwal.
    Ditangguhkan SPID sedang menunggu sumber, seperti kunci atau kait.

    • sys.dm_exec_sessions.open_transaction_count
      Bidang ini memberi tahu Anda jumlah transaksi terbuka dalam sesi ini. Jika nilai ini lebih besar dari 0, SPID berada dalam transaksi terbuka dan mungkin memegang kunci yang diperoleh oleh pernyataan apa pun dalam transaksi.

    • sys.dm_exec_requests.open_transaction_count
      Sama seperti itu, bidang ini memberi tahu Anda jumlah transaksi terbuka dalam permintaan ini. Jika nilai ini lebih besar dari 0, SPID berada dalam transaksi terbuka dan mungkin memegang kunci yang diperoleh oleh pernyataan apa pun dalam transaksi.

    • sys.dm_exec_requests.wait_type, wait_time, dan last_wait_type
      Jikasys.dm_exec_requests.wait_typeadalah NULL, saat ini permintaannya tidak sedang menunggu apa pun dan nilai last_wait_type menunjukkan wait_type terakhir yang ditemui permintaan. Untuk informasi selengkapnya tentang sys.dm_os_wait_stats dan deskripsi jenis tunggu yang paling umum, lihat sys.dm_os_wait_stats. Nilai wait_time dapat digunakan untuk menentukan apakah permintaan membuat kemajuan. Saat kueri terhadap tabel sys.dm_exec_requests menampilkan nilai di kolom wait_time yang nilainya kurang dari nilai wait_time kueri sys.dm_exec_requests sebelumnya, hal ini menunjukkan bahwa penguncian sebelumnya telah diperoleh dan dirilis dan sekarang menunggu penguncian baru (dengan asumsi wait_time bukan nol). Ini dapat diverifikasi dengan membandingkan wait_resource antara output sys.dm_exec_requests yang menampilkan sumber daya yang permintaannya sedang menunggu.

    • sys.dm_exec_requests.wait_resource Bidang ini menunjukkan sumber daya yang ditunggu oleh permintaan yang diblokir. Tabel berikut ini mencantumkan wait_resource format umum dan artinya:

    Sumber daya Format Contoh Penjelasan
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 Dalam hal ini, database ID 5 adalah database sampel pub dan objek ID 261575970 adalah tabel judul dan 1 adalah indeks berkluster.
    Halaman DatabaseID:FileID:PageID HALAMAN: 5:1:104 Dalam hal ini, database ID 5 adalah pub, file ID 1 adalah file data utama, dan halaman 104 adalah halaman tabel judul. Untuk mengidentifikasi object_id milik halaman, gunakan fungsi manajemen dinamis sys.dm_db_page_info, yang lewat di DatabaseID, FileId, PageId dari wait_resource.
    Kunci DatabaseID:Hobt_id (Nilai hash untuk kunci indeks) KUNCI: 5:72057594044284928 (3300a4f361aa) Dalam hal ini, database ID 5 adalah Pub, Hobt_ID 72057594044284928 sesuai dengan index_id 2 untuk object_id 261575970 (tabel judul). Gunakan tampilan katalog sys.partitions untuk mengaitkan hobt_id ke index_id dan object_id tertentu. Tidak ada cara untuk melepaskan hash kunci indeks ke nilai kunci tertentu.
    Baris DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Dalam hal ini, database ID 5 adalah pub, file ID 1 adalah file data utama, halaman 104 adalah halaman milik tabel judul, dan slot 3 menunjukkan posisi baris di halaman.
    Mengompilasi DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Dalam hal ini, database ID 5 adalah pub, file ID 1 adalah file data utama, halaman 104 adalah halaman milik tabel judul, dan slot 3 menunjukkan posisi baris di halaman.
    • sys.dm_tran_active_transactions DMV sys.dm_tran_active_transactions berisi data tentang transaksi terbuka yang dapat digabungkan ke DMV lain untuk gambaran lengkap transaksi menunggu penerapan atau putar kembali. Gunakan kueri berikut untuk menampilkan informasi tentang transaksi terbuka, yang digabungkan ke DMV lain termasuk sys.dm_tran_session_transactions. Pertimbangkan status transaksi saat ini, transaction_begin_time, dan data situasional lainnya untuk mengevaluasi apakah itu bisa menjadi sumber pemblokiran.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Kolom lainnya

      Kolom lainnya di ys.dm_exec_sessions dan sys.dm_exec_request dapat memberikan wawasan tentang akar masalah juga. Kegunaannya bervariasi tergantung pada keadaan masalah. Misalnya, Anda dapat menentukan apakah masalah hanya terjadi dari klien tertentu (nama host), pada pustaka jaringan tertentu (net_library), ketika batch terakhir yang diajukan oleh SPID adalah last_request_start_time di sys.dm_exec_sessions, berapa lama permintaan telah berjalan menggunakan start_time di sys.dm_exec_requests, dan sebagainya.

Skenario pemblokiran umum

Tabel di bawah ini memetakan gejala umum untuk kemungkinan penyebabnya.

kolom Waittype, Open_Tran, dan Status merujuk ke informasi yang dikembalikan oleh sys.dm_exec_request, kolom lain dapat dikembalikan dengan sys.dm_exec_sessions. kolom "Penyelesaian?" menunjukkan apakah pemblokiran akan diselesaikan sendiri atau tidak, atau apakah sesi harus diakhiri melalui perintah KILL. Untuk informasi selengkapnya, lihat Pernyataan KILL (T-SQL).

Skenario Waittype Open_Tran Status Terselesaikan? Gejala Lain
1 NOT_NULL >=0 dapat dijalankan Ya, saat kueri selesai. Di kolom sys.dm_exec_sessions, reads, cpu_time, dan/atau memory_usage akan meningkat seiring waktu. Durasi untuk kueri akan tinggi ketika selesai.
2 NULL >0 tidur Tidak, tapi SPID bisa dihentikan. Sinyal perhatian dapat dilihat pada sesi Extended Event untuk SPID ini, yang menunjukkan waktu habis kueri atau pembatalan telah terjadi.
3 NULL >=0 dapat dijalankan Tidak. Tidak akan teratasi sampai klien mengambil semua baris atau menutup koneksi. SPID dapat diakhiri, tetapi mungkin memakan waktu hingga 30 detik. Jika open_transaction_count = 0, dan SPID memegang kunci sementara tingkat isolasi transaksi adalah default (BACA DILAKUKAN), ini adalah kemungkinan penyebabnya.
4 Bervariasi >=0 dapat dijalankan Tidak. Tidak akan teratasi sampai klien membatalkan kueri atau menutup koneksi. SPID dapat diakhiri, tetapi mungkin memakan waktu hingga 30 detik. Kolom hostname di sys.dm_exec_sessions untuk SPID di kepala rantai pemblokiran akan sama dengan salah satu SPID yang diblokirnya.
5 NULL >0 putar kembali Ya. Sinyal perhatian dapat dilihat pada sesi Extended Events untuk SPID ini, yang menunjukkan waktu habis kueri atau pembatalan telah terjadi, atau hanya pernyataan putar kembali telah dikeluarkan.
6 NULL >0 tidur Akhirnya. Ketika Windows NT menentukan bahwa sesi tidak lagi aktif, koneksi Azure SQL Database akan berhenti. Nilai last_request_start_time dalam sys.dm_exec_sessions jauh lebih awal dari waktu saat ini.

Skenario pemblokiran terperinci

  1. Pemblokiran yang disebabkan oleh kueri yang berjalan normal dengan waktu eksekusi yang lama

    Resolusi: Solusi untuk jenis masalah pemblokiran ini adalah mencari cara untuk mengoptimalkan kueri. Sebenarnya, kelas masalah pemblokiran ini mungkin hanya masalah performa, dan ini mengharuskan Anda untuk mengejarnya. Untuk informasi tentang pemecahan masalah kueri tertentu yang berjalan lambat, lihat Cara memecahkan masalah kueri yang berjalan lambat di SQL Server. Untuk informasi selengkapnya, lihat Memantau dan Menyelaraskan Performa.

    Laporan dari Penyimpanan Kueri di SQL Server Management Studio juga merupakan alat yang sangat direkomendasikan dan berharga untuk mengidentifikasi kueri yang paling mahal, rencana eksekusi suboptimal. Tinjau juga bagian Intelligent Performance di portal Microsoft Azure untuk database Azure SQL, termasuk Query Performance Insight.

    Jika kueri hanya melakukan operasi SELECT, pertimbangkan untuk menjalankan pernyataan di bawah isolasi snapshot jika diaktifkan di database Anda, terutama jika RCSI telah dinonaktifkan. Seperti saat RCSI diaktifkan, kueri yang membaca data tidak memerlukan kunci bersama (S) di bawah tingkat isolasi snapshot. Selain itu, isolasi snapshot memberikan konsistensi tingkat transaksi untuk semua pernyataan dalam transaksi multi-pernyataan eksplisit. Isolasi snapshot mungkin sudah diaktifkan di database Anda. Isolasi snapshot juga dapat digunakan dengan kueri yang melakukan modifikasi, tetapi Anda harus menangani konflik pembaruan.

    Jika Anda memiliki kueri jangka panjang yang memblokir pengguna lain dan tidak dapat dioptimalkan, pertimbangkan untuk memindahkannya dari lingkungan OLTP ke sistem pelaporan, sebuah replika database baca-saja yang sinkron.

  2. Pemblokiran yang disebabkan oleh SPID tidur yang memiliki transaksi yang belum dilakukan

    Jenis pemblokiran ini sering kali dapat diidentifikasi oleh SPID yang sedang tidur atau menunggu perintah, tetapi tingkat berlapis transaksinya (@@TRANCOUNT, open_transaction_count dari sys.dm_exec_requests) lebih besar dari nol. Ini dapat terjadi jika aplikasi mengalami time-out kueri, atau mengeluarkan pembatalan tanpa mengeluarkan jumlah pernyataan ROLLBACK dan/atau COMMIT yang diperlukan. Ketika SPID menerima waktu habis kueri atau pembatalan, SPID akan mengakhiri kueri dan batch saat ini, tetapi tidak secara otomatis menggulung balik atau melakukan transaksi. Aplikasi ini bertanggung jawab untuk ini, karena Azure SQL Database tidak dapat mengasumsikan bahwa seluruh transaksi harus digulirkan kembali karena satu kueri dibatalkan. Batas waktu atau pembatalan kueri akan muncul sebagai peristiwa sinyal PERHATIAN untuk SPID di sesi Extended Event.

    Untuk menunjukkan transaksi eksplisit yang belum dilakukan, ajukan kueri berikut:

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

    Kemudian, jalankan kueri ini di jendela yang sama:

    SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

    Output kueri kedua menunjukkan bahwa tingkat lapisan transaksi adalah satu. Semua kunci yang diperoleh dalam transaksi masih ditahan sampai transaksi dilakukan atau digulirkan kembali. Jika aplikasi secara eksplisit membuka dan melakukan transaksi, komunikasi atau kesalahan lain dapat meninggalkan sesi dan transaksinya dalam keadaan terbuka.

    Gunakan skrip sebelumnya dalam artikel ini berdasarkan sys.dm_tran_active_transactions untuk mengidentifikasi transaksi yang saat ini belum diterapkan di seluruh instans.

    Resolusi:

    • Sebenarnya, kelas masalah pemblokiran ini mungkin hanya masalah performa, dan ini mengharuskan Anda untuk menangani performa. Jika waktu eksekusi kueri dapat berkurang, waktu habis atau batal kueri tidak akan terjadi. Penting bahwa aplikasi ini dapat menangani skenario waktu habis atau batal jika muncul, tetapi Anda mungkin juga mendapat manfaat dari memeriksa performa kueri.

    • Aplikasi harus mengelola tingkat lapisan transaksi dengan benar, atau itu dapat menyebabkan masalah pemblokiran setelah pembatalan kueri dengan cara ini. Pertimbangkan hal berikut:

      • Dalam penghandel kesalahan aplikasi klien, jalankan IF @@TRANCOUNT > 0 ROLLBACK TRAN apabila ada kesalahan, bahkan jika aplikasi klien tidak percaya transaksi terbuka. Memeriksa transaksi terbuka diperlukan, karena prosedur yang disimpan yang disebut selama batch bisa saja memulai transaksi tanpa sepengetahuan aplikasi klien. Di kondisi tertentu, seperti membatalkan kueri, mencegah prosedur dari mengeksekusi melewati pernyataan saat ini, jadi bahkan jika prosedur memiliki logika untuk memeriksa IF @@ERROR <> 0 dan membatalkan transaksi, kode pembatalan ini tidak akan dieksekusi dalam kasus seperti itu.
      • Jika kumpulan koneksi digunakan dalam aplikasi yang membuka koneksi dan menjalankan sejumlah kecil kueri sebelum melepaskan koneksi kembali ke kumpulan, seperti aplikasi berbasis Web, menonaktifkan kumpulan koneksi untuk sementara dapat membantu meringankan masalah sampai aplikasi klien dimodifikasi untuk menangani kesalahan dengan tepat. Dengan menonaktifkan kumpulan koneksi, melepaskan koneksi akan menyebabkan pemutusan koneksi Database Azure SQL secara fisik, yang mengakibatkan server menggulirkan kembali transaksi yang terbuka.
      • Gunakan SET XACT_ABORT ON untuk koneksi, atau dalam prosedur yang disimpan yang memulai transaksi dan tidak membersihkan mengikuti kesalahan. Jika terjadi kesalahan run-time, pengaturan ini akan membatalkan transaksi terbuka dan menampilkan kontrol kepada klien. Untuk informasi lebih lanjut, tinjau SET XACT_ABORT (Transact-SQL).

    Catatan

    Koneksi tidak diatur ulang sampai digunakan kembali dari kumpulan koneksi, jadi ada kemungkinan bahwa pengguna dapat membuka transaksi dan kemudian melepaskan koneksi ke kumpulan koneksi, tetapi mungkin tidak digunakan kembali selama beberapa detik, selama waktu itu transaksi akan tetap terbuka. Jika koneksi tidak digunakan kembali, transaksi akan dibatalkan ketika koneksi habis dan dihapus dari kumpulan koneksi. Dengan demikian, optimal bagi aplikasi klien untuk membatalkan transaksi dalam penghandel kesalahan transaksi atau gunakan SET XACT_ABORT ON untuk menghindari potensi penundaan ini.

    Perhatian

    Mengikuti SET XACT_ABORT ON, pernyataan T-SQL yang mengikuti pernyataan yang menyebabkan kesalahan tidak akan dijalankan. Ini dapat mempengaruhi alur yang dimaksudkan dari kode yang ada.

  3. Pemblokiran yang disebabkan oleh SPID yang aplikasi klien yang sesuai tidak mengambil semua baris hasil untuk penyelesaian

    Setelah mengirim kueri ke server, semua aplikasi harus segera mengambil semua baris hasil hingga selesai. Jika aplikasi tidak mengambil semua baris hasil, kunci dapat ditinggalkan di tabel, yang lalu memblokir pengguna lain. Jika Anda menggunakan aplikasi yang secara transparan mengirimkan pernyataan SQL ke server, aplikasi harus mengambil semua baris hasil. Jika tidak (dan jika tidak dapat dikonfigurasi untuk melakukannya), Anda mungkin tidak dapat menyelesaikan masalah pemblokiran. Untuk menghindari masalah, Anda dapat membatasi aplikasi yang berperilaku buruk pada pelaporan atau database pendukung keputusan, terpisah dari database OLTP utama.

    Dampak dari skenario ini berkurang ketika read committed snapshot diaktifkan di database, yang merupakan konfigurasi default di Azure SQL Database. Pelajari selengkapnya di bagian Memahami pemblokiran artikel ini.

    Catatan

    Lihat panduan untuk mencoba kembali logika untuk aplikasi yang menyambungkan ke Azure SQL Database.

    Resolusi: Aplikasi harus ditulis ulang untuk mengambil semua baris hasil hingga selesai. Ini tidak mengesampingkan penggunaan OFFSET dan FETCH dalam klausul ORDER BY dari kueri untuk melakukan halaman sisi server.

  4. Pemblokiran yang disebabkan oleh sesi dalam keadaan pemutaran kembali

    Kueri modifikasi data yang dihentikan, atau dibatalkan di luar transaksi yang ditentukan pengguna, akan digulirkan kembali. Ini juga dapat terjadi sebagai efek samping dari pemutusan sesi jaringan klien, atau ketika permintaan dipilih sebagai korban kebuntuan. Pemutaran kembali sering kali dapat diidentifikasi dengan mengamati output sys.dm_exec_requests yang mungkin mengindikasikan perintah ROLLBACK dan kolom percent_complete dapat menunjukkan kemajuan.

    Berkat fitur Accelerated Database Recovery yang diperkenalkan pada tahun 2019, pemutaran kembali yang panjang harusnya jarang terjadi.

    Resolusi: Tunggu SPID selesai menggulirkan kembali perubahan yang dibuat.

    Untuk menghindari situasi ini, jangan melakukan operasi penulisan batch besar atau operasi pembuatan indeks atau pemeliharaan selama jam sibuk pada sistem OLTP. Jika memungkinkan, lakukan operasi tersebut selama periode aktivitas rendah.

  5. Pemblokiran yang disebabkan oleh koneksi tanpa sumber

    Jika kesalahan perangkap aplikasi klien atau stasiun kerja klien dimulai ulang, sesi jaringan ke server mungkin tidak segera dibatalkan dalam beberapa kondisi. Dari perspektif Azure SQL Database, klien masih tampak hadir, dan setiap kunci yang diperoleh mungkin masih dipertahankan. Untuk informasi selengkapnya, lihat Cara memecahkan masalah koneksi tanpa sumber di SQL Server.

    Resolusi: Jika aplikasi klien terputus tanpa membersihkan sumbernya dengan tepat, Anda dapat mengakhiri SPID dengan menggunakan perintah KILL. Perintah KILL mengambil nilai SPID sebagai input. Misalnya, untuk menghentikan SPID 99, keluarkan perintah berikut:

    KILL 99

Lihat juga

Langkah berikutnya