Cara mengatasi masalah pemblokiran yang disebabkan oleh eskalasi lock di SQL Server

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 323630 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

RINGKASAN

Eskalasi Lock adalah proses konversi banyak mengunci di halus (seperti baris atau halaman kunci) dalam tabel kunci. Microsoft SQL Server secara dinamis menentukan kapan harus melakukan eskalasi lock. Ketika membuat ini keputusan, SQL Server memperhitungkan jumlah kunci yang diadakan pada scan tertentu, jumlah kunci yang dipegang oleh seluruh transaksi, dan memori yang sedang digunakan untuk kunci dalam sistem secara keseluruhan. Biasanya, SQL Server perilaku default mengakibatkan kunci eskalasi terjadi hanya pada titik-titik di mana itu akan meningkatkan kinerja atau ketika Anda harus mengurangi memori sistem berlebihan kunci ke tingkat yang lebih masuk akal. Namun, beberapa desain aplikasi atau permintaan dapat memicu eskalasi lock pada saat saat ini tidak diinginkan, dan kunci meluas tabel dapat memblokir pengguna lain. Artikel ini membahas bagaimana menentukan apakah eskalasi lock menyebabkan menghalangi dan bagaimana untuk berurusan dengan eskalasi lock tidak diinginkan.

INFORMASI LEBIH LANJUT

Cara menentukan apakah Eskalasi Lock menyebabkan menghalangi

Eskalasi Lock tidak menyebabkan sebagian besar masalah pemblokiran. Pada menentukan apakah eskalasi lock terjadi di sekitar waktu ketika Anda mengalami masalah pemblokiran, mulai SQL Profiler jejak yang mencakup Kunci: eskalasi acara. Jika Anda tidak melihat apapun Kunci: eskalasi peristiwa, eskalasi lock tidak terjadi pada server Anda dan informasi dalam artikel ini tidak berlaku untuk situasi Anda.

Jika Eskalasi Lock terjadi, memverifikasi bahwa kunci meluas tabel menghalangi pengguna lain.

Untuk informasi lebih lanjut tentang cara untuk mengidentifikasi Pemblokir kepala dan bagaimana untuk mengidentifikasi kunci sumber daya dipegang oleh kepala Pemblokir yang menghalangi lain server proses ID (SPIDs), klik nomor artikel di bawah ini untuk melihat artikel di dalam Basis Pengetahuan Microsoft:
224453Memahami dan memecahkan SQL Server 7.0 atau masalah pemblokiran 2000
Jika kunci yang menghalangi pengguna lain apa pun selain kunci TAB (tabel-tingkat) dengan mode kunci s (dibagi), atau X (eksklusif), eskalasi lock bukanlah masalah. Secara khusus, jika TAB mengunci adalah kunci maksud (seperti kunci mode IS, IU, atau IX), hal ini tidak hasil dari eskalasi lock. Jika masalah Anda memblokir yang tidak disebabkan oleh mengunci eskalasi, lihat artikel Q224453 untuk langkah pemecahan masalah.

Bagaimana mencegah Eskalasi Lock

