Ringkasan

Eskalasi Lock adalah proses konversi banyak detail kunci (seperti kunci baris atau halaman) ke tabel kunci. Microsoft SQL Server secara dinamis menentukan ketika melakukan eskalasi lock. Saat membuat keputusan ini, SQL Server memperhitungkan jumlah kunci yang disimpan di pemindaian tertentu, jumlah kunci yang dimiliki oleh seluruh transaksi dan memori yang digunakan untuk kunci di sistem secara keseluruhan. Biasanya, perilaku default SQL Server hasil eskalasi lock terjadi hanya pada titik tersebut mana akan meningkatkan kinerja atau saat Anda harus mengurangi memori sistem berlebihan kunci ke tingkat yang lebih masuk akal. Namun, beberapa aplikasi atau pertanyaan desain akan memicu eskalasi lock pada saat tidak diinginkan, dan telah ditingkatkan Tabel kunci dapat memblokir pengguna lain. Artikel ini membahas cara menentukan apakah eskalasi kunci yang menyebabkan memblokir dan cara menangani eskalasi lock yang tidak diinginkan.

Informasi lebih lanjut

Cara menentukan apakah eskalasi kunci yang menyebabkan memblokir

Eskalasi Lock tidak menimbulkan masalah pemblokiran sebagian besar. Untuk menentukan apakah kunci eskalasi terjadi saat ketika Anda mengalami masalah pemblokiran, mulai jejak SQL Profiler yang mencakup peristiwa Kunci: eskalasi . Jika Anda tidak melihat semua kejadian Eskalasi: Lock , eskalasi lock tidak terjadi pada server Anda dan informasi di dalam artikel ini tidak berlaku untuk situasi Anda.

Jika kunci eskalasi terjadi, verifikasi bahwa kunci telah ditingkatkan tabel memblokir pengguna lain.

Untuk informasi selengkapnya tentang cara mengidentifikasi Pemblokir kepala dan untuk mengidentifikasi sumber kunci dilaksanakan Pemblokir kepala yang memblokir lainnya server proses ID (SPIDs), klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:

224453 memahami dan memecahkan masalah pemblokiran 2000 atau SQL Server 7.0

Jika kunci yang menghalangi pengguna lain apa pun selain kunci (tabel tingkat) TAB dengan mode kunci s (berbagi), atau X (eksklusif), kunci eskalasi bukan masalah. Khususnya, jika kunci TAB kunci maksud (seperti kunci mode IS, IU atau IX), ini bukanlah hasil eskalasi lock. Jika masalah pemblokiran Anda tidak disebabkan oleh eskalasi lock, lihat artikel Q224453 untuk langkah pemecahan masalah.

Cara mencegah eskalasi Lock

