MySQL 5.1 mendukung penguncian tingkat tabel untuk tabel MyISAM dan MEMORY, penguncian tingkat halaman untuk tabel BDB, dan InnoDB tabel. Penguncian tingkat baris. Dalam banyak kasus, dimungkinkan untuk menebak berdasarkan pelatihan jenis kunci mana yang terbaik untuk suatu aplikasi, namun secara umum sulit untuk mengetahui apakah jenis kunci tertentu lebih baik daripada jenis kunci lainnya. Semuanya tergantung pada aplikasinya, bagian aplikasi yang berbeda mungkin memerlukan jenis kunci yang berbeda. Untuk menentukan apakah Anda ingin menggunakan mesin penyimpanan penguncian tingkat baris, Anda harus melihat apa yang dilakukan aplikasi Anda dan campuran pernyataan pilih dan perbarui yang digunakannya. Misalnya, sebagian besar aplikasi web melakukan banyak pilihan dan sedikit penghapusan, hanya memperbarui nilai kunci, dan hanya beberapa penyisipan tabel tertentu. Pengaturan dasar MySQL MyISAM telah disetel dengan baik.
Di MySQL, untuk mesin penyimpanan yang menggunakan penguncian tingkat tabel, tidak akan terjadi kebuntuan saat tabel dikunci. Hal ini dikelola dengan selalu meminta semua kunci yang diperlukan segera di awal kueri dan selalu mengunci tabel dalam urutan yang sama.
Untuk WRITE, metode penguncian tabel yang digunakan oleh MySQL berfungsi sebagai berikut:
◆ Jika tidak ada kunci pada tabel, letakkan kunci tulis di atasnya.
◆ Jika tidak, masukkan permintaan kunci ke dalam antrian kunci tulis.
Untuk READ, metode penguncian yang digunakan oleh MySQL bekerja sebagai berikut:
◆Jika tidak ada kunci tulis pada tabel, letakkan kunci baca di atasnya.
◆ Jika tidak, masukkan permintaan kunci ke dalam antrian kunci baca.
Saat kunci dilepaskan, kunci tersebut dapat diperoleh melalui thread di antrian kunci tulis, dan kemudian dengan thread di antrian kunci baca.
Artinya jika Anda memiliki banyak pembaruan pada sebuah tabel, pernyataan SELECT akan menunggu hingga tidak ada lagi pembaruan.
Jika pernyataan INSERT tidak bertentangan, Anda dapat dengan bebas menggabungkan pernyataan INSERT dan SELECT paralel untuk tabel MyISAM tanpa mengunci.
InnoDB menggunakan penguncian baris dan BDB menggunakan penguncian halaman. Dengan kedua mesin penyimpanan, kebuntuan mungkin terjadi. Hal ini karena InnoDB secara otomatis memperoleh kunci baris dan BDB memperoleh kunci halaman selama pemrosesan pernyataan SQL, bukan saat transaksi dimulai.
Keuntungan penguncian tingkat baris:
· Hanya ada sedikit konflik penguncian ketika baris berbeda diakses di banyak thread.
· Kembalikan hanya dengan sedikit perubahan.
· Satu baris dapat dikunci untuk waktu yang lama.
Kerugian dari penguncian tingkat baris:
· Memakan lebih banyak memori dibandingkan penguncian tingkat halaman atau tingkat tabel.
· Bila digunakan pada sebagian besar tabel, lebih lambat dibandingkan penguncian tingkat halaman atau tingkat tabel karena Anda harus memperoleh lebih banyak kunci.
· Jika Anda sering melakukan operasi GROUP BY pada sebagian besar data Anda atau harus sering memindai seluruh tabel, ini akan jauh lebih lambat dibandingkan kunci lainnya.
· Dengan penguncian tingkat tinggi, Anda juga dapat dengan mudah menskalakan aplikasi Anda dengan mendukung berbagai jenis penguncian karena biaya penguncian lebih murah dibandingkan penguncian tingkat baris.
Kunci tabel lebih diutamakan daripada kunci tingkat halaman atau baris ketika:
· Sebagian besar pernyataan pada tabel digunakan untuk membaca.
· Baca dan perbarui dengan kunci yang ketat, Anda dapat memperbaruiatau
menghapus baris yang dapat diekstraksi dengan satu kunci baca:
• UPDATE tbl_name SET kolom = nilai WHERE Unique_key_col = key_value;
• DELETE FROM tbl_name WHERE Unique_key_col = key_value;
pernyataan INSERT paralel dan sangat sedikit pernyataan UPDATE atau DELETE.
· Ada banyak pemindaian atau operasi GROUP BY di seluruh tabel tanpa operasi tulis apa pun.
Opsi yang berbeda dari penguncian tingkat baris atau tingkat halaman:
· Pembuatan versi (misalnya, teknik yang digunakan di MySQL untuk penyisipan paralel), di mana dapat terdapat satu operasi tulis dan banyak operasi baca pada saat yang bersamaan. Ini berarti database atau tabel mendukung tampilan data yang berbeda, bergantung pada kapan akses dimulai. Istilah umum lainnya adalah "pelacakan waktu", "copy on write" atau "copy on demand".
· Replikasi berdasarkan permintaan lebih diutamakan daripada penguncian tingkat halaman atau baris dalam banyak kasus. Namun, dalam kasus terburuk, ini mungkin menggunakan lebih banyak memori dibandingkan menggunakan penguncian biasa.
· Selain penguncian tingkat baris, Anda dapat menggunakan penguncian tingkat aplikasi, seperti GET_LOCK() dan RELEASE_LOCK() di MySQL. Ini adalah kunci penasehat, dan hanya akan berfungsi pada aplikasi yang berjalan dengan baik.
Untuk mencapai kecepatan penguncian maksimum, MySQL menggunakan penguncian tabel (bukan penguncian halaman, baris, atau kolom) untuk semua mesin penyimpanan kecuali InnoDB dan BDB. Untuk tabel InnoDB dan BDB, MySQL hanya menggunakan penguncian tabel jika Anda secara eksplisit mengunci tabel dengan LOCK TABLES; jika Anda tidak menggunakan LOCK TABLES, karena InnoDB menggunakan penguncian tingkat baris otomatis dan BDB menggunakan penguncian tingkat halaman untuk memastikan isolasi transaksi.
Namun untuk tabel besar, penguncian tabel lebih baik daripada penguncian baris untuk sebagian besar aplikasi, namun memiliki beberapa kelemahan. Penguncian tabel memungkinkan banyak thread untuk membaca dari sebuah tabel secara bersamaan, namun jika sebuah thread ingin menulis ke tabel, maka thread tersebut harus mendapatkan akses eksklusif terlebih dahulu. Selama pembaruan, semua thread lain yang ingin mengakses tabel harus menunggu hingga pembaruan selesai.
Pembaruan tabel umumnya dianggap lebih penting daripada pengambilan tabel, sehingga diberi prioritas lebih tinggi. Hal ini harus memastikan bahwa aktivitas memperbarui tabel tidak akan terhenti, bahkan jika ada aktivitas SELECT yang berat di tabel.
Penguncian tabel dapat menyebabkan masalah dalam situasi seperti ketika thread sedang menunggu karena hard disk penuh dan harus ada ruang kosong sebelum thread dapat diproses. Dalam hal ini, semua thread yang ingin mengakses tabel tersebut juga dimasukkan ke dalam status menunggu hingga tersedia lebih banyak ruang hard disk.
Penguncian tabel juga bermasalah dalam situasi berikut:
· Pelanggan mengeluarkan query yang sudah berjalan lama.
· Kemudian, klien lain memperbarui tabel yang sama. Klien harus menunggu sampai SELECT selesai.
· Klien lain mengeluarkan pernyataan SELECT lain pada tabel yang sama. Karena UPDATE mempunyai prioritas lebih tinggi daripada SELECT, pernyataan SELECT menunggu UPDATE selesai, dan menunggu SELECT pertama selesai.
Beberapa metode dijelaskan di bawah ini untuk menghindari atau mengurangi pertikaian yang disebabkan oleh kunci tabel:
· Cobalah untuk membuat pernyataan SELECT berjalan lebih cepat. Anda mungkin harus membuat beberapa tabel ringkasan untuk melakukan ini.
· Mulai mysqld dengan --low-priority-updates. Ini akan memberikan semua pernyataan yang memperbarui (memodifikasi) tabel dengan prioritas lebih rendah daripada pernyataan SELECT. Dalam hal ini, pernyataan SELECT kedua pada situasi sebelumnya akan dieksekusi sebelum pernyataan UPDATE tanpa menunggu SELECT pertama selesai.
· Anda dapat menggunakan pernyataan SET LOW_PRIORITY_UPDATES=1 untuk menentukan bahwa semua pembaruan dalam koneksi tertentu harus menggunakan prioritas rendah.
· Anda dapat menggunakan atribut LOW_PRIORITY untuk memberikan prioritas lebih rendah pada pernyataan INSERT, UPDATE, atau DELETE tertentu.
· Anda dapat menggunakan atribut HIGH_PRIORITY untuk memberikan prioritas lebih tinggi pada pernyataan SELECT tertentu.
· Mulai mysqld dengan menentukan nilai rendah untuk variabel sistem max_write_lock_count untuk memaksa MySQL untuk sementara meningkatkan prioritas semua pernyataan SELECT yang menunggu tabel setelah sejumlah penyisipan tertentu selesai. Hal ini memungkinkan kunci BACA diberikan setelah sejumlah kunci TULIS tertentu.
· Jika Anda memiliki masalah dengan INSERT yang digabungkan dengan SELECT, beralihlah menggunakan tabel MyISAM baru karena tabel tersebut mendukung SELECT dan INSERT secara bersamaan.
· Jika Anda mencampur sisipan dan penghapusan pada tabel yang sama, INSERT DELAYED akan sangat membantu.
· Jika Anda mempunyai masalah dalam menggabungkan pernyataan SELECT dan DELETE pada tabel yang sama, opsi LIMIT DELETE dapat membantu.
· Menggunakan SQL_BUFFER_RESULT untuk pernyataan SELECT dapat membantu mempersingkat waktu penguncian tabel.
· Kode kunci di mysys/thr_lock.c dapat diubah menggunakan antrian tunggal. Dalam hal ini, kunci tulis dan kunci baca akan memiliki prioritas yang sama, yang dapat berguna untuk beberapa aplikasi.
Berikut beberapa tips terkait penguncian tabel di MySQL:
· Jika Anda tidak mencampur pembaruan dengan pilihan yang memerlukan pengecekan banyak baris dalam tabel yang sama, Anda dapat melakukan operasi paralel.
· Anda dapat menggunakan LOCK TABLES untuk meningkatkan kecepatan, karena banyak pembaruan dalam satu kunci jauh lebih cepat daripada pembaruan tanpa kunci. Memisahkan isi tabel menjadi beberapa tabel juga bisa membantu.
· Jika Anda mengalami masalah kecepatan saat mengunci tabel di MySQL, Anda dapat mengonversi tabel tersebut menjadi tabel InnoDB atau BDB untuk meningkatkan kinerja.