Cara paling sederhana dan paling aman untuk mencegah eskalasi lock adalah untuk menjaga transaksi pendek dan untuk mengurangi jejak kunci permintaan mahal jadi bahwa eskalasi lock ambang batas tidak melampaui. Ada beberapa cara untuk memperoleh tujuan ini, banyak yang terdaftar:
  • Memecah batch besar operasi ke beberapa lebih kecil operasi. Misalnya, Anda menjalankan query berikut untuk menghapus beberapa seratus ribu catatan lama dari audit meja, dan kemudian Anda menemukan bahwa disebabkan eskalasi lock yang memblokir pengguna lain:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    Dengan menghapus catatan-catatan ini beberapa ratus sekaligus, Anda dapat secara dramatis mengurangi jumlah kunci yang mengumpulkan per transaksi dan mencegah kunci eskalasi. Misalnya:
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Mengurangi jejak kunci permintaan dengan membuat permintaan sebagai efisien mungkin. Scan besar atau sejumlah besar Bookmark pencarian mungkin meningkatkan kemungkinan eskalasi lock; Selain itu, meningkatkan kesempatan umumnya negatif mempengaruhi concurrency deadlock, dan kinerja. Setelah Anda menemukan query yang menyebabkan eskalasi lock, mencari peluang untuk membuat indeks baru atau untuk menambahkan kolom untuk indeks yang ada untuk menghapus indeks atau Tabel scan dan untuk memaksimalkan efisiensi indeks berusaha. Mempertimbangkan paste query ke jendela query Query Analyzer untuk melakukan analisis indeks otomatis di atasnya. Untuk melakukannya, pada Permintaan menu, klik Indeks Tuning Wizard dalam SQL Server 2000, atau klik Melakukan analisis indeks dalam SQL Server 7.0.

    Salah satu tujuan dari optimasi ini untuk membuat indeks berusaha kembali baris sesedikit mungkin untuk meminimalkan biaya Penanda pencarian (memaksimalkan selektivitas indeks untuk tertentu permintaan). Jika SQL Server memperkirakan bahwa operator logis Bookmark Lookup mungkin kembali banyak baris, dapat menggunakan PREFETCH untuk melakukan pencarian bookmark. Jika SQL Server menggunakan PREFETCH untuk lookup penunjuk, itu harus meningkatkan tingkat isolasi transaksi sebagian dari query untuk berulang membaca untuk bagian dari query. Ini berarti bahwa apa yang mungkin terlihat mirip dengan pilih pernyataan di tingkat isolasi berkomitmen baca mungkin memperoleh ribuan kunci kunci (di kedua berkerumun indeks dan indeks nonclustered satu), yang dapat menyebabkan seperti query untuk melebihi ambang batas eskalasi lock. Hal ini terutama penting jika Anda menemukan bahwa kunci meluas kunci bersama meja, yang, Namun, tidak sering terlihat di tingkat isolasi berkomitmen baca default. Jika Bookmark Lookup dengan PREFETCH klausul yang menyebabkan eskalasi, pertimbangkan menambahkan kolom tambahan untuk nonclustered indeks yang muncul dalam indeks Mencari atau indeks memindai operator logis di bawah Lookup Bookmark logis operator di rencana permintaan. Mungkin untuk membuat meliputi indeks ( indeks yang mencakup semua kolom dalam tabel yang digunakan dalam permintaan) atau di setidaknya indeks yang mencakup kolom yang digunakan untuk bergabung dengan kriteria atau di klausul WHERE jika termasuk segala sesuatu dalam daftar pilih kolom adalah tidak praktis.

    Join bersarang Loop juga dapat menggunakan PREFETCH, dan ini menyebabkan perilaku penguncian yang sama.

    Untuk informasi selengkapnya, klik nomor artikel berikut untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
    260652Join bersarang loop yang menggunakan "BOOKMARK LOOKUP...DENGAN PREFETCH"mungkin memegang kunci lagi
  • Eskalasi Lock tidak dapat terjadi jika SPID berbeda saat ini memegang kunci tidak sesuai tabel. Eskalasi Lock selalu meningkat Tabel kunci, dan tidak pernah untuk halaman kunci. Selain itu, jika eskalasi lock usaha gagal karena SPID lain memegang kunci TAB yang tidak kompatibel, query eskalasi percobaan yang tidak memblokir sementara menunggu kunci TAB. Sebaliknya, terus untuk mendapatkan kunci pada tingkat yang asli, lebih rinci (baris, kunci, atau halaman), secara berkala membuat tambahan eskalasi upaya. Oleh karena itu, salah satu metode untuk mencegah eskalasi lock di atas meja tertentu adalah untuk memperoleh dan memegang kunci pada hubungan yang berbeda yang tidak kompatibel dengan meluas kunci tipe. IX (maksud eksklusif) kunci di tingkat tabel tidak mengunci semua baris atau halaman, tetapi itu adalah masih tidak kompatibel dengan S (dibagi) dinaikkan atau x (eksklusif) TAB kunci. Sebagai contoh, asumsikan bahwa Anda harus menjalankan pekerjaan batch yang memodifikasi baris di dalam jumlah besar mytable tabel dan yang telah menyebabkan menghalangi yang terjadi karena kunci eskalasi. Jika pekerjaan ini selalu selesai dalam waktu kurang dari satu jam, Anda dapat membuat Transact-SQL pekerjaan yang berisi kode berikut, dan jadwal pekerjaan baru untuk mulai beberapa menit sebelum waktu mulai pekerjaan batch:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    Query ini mengakuisisi dan memegang kunci IX mytable selama satu jam, yang mencegah eskalasi lock di atas meja selama waktu itu. Batch ini tidak memodifikasi data atau memblokir permintaan lain (kecuali permintaan lainnya pasukan tabel kunci dengan tanda-tanda TABLOCK atau jika Administrator telah dinonaktifkan halaman atau baris kunci dengan menggunakan sp_indexoption disimpan prosedur).