Cara termudah dan paling aman untuk mencegah eskalasi lock adalah untuk menjaga transaksi singkat dan untuk mengurangi jejak kunci permintaan murah sehingga ambang eskalasi lock tidak melebihi. Ada beberapa cara untuk mendapatkan tujuan ini, banyak yang terdaftar:

  • Memecah operasi batch besar menjadi beberapa operasi yang lebih kecil. Misalnya, Anda menjalankan kueri berikut ini untuk menghapus beberapa ratusan ribu catatan lama dari tabel audit, dan kemudian Anda menemukan bahwa hal ini menyebabkan eskalasi kunci yang diblokir pengguna lain:

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

    Dengan menghapus catatan ini beberapa seratus per satu, Anda dapat secara drastis mengurangi jumlah kunci yang mengumpulkan per transaksi dan mencegah eskalasi lock. Misalnya:

    SET ROWCOUNT 500delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Mengurangi permintaan kunci jejak membuat permintaan yang efisien. Pemindaian besar atau penanda pencarian dalam jumlah besar dapat meningkatkan kemungkinan eskalasi lock; Selain itu, meningkatkan kemungkinan kemogokan, dan umumnya sangat mempengaruhi concurrency dan kinerja. Setelah Anda menemukan permintaan yang menyebabkan eskalasi lock, berusaha mencari peluang untuk membuat indeks baru atau untuk menambahkan kolom yang sudah ada indeks untuk menghapus indeks atau tabel scan dan untuk memaksimalkan efisiensi indeks. Pertimbangkan menempel permintaan ke jendela pencarian Query Analyzer untuk melakukan analisis indeks otomatis di dalamnya. Untuk melakukannya, pada menu permintaan , klik Indeks Tuning Wizard di SQL Server 2000, atau klik Melakukan analisis indeks di SQL Server 7.0.

    Salah satu tujuan optimasi ini adalah untuk membuat indeks berusaha kembali baris sesedikit mungkin untuk meminimalkan biaya penanda pencarian (memaksimalkan selektivitas indeks untuk permintaan tertentu). Jika SQL Server memperkirakan operator logis penanda pencarian mungkin kembali banyak baris, dapat menggunakan PREFETCH untuk melakukan pencarian penunjuk. Jika SQL Server menggunakan PREFETCH untuk pencarian bookmark, harus meningkatkan transaksi isolasi tingkat porsi permintaan berulang membaca untuk porsi permintaan. Ini berarti bahwa apa yang mungkin terlihat sama dengan pernyataan SELECT pada tingkat isolasi berkomitmen Baca dapat memperoleh ribuan kunci (di kluster indeks maupun indeks nonclustered satu), yang dapat menyebabkan permintaan melampaui ambang eskalasi lock. Hal ini sangat penting jika Anda menemukan bahwa kunci telah ditingkatkan kunci bersama tabel, yang, namun tidak sering dilihat di tingkat isolasi berkomitmen Baca default. Jika klausa penanda pencarian dengan PREFETCH menyebabkan eskalasi, pertimbangkan untuk menambahkan kolom tambahan untuk indeks nonclustered yang muncul dalam indeks mencari atau operator logis indeks memindai di bawah operator logis penanda pencarian dalam skema pertanyaan. Dimungkinkan untuk membuat meliputi index (indeks yang mencakup semua kolom dalam tabel yang digunakan dalam permintaan), atau setidaknya indeks yang mencakup kolom yang digunakan untuk bergabung dengan kriteria atau klausa di mana jika termasuk semua dalam daftar pilih kolom tidak praktis.

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

    Untuk informasi selengkapnya, klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:

    260652 Nested loop gabungan yang menggunakan "penanda pencarian... DENGAN PREFETCH"mungkin tahan lama kunci

  • Eskalasi Lock tidak dapat terjadi jika SPID berbeda saat ini memegang kunci tabel yang kompatibel. Eskalasi Lock selalu semakin meningkat untuk kunci tabel, dan tidak pernah kunci halaman. Selain itu, apabila upaya eskalasi lock gagal karena lain SPID memegang kunci TAB kompatibel, permintaan yang dicoba eskalasi tidak diblokir sementara menunggu kunci TAB. Namun, ini akan tetap 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 pada tabel tertentu adalah untuk memperoleh dan tahan kunci pada koneksi yang berbeda yang tidak kompatibel dengan jenis kunci yang telah ditingkatkan. Kunci IX (tujuan eksklusif) di tingkat tabel tidak mengunci baris atau halaman, tetapi masih tidak kompatibel dengan S telah ditingkatkan (berbagi) atau X (eksklusif) kunci TAB. Sebagai contoh, misalnya, Anda harus menjalankan pekerjaan batch yang mengubah jumlah baris pada tabel mytable dan yang telah menyebabkan memblokir yang terjadi karena kunci eskalasi. Jika pekerjaan ini selalu selesai kurang dari satu jam, Anda dapat membuat pekerjaan Transact-SQL yang berisi kode berikut, dan jadwal tugas baru untuk memulai beberapa menit sebelum waktu mulai pekerjaan batch:

    BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN

    Permintaan ini diakuisisi dan menyimpan kunci IX pada mytable untuk satu jam, yang mencegah eskalasi lock pada tabel selama waktu tersebut. Batch ini tidak mengubah data atau memblokir pertanyaan lain (kecuali permintaan lain memaksa Tabel kunci dengan petunjuk TABLOCK atau jika administrator telah dinonaktifkan kunci halaman atau baris dengan menggunakan sp_indexoption prosedur tersimpan).

