INF: Memahami dan memecahkan masalah pemblokiran SQL Server

• Mengunci di mesin Database
• Menyesuaikan mengunci dan versi baris
• Kunci mode
• Kunci kompatibilitas
• Baris berbasis versi tingkat isolasi di mesin Database
• Mengendalikan transaksi (Database Engine)

• SPID memegang kunci pada set sumber daya untuk diperpanjang jangka waktu sebelum melepaskan mereka. Jenis ini menghalangi menyelesaikan sendiri dari waktu ke waktu, tetapi dapat menyebabkan penurunan kinerja.
• SPID memegang kunci pada set sumber daya dan tidak pernah melepaskan mereka. Jenis ini menghalangi tidak memecahkan itu sendiri dan mencegah akses ke terpengaruh sumber daya tanpa batas.

Pengumpulan memblokir informasi

1. Mengidentifikasi SPID (Session ID) kepala rantai memblokir dan pernyataan SQL.
   Selain untuk menggunakan script di artikel Basis Pengetahuan yang disebutkan sebelumnya, Anda dapat mengidentifikasi kepala rantai memblokir dengan menggunakan fitur yang disediakan melalui SQL Server Management Studio. Untuk melakukannya, gunakan salah satu dari metode berikut ini:
   • Klik kanan objek server, memperluas Laporan, memperluas Laporan standar, lalu klik Kegiatan-semua transaksi yang Pemblokir. Laporan ini menunjukkan transaksi di kepala menghalangi rantai. Jika Anda memperluas transaksi, laporan akan menunjukkan transaksi yang diblokir oleh kepala transaksi. Laporan ini juga akan menampilkan "Menghalangi SQL pernyataan" dan "Pernyataan SQL Blocked."
   • Menggunakan DBCC INPUTBUFFER (<spid>) untuk menemukan pernyataan terakhir yang telah disampaikan oleh SPID.</spid>
2. Menemukan tingkat nesting transaksi dan status proses memblokir SPID.
   Transaksi yang bersarang tingkat SPID tersedia dalam @@ TRANCOUNT variabel global. Namun, yang dapat ditentukan dari luar SPID oleh query sysprocesses tabel sebagai berikut:
   
   

   SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
   go
   						

   Nilai kembali adalah nilai @@ TRANCOUNT untuk SPID. Ini menunjukkan tingkat nesting transaksi untuk memblokir SPID, yang pada gilirannya dapat menjelaskan Mengapa itu memegang kunci. Misalnya, jika nilai lebih besar dari nol, SPID adalah di tengah-tengah transaksi (dalam hal ini diperkirakan bahwa mempertahankan kunci tertentu itu telah mengambil alih, tergantung pada transaksi isolasi tingkat).
   
   Anda juga dapat memeriksa untuk melihat apakah ada jangka panjang membuka transaksi ada di database dengan menggunakan DBCC OPENTRANdatabase_name.

Pengumpulan informasi jejak SQL Server Profiler

1. Buka SQL Server Profiler.
2. Pada Berkas menu, titik Baru, lalu klik Jejak.
3. Pada General tab, menetapkan nama kecil dan nama file untuk menangkap data.
   
   Penting Jejak file harus ditulis ke disk cepat lokal atau bersama. Menghindari melacak untuk drive disk atau jaringan yang lambat. Juga pastikan Server proses jejak data dipilih.
4. Pada Peristiwa seleksi tab, klik untuk memilih Tampilkan semua peristiwa dan Tampilkan semua kolom kotak centang.
5. Pada Peristiwa seleksi tab, menambah jenis peristiwa yang tercantum dalam tabel 1 jejak Anda.
   
   Selain itu, Anda mungkin termasuk jenis acara tambahan yang didaftar di tabel 2 untuk informasi lebih lanjut. Jika Anda menjalankan dalam lingkungan produksi volume tinggi, Anda dapat memutuskan untuk menggunakan hanya peristiwa-peristiwa dalam tabel 1, karena mereka biasanya cukup untuk memecahkan sebagian besar masalah pemblokiran. Termasuk acara tambahan dalam tabel 2 mungkin membuatnya lebih mudah untuk dengan cepat menentukan sumber masalah (atau peristiwa-peristiwa ini mungkin diperlukan untuk mengidentifikasi pernyataan pelakunya dalam prosedur multi-statement). Namun, termasuk peristiwa dalam tabel 2 juga akan menambah beban pada sistem dan meningkatkan jejak output ukuran.

