|
|
Diposting pada 20/07/2016 12.37.53
|
|
|
Ikhtisar kunci
1. Mengapa memperkenalkan kunci
Ketika beberapa pengguna melakukan operasi bersamaan pada database secara bersamaan, inkonsistensi data berikut terjadi:
Pembaruan yang hilang
Dua pengguna, A dan B, membaca data yang sama dan memodifikasinya, dan hasil modifikasi satu pengguna menghancurkan hasil modifikasi lainnya, seperti sistem pemesanan tiket
Bacaan kotor
Pengguna A memodifikasi data, lalu pengguna B membacakan data, tetapi pengguna A membatalkan modifikasi data karena alasan tertentu, dan data kembali ke nilai aslinya
Jangan membaca berulang kali
Pengguna A membaca data, lalu pengguna B membaca data dan memodifikasinya
Metode utama kontrol konkurensi adalah pemblokiran, yaitu melarang pengguna melakukan operasi tertentu untuk jangka waktu tertentu untuk menghindari inkonsistensi data
2. Klasifikasi kunci
Ada dua divisi ke dalam kategori kunci:
1 . Dari perspektif sistem basis data: dibagi menjadi kunci eksklusif (yaitu, kunci eksklusif), kunci bersama, dan kunci pembaruan
MS - SQL Server menggunakan pola kunci sumber daya berikut.
Deskripsi Mode Kunci
Berbagi digunakan untuk operasi yang tidak mengubah atau memperbarui data (operasi baca-saja), seperti pernyataan SELECT.
Update (U) digunakan dalam resource yang dapat diperbarui. Mencegah bentuk kebuntuan umum saat beberapa sesi dibaca, dikunci, dan mungkin pembaruan sumber daya yang mungkin terjadi.
Eksklusif (X) digunakan untuk operasi modifikasi data, seperti INSERT, UPDATE, atau DELETE. Pastikan bahwa beberapa pembaruan tidak dilakukan pada sumber daya yang sama secara bersamaan.
Kunci Intent digunakan untuk menetapkan hierarki kunci. Jenis kunci intent adalah: Intent Shared (IS), Intent Exclusive (IX), dan Intent Exclusive (SIX).
Kunci skema digunakan saat melakukan operasi yang bergantung pada skema tabel. Jenis kunci skema adalah: modifikasi skema (Sch -M) dan stabilitas skema (Sch -S).
Pembaruan massal (BU) digunakan ketika volume besar data disalin ke tabel dan petunjuk TABLOCK ditentukan.
Kunci bersama
Kunci bersama memungkinkan transaksi bersamaan membaca (PILIH) sumber daya. Ketika kunci bersama (S) ada pada sumber daya, tidak ada transaksi lain yang dapat memodifikasi data. Lepaskan kunci bersama (S) pada sumber daya segera setelah data dibaca, kecuali tingkat isolasi transaksi diatur ke dapat diulang atau lebih tinggi, atau kunci bersama (S) dipertahankan dengan petunjuk kunci selama masa pakai transaksi.
Kunci perbarui
Kunci pembaruan (U) mencegah kebuntuan dalam bentuk biasanya. Pola pembaruan tipikal terdiri dari transaksi yang membaca rekaman, mendapatkan kunci bersama (S) untuk sumber daya (halaman atau baris), lalu memodifikasi baris, yang mengharuskan kunci dikonversi menjadi kunci eksklusif (X). Jika dua transaksi memperoleh kunci mode bersama pada sumber daya dan kemudian mencoba memperbarui data secara bersamaan, satu transaksi mencoba mengonversi kunci menjadi kunci eksklusif (X). Transisi dari mode bersama ke kunci eksklusif harus menunggu beberapa saat karena kunci eksklusif satu transaksi tidak kompatibel dengan kunci mode bersama transaksi lain; Menunggu kunci terjadi. Transaksi kedua mencoba mendapatkan kunci eksklusif (X) untuk pembaruan. Kebuntuan terjadi karena kedua transaksi dikonversi menjadi kunci eksklusif (X), dan setiap transaksi menunggu transaksi lain melepaskan kunci mode bersama.
Untuk menghindari potensi masalah kebuntuan ini, gunakan kunci (U) yang diperbarui. Hanya satu transaksi dalam satu waktu yang bisa mendapatkan kunci (U) yang diperbarui untuk sumber daya. Jika transaksi memodifikasi sumber daya, kunci pembaruan (U) dikonversi menjadi kunci eksklusif (X). Jika tidak, kunci dikonversi menjadi kunci bersama.
Kunci eksklusif
Kunci eksklusif (X) mencegah transaksi bersamaan mengakses sumber daya. Transaksi lain tidak dapat membaca atau memodifikasi data yang dikunci oleh kunci eksklusif (X).
Kunci niat
Kunci intent menunjukkan bahwa SQL Server perlu memperoleh kunci bersama (S) atau kunci eksklusif (X) pada beberapa sumber daya yang mendasarinya dalam hierarki. Misalnya, kunci intent berbagi yang ditempatkan di tingkat tabel menunjukkan bahwa transaksi bermaksud untuk menempatkan kunci berbagi pada halaman atau baris dalam tabel. Mengatur kunci intent di tingkat tabel mencegah transaksi lain memperoleh kunci eksklusif (X) pada tabel yang berisi halaman tersebut. Kunci maksud dapat meningkatkan performa karena SQL Server hanya memeriksa kunci maksud di tingkat tabel untuk menentukan apakah transaksi dapat memperoleh kunci dengan aman pada tabel tersebut. Alih-alih memeriksa kunci pada setiap baris atau halaman dalam tabel untuk menentukan apakah transaksi dapat mengunci seluruh tabel.
Kunci intent mencakup Intent Sharing (IS), Intent Exclusive (IX), dan Intent Exclusive Sharing (SIX).
Deskripsi Mode Kunci
Berbagi Intent (IS) menunjukkan bahwa maksud transaksi adalah beberapa, bukan semua, sumber daya yang mendasarinya dalam hierarki baca dengan menempatkan kunci S pada setiap sumber daya.
Intent Exclusive (IX) menunjukkan bahwa maksud transaksi adalah untuk memodifikasi beberapa, tetapi tidak semua, sumber daya yang mendasarinya dalam hierarki dengan menempatkan X-lock pada setiap sumber daya. IX adalah superset dari IS.
Berbagi eksklusif dengan maksud (SIX) menunjukkan bahwa maksud transaksi adalah untuk membaca semua sumber daya yang mendasarinya dalam hierarki dan memodifikasi beberapa, tetapi tidak semua, sumber daya yang mendasarinya dengan menempatkan kunci IX pada setiap sumber daya. Izinkan kunci IS bersamaan pada sumber daya tingkat atas. Misalnya, kunci SIX tabel menempatkan kunci SIX pada tabel (memungkinkan kunci IS bersamaan) dan kunci IX pada halaman yang saat ini dimodifikasi (kunci X pada baris yang dimodifikasi). Meskipun setiap sumber daya hanya dapat memiliki satu kunci SIX untuk jangka waktu tertentu untuk mencegah transaksi lain memperbarui sumber daya, transaksi lain dapat membaca sumber daya yang mendasarinya dalam hierarki dengan memperoleh kunci IS tingkat tabel.
Kunci eksklusif: Hanya program yang melakukan operasi penguncian yang diizinkan untuk menggunakannya, dan operasi lain di atasnya tidak akan diterima. Saat Anda menjalankan perintah pembaruan data, SQL Server secara otomatis menggunakan kunci eksklusif. Ketika kunci lain ada pada suatu objek, Anda tidak dapat menambahkan kunci eksklusif ke dalamnya.
Kunci bersama: Sumber daya yang dikunci oleh kunci bersama dapat dibaca oleh pengguna lain, tetapi pengguna lain tidak dapat memodifikasinya.
Kunci perbarui: Saat SQL Server siap untuk memperbarui data, pertama-tama SQL Server mengunci objek data sehingga data tidak dapat dimodifikasi tetapi dapat dibaca. Ketika SQL Server menentukan bahwa ia ingin memperbarui data, SQL Server akan secara otomatis mengganti kunci pembaruan dengan kunci eksklusif, dan tidak dapat menambahkan kunci pembaruan ke dalamnya ketika kunci lain ada pada objek.
2 . Dari sudut pandang programmer: dibagi menjadi kunci optimis dan kunci pesimis.
Kunci Optimisme: Mengandalkan sepenuhnya database untuk mengelola pekerjaan kunci.
Kunci pesimis: Pemrogram mengelola penanganan kunci pada data atau objek itu sendiri.
MS - SQLSERVER menggunakan kunci untuk menerapkan kontrol konkurensi pesimis antara beberapa pengguna yang melakukan modifikasi dalam database secara bersamaan
3. Ukuran partikel kunci
Granularitas kunci adalah ukuran target yang diblokir, granularitas pemblokiran kecil adalah konkurensi tinggi, tetapi overheadnya besar, dan granularitas pemblokiran besar adalah konkurensi rendah tetapi overheadnya kecil
SQL Server mendukung granularitas kunci untuk baris, halaman, kunci, rentang kunci, indeks, tabel, atau database
Deskripsi Sumber Daya
Pengidentifikasi baris RID. Digunakan untuk mengunci baris dalam tabel satu per satu.
Kunci baris kunci di indeks. Digunakan untuk melindungi rentang kunci dalam transaksi yang dapat diserialisasikan.
8 kilobyte (KB) halaman data atau halaman indeks.
Extended Disk Satu set delapan halaman data atau halaman indeks yang bersebelahan.
Tabel Seluruh tabel termasuk semua data dan indeks.
database DB.
4. Lamanya waktu penguncian
Lamanya waktu kunci dipegang adalah lamanya waktu yang diperlukan untuk melindungi sumber daya pada tingkat yang diminta.
Waktu penangguhan kunci bersama yang digunakan untuk melindungi operasi baca bergantung pada tingkat isolasi transaksi. Dengan tingkat isolasi transaksi default READ COMMITTED, kunci bersama hanya dikontrol selama durasi halaman baca. Dalam pemindaian, kunci tidak dilepaskan sampai kunci diperoleh di halaman berikutnya dalam pemindaian. Jika Anda menentukan prompt HOLDLOCK atau mengatur tingkat isolasi transaksi ke REPEATABLE READ atau SERIALIZABLE, kunci tidak dilepaskan hingga transaksi berakhir.
Bergantung pada opsi konkurensi yang ditetapkan untuk kursor, kursor dapat memperoleh kunci gulir dalam mode bersama untuk melindungi ekstrak. Saat kunci gulir diperlukan, kunci gulir tidak dilepaskan hingga kursor diekstraksi atau ditutup berikutnya, mana yang terjadi lebih dulu. Namun, jika Anda menentukan HOLDLOCK, kunci gulir tidak dilepaskan hingga akhir transaksi.
Kunci eksklusif yang digunakan untuk melindungi pembaruan tidak akan dirilis hingga akhir transaksi.
Jika koneksi mencoba memperoleh kunci yang bertentangan dengan kunci yang dikendalikan oleh koneksi lain, koneksi yang mencoba memperoleh kunci akan diblokir hingga:
Kunci yang bertentangan dilepaskan dan koneksi memperoleh kunci yang diminta.
Batas waktu koneksi telah kedaluwarsa. Tidak ada interval batas waktu secara default, tetapi beberapa app menetapkan interval batas waktu untuk mencegah penantian tanpa batas waktu
Lima penyesuaian kunci di SQL Server
1 Tangani kebuntuan dan tetapkan prioritas kebuntuan
Kebuntuan adalah penantian tanpa akhir yang disebabkan oleh banyak pengguna yang mengajukan pemblokiran yang berbeda, karena pemohon memiliki bagian dari hak pemblokiran dan menunggu pemblokiran parsial yang dimiliki oleh pengguna lain
Anda dapat menggunakan DEADLOCK_PRIORITY SET untuk mengontrol bagaimana sesi bereaksi jika terjadi kondisi kebuntuan. Jika kedua proses mengunci data, dan setiap proses tidak dapat melepaskan kuncinya sendiri sampai proses lain melepaskan kuncinya sendiri, situasi kebuntuan terjadi.
2 Tangani batas waktu dan atur durasi batas penguncian.
@@LOCK_TIMEOUT Mengembalikan pengaturan batas waktu penguncian saat ini untuk sesi saat ini dalam milidetik
Pengaturan LOCK_TIMEOUT SET memungkinkan aplikasi untuk mengatur jumlah waktu maksimum yang menunggu pernyataan untuk memblokir sumber daya. Ketika waktu tunggu pernyataan lebih besar dari pengaturan LOCK_TIMEOUT, sistem secara otomatis membatalkan pernyataan pemblokiran dan mengembalikan aplikasi pesan kesalahan 1222 bahwa periode batas waktu permintaan kunci telah terlampaui
contoh
Dalam contoh berikut, periode batas waktu penguncian diatur ke 1.800 milidetik.
SET LOCK_TIMEOUT1800
3) Atur tingkat isolasi transaksi.
4) Gunakan petunjuk kunci tingkat tabel untuk pernyataan SELECT, INSERT, UPDATE, dan DELETE.
5) Konfigurasikan granularitas penguncian indeks
Anda dapat menggunakan prosedur tersimpan sistem sp_indexoption untuk mengatur granularitas kunci untuk pengindeksan
6. Lihat informasi kunci
1 Lakukan EXEC SP_LOCK melaporkan informasi tentang kunci
2 Tekan Ctrl + 2 di penganalisis kueri untuk melihat informasi kunci
7. Tindakan pencegahan untuk digunakan
Cara menghindari kebuntuan
1. Saat menggunakan transaksi, cobalah untuk mempersingkat proses pemrosesan transaksi yang logis, dan kirimkan atau balikkan transaksi lebih awal.
2 Atur parameter batas waktu kebuntuan ke kisaran yang wajar, seperti: 3 menit - 10 menit; Setelah waktu, operasi akan secara otomatis ditinggalkan untuk menghindari proses menggantung;
3. Optimalkan program, periksa dan hindari fenomena kebuntuan;
4. Uji semua skrip dan SP dengan hati-hati sebelum versi yang tepat.
5 Semua SP harus memiliki penanganan kesalahan (melalui @error)
6 Jangan mengubah tingkat default transaksi SQL SERVER. Penguncian paksa tidak disarankan
Memecahkan masalah Cara mengunci database tabel baris
8. Beberapa pertanyaan tentang kunci
1 Cara mengunci barisan meja
MENGATUR TINGKAT TRANSACTIONISOLATION READUNCOMMITTED
PILIH *DARI tabel ROWLOCKWHERE id = 1
2 Mengunci tabel dalam database
PILIH *DARI tabel WITH( HOLDLOCK)
Pernyataan kunci:
sybase:
memperbarui set tabel col1 = col1 dimana1= 0 ;
MSSQL:
pilih col1dari tabel (tablockx)di mana 1= 0 ;
oracle:
KUNCI MEJA MEJA DALAM MODE EKSKLUSIF;
Setelah kunci terkunci, tidak ada orang lain yang dapat mengoperasikannya sampai pengguna yang terkunci membukanya, dan dibuka kuncinya dengan commit atau rollback
Beberapa contoh membantu Anda memperdalam kesan Anda
Atur tabel1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Kunci eksklusif
Membuat dua koneksi baru
Jalankan pernyataan berikut dalam koneksi pertama
mulai tran
memperbarui tabel1
set A= ' aa '
di mana B= ' b2 '
tunggu penundaan' 00:00:30' --tunggu 30 detik
Melakukan trans
Jalankan pernyataan berikut dalam koneksi kedua
mulai tran
Pilih *dari Tabel1
di mana B= ' b2 '
Melakukan trans
Jika dua pernyataan di atas dijalankan secara bersamaan, kueri pilih harus menunggu pembaruan dieksekusi, yaitu menunggu 30 detik
2) Kunci bersama
Jalankan pernyataan berikut dalam koneksi pertama
mulai tran
select *from table1 holdlock - Holdlock ditambahkan ke kunci secara artifisial
di mana B= ' b2 '
tunggu penundaan' 00:00:30' --tunggu 30 detik
Melakukan trans
Jalankan pernyataan berikut dalam koneksi kedua
mulai tran
pilih A,C dari tabel1
di mana B= ' b2 '
memperbarui tabel1
set A= ' aa '
di mana B= ' b2 '
Melakukan trans
Jika dua pernyataan di atas dijalankan secara bersamaan, kueri pilih dalam koneksi kedua dapat dieksekusi
Pembaruan harus menunggu transaksi pertama melepaskan kunci bersama dan mengubahnya menjadi kunci eksklusif sebelum dapat dieksekusi, yaitu menunggu 30 detik
3) Kebuntuan
Menambahkan tabel2(D,E)
D E
d1 e1
d2 e2
Jalankan pernyataan berikut dalam koneksi pertama
mulai tran
memperbarui tabel1
set A= ' aa '
di mana B= ' b2 '
tunggu penundaan' 00:00:30'
memperbarui tabel2
atur D= ' d5 '
di mana E= ' e1 '
Melakukan trans
Jalankan pernyataan berikut dalam koneksi kedua
mulai tran
memperbarui tabel2
atur D= ' d5 '
di mana E= ' e1 '
tunggu penundaan' 00:00:10'
memperbarui tabel1
set A= ' aa '
di mana B= ' b2 '
Melakukan trans
Pada saat yang sama, sistem mendeteksi kebuntuan dan membatalkan proses
Untuk menambahkan:
Petunjuk penguncian tingkat tabel yang didukung oleh SQL Server 2000
HOLDLOCK menahan kunci bersama hingga seluruh transaksi selesai dan harus dilepaskan segera setelah objek terkunci tidak diperlukan, sama dengan tingkat isolasi transaksi SERIALIZABLE
Pernyataan NOLOCK dijalankan tanpa mengeluarkan kunci bersama, memungkinkan pembacaan kotor, yang sama dengan tingkat isolasi transaksi READ UNCOMMITTED
PAGLOCK menggunakan beberapa kunci halaman di mana satu kunci tabel digunakan
READPAST memungkinkan server sql melewati baris terkunci dan mengeksekusi transaksi, dan untuk tingkat isolasi transaksi READ UNCOMMITTED hanya melewati kunci RID, bukan kunci halaman, zona, dan tabel
ROWLOCK memberlakukan penggunaan rowlocks
TABLOCKX memberlakukan penggunaan kunci tingkat tabel eksklusif, yang mencegah transaksi lain menggunakan tabel selama transaksi
UPLOCK memaksa penggunaan pembaruan saat membaca tabel tanpa kunci bersama
Kunci Aplikasi:
Kunci aplikasi adalah kunci yang dihasilkan oleh kode klien, bukan kunci yang dihasilkan oleh SQL Server itu sendiri
Dua proses untuk menangani kunci aplikasi
sp_getapplock Mengunci sumber daya aplikasi
sp_releaseapplock Buka kunci sumber daya aplikasi
Catatan: Perbedaan antara mengunci tabel dalam database
PILIH *DARI tabel WITH( HOLDLOCK ) Transaksi lain dapat membaca tabel, tetapi tidak dapat memperbarui dan menghapus
PILIH *DARI tabel WITH(TABLOCKX) Transaksi lain tidak dapat membaca, memperbarui, dan menghapus tabel
|
Mantan:Tidak ada titik akhir yang mendengarkan di http://localhost:111/xxx.svc bahwa c...Depan:Kunci SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