Selain itu, Anda dapat menonaktifkan eskalasi lock dengan memungkinkan jejak Bendera 1211. Namun, bendera jejak ini menonaktifkan semua eskalasi lock global di contoh SQL Server. Eskalasi Lock melayani tujuan yang sangat berguna dalam SQL Server oleh memaksimalkan efisiensi pertanyaan yang jika tidak melambat oleh overhead memperoleh dan melepaskan beberapa ribuan kunci. Kunci eskalasi juga membantu untuk meminimalkan memori diperlukan untuk melacak kunci. Memori yang SQL Server dapat mengalokasikan secara dinamis untuk kunci struktur hingga, jadi jika Anda menonaktifkan eskalasi lock dan memori kunci tumbuh besar cukup, upaya untuk mengalokasikan tambahan kunci untuk permintaan apapun mungkin gagal dan setelah kesalahan terjadi:

Kesalahan: 1204, tingkat keparahan: 19 Negara: 1
SQL Server tidak bisa memperoleh sumber kunci saat ini. Jalankan kembali pernyataan Anda ketika ada lebih sedikit pengguna aktif atau meminta sistem administrator untuk memeriksa konfigurasi kunci dan memori SQL Server.
Catatan Ketika terjadi kesalahan "1204", berhenti pengolahan Laporan saat ini dan menyebabkan rollback transaksi aktif. Kembalikan itu sendiri dapat memblokir pengguna atau mengakibatkan waktu pemulihan database lama jika Anda me-restart layanan SQL Server.

Menggunakan petunjuk kunci seperti ROWLOCK hanya mengubah rencana awal kunci. Petunjuk kunci tidak mencegah eskalasi lock.

Metode lain untuk mencegah eskalasi lock yang dibahas sebelumnya dalam artikel ini adalah pilihan yang lebih baik daripada memungkinkan jejak bendera. Selain itu, metode lain umumnya menghasilkan lebih baik performa untuk permintaan daripada menonaktifkan eskalasi lock untuk seluruh contoh. Microsoft menganjurkan memungkinkan bendera jejak ini hanya untuk mengurangi berat menghalangi yang disebabkan oleh eskalasi lock sementara pilihan lain, seperti dibahas sebelumnya dalam artikel ini, sedang diselidiki. Untuk mengaktifkan jejak Bendera sehingga diaktifkan setiap kali SQL Server dimulai, menambahkannya sebagai server Startup parameter.

Untuk menambahkan server startup parameter, klik kanan server di SQL Enterprise Manager, klik Properti, dan kemudian pada General tab, klik Startup parameter, dan kemudian menambahkan parameter berikut (persis seperti yang ditunjukkan):
-T1211
Anda harus siklus layanan SQL Server untuk parameter startup baru untuk mengambil efek. Jika Anda menjalankan query berikut dalam Query Analyzer bendera jejak mulai berlaku segera:
DBCC TRACEON (1211, -1)				
Namun, jika Anda tidak menambahkan -T1211 Startup parameter, efek traceon perintah ini hilang ketika layanan SQL Server bersepeda. Menyalakan Bendera jejak mencegah setiap escalations kunci masa depan, tetapi tidak sebaliknya escalations kunci apapun yang telah terjadi di aktif transaksi.

Properti

ID Artikel: 323630 - Kajian Terakhir: 26 September 2011 - Revisi: 2.0
Berlaku bagi:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Kata kunci: 
kbsqlsetup kbinfo kbmt KB323630 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.
Klik disini untuk melihat versi Inggris dari artikel ini:323630

Berikan Masukan

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com