Kembali ke atas

Mengidentifikasi dan memecahkan Common menghalangi skenario

Melihat Output Script memblokir

Memeriksa sys.sysprocesses output untuk menentukan kepala rantai memblokir

SPIDs at the head of blocking chains

Memeriksa sys.sysprocesses output untuk informasi tentang SPIDs di kepala rantai menghalangi.

DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )

Memeriksa output DBCC INPUTBUFFER.

Melihat Profiler Data

• Apa perintah telah SPID di kepala rantai memblokir dijalankan dalam transaksi saat ini?
  Menyaring jejak data untuk SPID tertentu yang adalah kepala dari rantai memblokir (pada Berkas menu, klik Properti; kemudian pada Filter tab menentukan nilai SPID). Anda kemudian dapat memeriksa perintah itu telah dijalankan sebelum ke waktu itu menghalangi SPIDs lain. Jika Anda menyertakan Transaksi peristiwa, mereka dapat dengan mudah mengidentifikasi ketika transaksi dimulai. Jika tidak, Anda dapat mencari Teks kolom untuk BEGIN, Simpan, COMMIT, atau transaksi ROLLBACK operasi. Penggunaan open_tran nilai dari sysprocesses tabel untuk memastikan bahwa Anda menangkap semua peristiwa transaksi. Mengetahui perintah dieksekusi dan konteks transaksi akan memungkinkan Anda untuk menentukan mengapa SPID memegang kunci.
  
  Ingat, Anda dapat menghapus peristiwa dan data kolom. Alih-alih melihat kedua mulai dan selesai peristiwa, pilih salah satu. Jika memblokir SPIDs tidak disimpan prosedur, menghapusSP: mulai atau SP: selesai peristiwa; The SQLBatch dan RPC acara akan menunjukkan prosedur panggilan. Hanya melihat peristiwa SP ketika Anda perlu untuk melihat bahwa tingkat detail.
• Apakah durasi permintaan untuk SPIDs di kepala dari menghalangi rantai?
  Jika Anda menyertakan acara selesai di atas, Durasi kolom akan menunjukkan waktu eksekusi query. Ini dapat membantu Anda mengidentifikasi berjalan lama query yang menyebabkan menghalangi. Untuk menentukan mengapa permintaan kinerja perlahan-lahan, lihat CPU, Baca, dan Menulis kolom, serta Rencana pelaksanaan acara.

Mengkategorikan skenario memblokir umum

Umum menghalangi skenario dan resolusi

