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

Cara menentukan apakah Eskalasi Lock menyebabkan menghalangi

Kembali ke atas

Bagaimana mencegah Eskalasi Lock

• 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:
  260652  (http://support.microsoft.com/kb/260652/ ) Join 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).

DBCC TRACEON (1211, -1)				

Kembali ke atas