Selain itu, Anda dapat menonaktifkan eskalasi lock dengan mengaktifkan bendera pelacakan 1211. Namun, bendera pelacakan ini menonaktifkan semua kunci eskalasi global dalam contoh SQL Server. Eskalasi Lock melayani tujuan yang sangat berguna di SQL Server dengan memaksimalkan efisiensi permintaan yang tidak melambat dengan beban memperoleh dan melepaskan beberapa ribuan kunci. Eskalasi Lock juga membantu untuk meminimalkan memori yang diperlukan untuk melacak kunci. Memori SQL Server dapat mengalokasikan secara dinamis untuk kunci struktur terbatas, sehingga apabila Anda menonaktifkan eskalasi lock dan memori kunci tumbuh cukup besar, usaha untuk mengalokasikan kunci tambahan untuk semua permintaan mungkin gagal dan terjadi galat berikut ini:


Galat: 1204, tingkat keparahan: 19, status: 1
SQL Server tidak bisa mendapatkan kunci sumber daya saat ini. Jalankan kembali pernyataan Anda bila ada lebih sedikit pengguna aktif atau meminta administrator sistem untuk memeriksa konfigurasi kunci dan memori SQL Server.

Catatan Ketika terjadi kesalahan "1204", menghentikan pemrosesan pernyataan saat ini dan menyebabkan rollback transaksi aktif. Rollback itu sendiri dapat memblokir pengguna atau menyebabkan waktu pemulihan database lama jika Anda me-restart layanan SQL Server.

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



Metode lainnya untuk mencegah eskalasi lock yang dibahas sebelumnya dalam artikel ini adalah pilihan yang lebih baik dari mengaktifkan bendera pelacakan. Selain itu, metode lainnya umumnya menghasilkan kinerja yang lebih baik untuk permintaan dari menonaktifkan eskalasi lock untuk contoh keseluruhan. Microsoft menganjurkan mengaktifkan bendera pelacakan ini hanya untuk mengurangi memblokir parah yang disebabkan oleh eskalasi lock saat opsi lainnya, seperti yang dibahas sebelumnya di artikel ini sedang diselidiki. Untuk mengaktifkan bendera pelacakan sehingga diaktifkan pada setiap SQL Server dimulai, menambahkan parameter permulaan server.

Untuk menambahkan parameter permulaan server, klik kanan server di SQL Enterprise Manager, klik properti, dan kemudian pada tab umum , klik Mulai parameter, dan kemudian tambahkan parameter berikut ini (persis seperti yang ditunjukkan):

-T1211Anda harus siklus layanan SQL Server untuk parameter permulaan baru diterapkan. Jika Anda menjalankan kueri berikut ini dalam Query Analyzer bendera pelacakan akan diterapkan segera:

DBCC TRACEON (1211, -1)

Namun, jika Anda tidak menambahkan -T1211 parameter permulaan, efek traceon perintah hilang saat dihantarkan layanan SQL Server. Mengaktifkan bendera pelacakan mencegah escalations depan kunci apa pun, tetapi tidak membalikkan escalations kunci apa pun yang telah terjadi dalam transaksi yang aktif.

Perlu bantuan lainnya?

Kembangkan keterampilan Anda

JELAJAHI PELATIHAN >

Dapatkan fitur baru terlebih dahulu

GABUNG MICROSOFT INSIDER >

Apakah informasi ini bermanfaat?

Seberapa puaskah Anda dengan kualitas bahasanya?
Apa yang memengaruhi pengalaman Anda?

Terima kasih atas umpan balik Anda!

×