1. Memblokir disebabkan oleh biasanya menjalankan Query dengan waktu eksekusi lama
   
   Resolusi:
   Solusi untuk masalah memblokir jenis ini adalah untuk mencari cara untuk mengoptimalkan query. Sebenarnya, ini kelas masalah pemblokiran mungkin hanya menjadi masalah kinerja, dan memerlukan Anda untuk mengejar seperti. Untuk informasi pada pemecahan masalah tertentu berjalan lambat query, lihat artikel Basis Pengetahuan Microsoft berikut:
   243589  (http://support.microsoft.com/kb/243589/ ) Bagaimana memecahkan masalah berjalan lambat query di SQL Server 7.0 atau versi yang lebih baru
   Untuk kinerja keseluruhan aplikasi pemecahan masalah, lihat artikel Basis Pengetahuan berikut:
   224587  (http://support.microsoft.com/kb/224587/ ) CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server
   Untuk informasi lebih lanjut, lihat Kinerja pemantauan dan Tuning How-to topik SQL Server 2008 buku Online topik di Website MSDN berikut:
   http://MSDN.Microsoft.com/en-us/library/ms187830.aspx (http://msdn.microsoft.com/en-us/library/ms187830.aspx)
   Jika Anda memiliki pertanyaan berjalan lama yang menghalangi pengguna lain dan tidak dapat dioptimalkan, mempertimbangkan bergerak dari OLTP lingkungan untuk sistem pendukung keputusan.
2. Memblokir disebabkan oleh SPID tidur yang kehilangan jejak dari tingkat Nesting transaksi
   
   Jenis ini menghalangi dapat sering diidentifikasi oleh SPID yang tidur atau menunggu perintah, namun transaksi yang bersarang tingkat (@@ TRANCOUNT, open_tran dari sysprocesses) lebih besar dari nol. Hal ini dapat terjadi jika aplikasi pengalaman permintaan timeout, atau masalah Batal tanpa juga mengeluarkan dibutuhkan sejumlah pernyataan ROLLBACK dan/atau KOMIT. Ketika SPID menerima permintaan timeout atau Batal, hal itu akan mengakhiri permintaan saat ini dan batch, tetapi secara otomatis memutar kembali atau melakukan transaksi. Aplikasi bertanggung jawab untuk ini, seperti SQL Server tidak bisa berasumsi bahwa seluruh transaksi harus digulung kembali hanya karena untuk permintaan tunggal yang dibatalkan. Query timeout atau Batal akan muncul sebagai acara sinyal perhatian untuk SPID di Profiler jejak.
   
   Untuk menunjukkan hal ini, masalah sederhana query berikut dari Query Analyzer:
   
   

   BEGIN TRAN 
   SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
   
   -- Issue this after canceling query
   SELECT @@TRANCOUNT
   ROLLBACK TRAN
   						

   Sementara query mengeksekusi, klik merah Batalkan tombol. Setelah permintaan dibatalkan, pilih @@ TRANCOUNT menunjukkan bahwa tingkat bersarang transaksi adalah salah satu. Ini telah menghapus atau UPDATE permintaan, atau HOLDLOCK telah digunakan pada pilih, semua kunci yang diperoleh akan masih akan diadakan. Bahkan dengan pertanyaan di atas, jika permintaan lain telah diperoleh dan memegang kunci sebelumnya dalam transaksi, mereka akan masih diadakan ketika di atas Pilih dibatalkan.
   
   Resolusi:
   
   
   • Aplikasi harus benar mengelola transaksi bersarang tingkat, atau mereka dapat menyebabkan masalah pemblokiran setelah pembatalan permintaan dengan cara ini. Hal ini dapat dicapai dalam beberapa cara:
     1. Dalam penangan kesalahan aplikasi klien, Kirim jika @@ TRANCOUNT mengatakan 0 ROLLBACK TRAN berikut kesalahan, bahkan jika aplikasi klien tidak percaya transaksi terbuka. Ini diperlukan, karena disimpan prosedur yang disebut selama bets bisa telah mulai transaksi tanpa pengetahuan aplikasi klien. Catatan yang tertentu kondisi, seperti membatalkan permintaan, mencegah prosedur mengeksekusi melewati pernyataan saat ini, jadi bahkan jika prosedur yang memiliki logika untuk memeriksa jika @@ KESALAHAN <> 0 dan abort transaksi, kode rollback ini tidak akan dijalankan dalam kasus tersebut.
     2. Menggunakan SET XACT_ABORT ON untuk sambungan, atau di disimpan prosedur yang mulai transaksi dan tidak membersihkan berikut kesalahan. Dalam hal terdapat galat run-time, pengaturan ini akan membatalkan setiap terbuka transaksi dan kembali kontrol untuk klien. Catatan bahwa pernyataan T-SQL Setelah pernyataan yang menyebabkan kesalahan tidak akan dijalankan.
     3. Jika koneksi penggabungan sedang digunakan dalam aplikasi yang membuka koneksi dan menjalankan sejumlah kecil pertanyaan sebelum melepaskan sambungan kembali ke kolam renang, seperti aplikasi berbasis Web, Sementara menonaktifkan koneksi penggabungan dapat membantu meringankan masalah sampai aplikasi klien yang dimodifikasi untuk menangani kesalahan yang tepat. Oleh menonaktifkan koneksi penggabungan, melepaskan sambungan akan menyebabkan fisik logout koneksi SQL Server, mengakibatkan server bergulir kembali setiap membuka transaksi.
     4. Jika koneksi penggabungan diaktifkan dan server tujuan adalah SQL Server 2000, upgrade komputer klien untuk MDAC 2.6 atau kemudian mungkin bermanfaat. Versi ini dari komponen MDAC menambahkan kode untuk ODBC driver dan penyedia OLE DB sehingga koneksi akan "reset" sebelum digunakan. Panggilan ini untuk sp_reset_connection aborts apapun memprakarsai server transaksi (DTC transaksi diprakarsai oleh aplikasi klien yang tidak terpengaruh), me-reset default database, SET pilihan, dan sebagainya. Catatan bahwa sambungan tidak reset sampai kembali dari kolam sambungan, Jadi sangat mungkin bahwa pengguna dapat membuka transaksi dan kemudian melepaskan sambungan ke koneksi kolam renang, tapi itu mungkin tidak dapat digunakan kembali untuk beberapa detik, selama waktu transaksi akan tetap terbuka. Jika sambungan adalah tidak kembali, transaksi akan dibatalkan ketika koneksi kali keluar dan akan dihapus dari kolam sambungan. Dengan demikian, itu optimal untuk klien aplikasi untuk membatalkan transaksi di handler kesalahan mereka atau menggunakan SET XACT_ABORT ON untuk menghindari penundaan ini potensi.
   • Sebenarnya, ini kelas masalah pemblokiran juga mungkin masalah kinerja, dan memerlukan Anda untuk mengejar seperti. Jika query waktu eksekusi dapat berkurang, permintaan timeout atau Batal tidak akan terjadi. Sangat penting bahwa aplikasi akan mampu menangani batas waktu atau membatalkan skenario harus mereka muncul, tetapi Anda juga dapat memperoleh manfaat dari memeriksa kinerja dari query.
3. Memblokir disebabkan oleh SPID aplikasi klien yang bersangkutan tidak mengambil semua hasil baris untuk penyelesaian
   
   Setelah mengirim permintaan ke server, semua aplikasi harus segera mengambil semua hasil baris untuk penyelesaian. Jika sebuah aplikasi tidak tidak mengambil semua hasil baris, kunci dapat meninggalkan meja, menghalangi lain pengguna. Jika Anda menggunakan aplikasi yang transparan mengajukan SQL pernyataan ke server, aplikasi harus mengambil semua hasil baris. Jika itu tidak (dan jika tidak dikonfigurasi untuk melakukannya), Anda mungkin tidak dapat mengatasi masalah pemblokiran. Untuk menghindari masalah, Anda dapat membatasi berperilaku buruk aplikasi untuk pelaporan atau dukungan keputusan database.
   
   Resolusi:
   
   Aplikasi harus ditulis ulang untuk mengambil semua baris hasil penyelesaian.
4. Memblokir disebabkan oleh kebuntuan klien/Server yang terdistribusi
   
   Tidak seperti kebuntuan konvensional, kebuntuan didistribusikan ini tidak dapat dideteksi menggunakan RDBMS lock manager. Hal ini disebabkan oleh kenyataan bahwa hanya satu dari sumber-sumber yang terlibat dalam kebuntuan adalah kunci SQL Server. The sisi lain dari kebuntuan adalah di level aplikasi klien, atas SQL yang Server tidak memiliki kontrol. Berikut adalah dua contoh bagaimana hal ini dapat terjadi, dan kemungkinan cara aplikasi dapat menghindarinya.
   
   
   1. Klien/Server didistribusikan kebuntuan dengan satu klien Benang
      Jika klien memiliki beberapa sambungan terbuka, dan satu benang eksekusi, kebuntuan didistribusikan berikut dapat terjadi. Untuk singkatnya, istilah "dbproc" digunakan di sini merujuk kepada struktur sambungan klien.
      
      

       SPID1------blocked on lock------->SPID2
        /\                         (waiting to write results         
        |                           back to client)
        |                                 |
        |                                 |                      Server side
        | ================================|==================================
        |     <-- single thread -->       |                      Client side
        |                                 \/ 
       dbproc1   <-------------------   dbproc2
       (waiting to fetch             (effectively blocked on dbproc1, awaiting
        next row)                     single thread of execution to run)
      								

      Dalam kasus yang ditunjukkan di atas, memiliki satu klien aplikasi thread dua membuka koneksi. Asynchronously mengajukan operasi SQL pada dbproc1. Ini berarti itu tidak menunggu panggilan untuk kembali sebelum melanjutkan. The aplikasi mengajukan lain SQL operasi di dbproc2, kemudian menunggu hasil untuk memulai pengolahan data kembali. Ketika data mulai kembali (dbproc mana pertama Tanggap--menganggap ini adalah dbproc1), proses untuk penyelesaian semua data kembali pada dbproc itu. Ini mengambil hasil dari dbproc1 sampai SPID1 akan diblokir pada kunci yang dipegang oleh SPID2 (karena dua Pertanyaan berjalan asynchronously pada server). Pada titik ini, dbproc1 akan menunggu selamanya untuk lebih banyak data. SPID2 tidak diblokir pada kunci, tetapi mencoba untuk mengirim data ke klien, dbproc2. Namun, dbproc2 secara efektif diblokir di dbproc1 pada lapisan aplikasi sebagai benang satu eksekusi untuk aplikasi ini digunakan oleh dbproc1. Hal ini mengakibatkan kebuntuan SQL Server tidak dapat mendeteksi atau menyelesaikan karena hanya salah satu sumber daya yang terlibat adalah SQL Sumber daya server.
   2. Klien/Server didistribusikan kebuntuan dengan benang per Sambungan
      
      Bahkan jika ada thread terpisah untuk setiap sambungan pada klien, variasi kebuntuan ini didistribusikan mungkin masih terjadi seperti yang ditunjukkan oleh berikut.
      
      

      SPID1------blocked on lock-------->SPID2
        /\                         (waiting on net write)        Server side
        |                                 |
        |                                 |
        | INSERT                          |SELECT
        | ================================|==================================
        |     <-- thread per dbproc -->   |                      Client side
        |                                 \/ 
       dbproc1   <-----data row-------   dbproc2
       (waiting on                     (blocked on dbproc1, waiting for it
        insert)                         to read the row from its buffer)
      								

      Kasus ini serupa dengan contoh A, kecuali dbproc2 dan SPID2 menjalankan sebuah pilih pernyataan dengan tujuan untuk melakukan baris pada waktu pengolahan dan menyerahkan setiap baris melalui penyangga untuk dbproc1 untuk MENYISIPKAN, PEMBARUAN, atau Hapus pernyataan di atas meja yang sama. Akhirnya, SPID1 (melakukan INSERT, UPDATE, atau menghapus) menjadi diblokir di kunci yang dipegang oleh SPID2 (melakukan pilih). SPID2 menulis hasil baris untuk klien dbproc2. Dbproc2 kemudian mencoba untuk lulus baris dalam buffer untuk dbproc1, tetapi menemukan dbproc1 sibuk (itu diblokir menunggu SPID1 untuk menyelesaikan INSERT saat ini, yang diblokir di SPID2). Pada titik ini, dbproc2 diblokir di lapisan aplikasi oleh dbproc1 yang SPID (SPID1) diblokir di database tingkat oleh SPID2. Sekali lagi, Hal ini menyebabkan kebuntuan yang SQL Server tidak dapat mendeteksi atau menyelesaikan karena hanya salah satu sumber daya yang terlibat adalah sumber SQL Server.
   Kedua contoh a dan b adalah dasar masalah yang pengembang aplikasi harus menyadari. Mereka harus kode aplikasi untuk menangani ini kasus yang tepat.
   
   Resolusi:
   
   Dua solusi yang dapat diandalkan adalah untuk menggunakan baik permintaan timeout atau terikat koneksi.
   
   
   • Permintaan Timeout
     Ketika permintaan timeout telah disediakan, jika kebuntuan didistribusikan terjadi, maka akan rusak ketika kemudian timeout terjadi. Melihat DB-perpustakaan atau ODBC dokumentasi untuk informasi lebih lanjut menggunakan permintaan timeout.
   • Terikat koneksi
     Fitur ini memungkinkan klien memiliki beberapa sambungan untuk mengikat mereka ke dalam ruang transaksi tunggal, jadi koneksi tidak memblokir satu sama lain. Untuk selengkapnya, lihat menggunakan" Terikat koneksi"topik dalam SQL Server 7.0 buku Online.
5. Memblokir disebabkan oleh SPID yang ada di "emas", atau Rollback, negara
   
   Permintaan modifikasi data yang dibunuh, atau dibatalkan di luar dari transaksi yang ditetapkan pengguna, akan diluncurkan kembali. Hal ini juga dapat terjadi sebagai efek samping dari restart komputer klien dan sesi jaringan melepaskan. Demikian pula, permintaan dipilih sebagai korban kebuntuan akan diluncurkan kembali. Permintaan modifikasi data sering tidak dapat digulung balik lebih cepat daripada perubahan pada awalnya diterapkan. Sebagai contoh, jika menghapus, memasukkan, atau memperbarui pernyataan telah berjalan selama satu jam, itu bisa mengambil setidaknya satu jam untuk roll kembali. Ini adalah perilaku yang diharapkan, karena perubahan yang dibuat harus benar-benar diluncurkan kembali, atau transaksional dan fisik integritas dalam database akan dikompromikan. Karena hal ini harus terjadi, SQL Server menandai SPID dalam "emas" atau rollback negara (yang berarti itu tidak dapat membunuh atau dipilih sebagai jalan buntu korban). Ini dapat sering diidentifikasi dengan memperhatikan output sp_who, yang dapat menunjukkan perintah ROLLBACK. The Status kolom sys.sysprocesses akan menunjukkan status ROLLBACK, yang juga akan muncul di sp_who output atau SQL Server Management Studio Activity Monitor.
   Resolusi:
   
   Anda harus menunggu SPID untuk menyelesaikan bergulir kembali perubahan yang dibuat.
   
   Jika server dimatikan dalam operasi ini, database akan dalam modus pemulihan setelah restart, dan akan menjadi tidak dapat diakses hingga semua terbuka transaksi diproses. Permulaan Pemulihan diperlukan pada dasarnya sama dengan jumlah waktu per transaksi sebagai run-time pemulihan, dan database adalah tidak dapat diakses selama periode ini. Dengan demikian, memaksa server down untuk memperbaiki SPID dalam keadaan rollback akan sering kontraproduktif.
   
   Untuk menghindari situasi ini, tidak melakukan besar operasi INSERT, UPDATE, atau menghapus batch selama jam sibuk pada OLTP sistem. Jika mungkin, melakukan operasi tersebut selama periode aktivitas rendah.
6. Memblokir disebabkan oleh koneksi yatim
   
   Jika perangkap aplikasi klien atau klien komputer di-restart, sesi jaringan ke server tidak dapat segera membatalkan beberapa kondisi. Dari perspektif server, klien masih muncul untuk hadir, dan kunci apapun memperoleh masih mungkin dipertahankan. Untuk informasi selengkapnya, klik nomor artikel berikut untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
   137983  (http://support.microsoft.com/kb/137983/ ) Bagaimana memecahkan masalah koneksi yatim piatu di SQL Server
   
   Resolusi:
   
   Jika aplikasi klien telah terputus tanpa tepat membersihkan sumber daya, Anda dapat menghentikan SPID dengan menggunakan perintah KILL. Perintah KILL mengambil nilai SPID sebagai masukan. Sebagai contoh, untuk membunuh SPID 9, hanya mengeluarkan perintah berikut:
   
   

   KILL 9
   						

   
   Catatan Perintah KILL mungkin diperlukan waktu hingga 30 detik untuk menyelesaikan, karena untuk interval antara cek untuk perintah KILL.

Kembali ke atas

Aplikasi keterlibatan dalam masalah pemblokiran

Kembali ke atas