Practical MySQL & MariaDB

Penjelasan Penguncian MySQL: Periksa Status Kunci, Lepaskan Kunci dengan Aman, dan Cegah Deadlock (5.6–8.0)

Konfigurasi & Administrasi Server
目次

1. Pendahuluan

MySQL banyak digunakan sebagai sistem manajemen basis data, tetapi ketika beberapa kueri berusaha mengakses data yang sama, mekanisme penguncian akan dipicu. Kunci penting untuk menjaga konsistensi data; namun, pengelolaan yang tidak tepat dapat menyebabkan deadlock dan penurunan kinerja.

Dalam artikel ini, kami akan menjelaskan konsep dasar penguncian di MySQL dan memberikan panduan terperinci tentang cara memeriksa status kunci, cara melepaskan kunci, dan cara mencegah deadlock.

Apa yang Akan Anda Pelajari dalam Artikel Ini

  • Jenis-jenis kunci MySQL dan dampaknya
  • Cara memeriksa kunci berdasarkan versi MySQL
  • Prosedur aman untuk melepaskan kunci
  • Saran praktis untuk mencegah deadlock

Mari kita mulai dengan menjelaskan konsep dasar penguncian MySQL.

2. Konsep Dasar Penguncian MySQL

Dalam basis data, “kunci” adalah mekanisme yang membatasi akses untuk menjaga integritas data ketika beberapa transaksi berusaha memodifikasi data secara bersamaan. Jika kunci tidak dikelola dengan baik, penurunan kinerja dan deadlock dapat terjadi.

2.1 Jenis Utama Kunci

Di MySQL, terdapat beberapa jenis kunci tergantung pada tingkat perlindungan data yang dibutuhkan.

Kunci Baris

  • Mengunci hanya baris tertentu, meminimalkan dampak pada transaksi lain.
  • Didukung hanya oleh mesin penyimpanan InnoDB.
  • Terpicu saat menggunakan SELECT ... FOR UPDATE atau SELECT ... LOCK IN SHARE MODE .

Kunci Tabel

  • Mengunci seluruh tabel, mencegah beberapa kueri dijalankan secara bersamaan.
  • Umumnya digunakan dengan mesin penyimpanan MyISAM.
  • Terpicu saat menggunakan pernyataan LOCK TABLES.

Kunci Niat

  • Kunci yang digunakan untuk mengoordinasikan kunci baris dan kunci tabel guna menghindari konflik.
  • Hanya digunakan di InnoDB dan dikelola secara otomatis.

Deadlock

  • Suatu keadaan di mana beberapa transaksi menunggu kunci satu sama lain.
  • Jika transaksi tidak dirancang dengan baik, proses dapat berhenti sepenuhnya.

2.2 Contoh Terjadinya Kunci

Mari kita lihat contoh kueri SQL spesifik untuk memahami bagaimana kunci terjadi.

Contoh Kunci Baris

Menjalankan SQL berikut akan mengunci sebuah baris tertentu.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Jika sesi lain mencoba memperbarui baris yang sama, ia akan masuk ke keadaan menunggu kunci (kontensi kunci).

Contoh Kunci Tabel

Untuk mengunci seluruh tabel, gunakan perintah berikut.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Sampai kunci ini dilepaskan, pengguna lain tidak dapat memodifikasi data di tabel products.

Contoh Deadlock

Berikut ini menunjukkan skenario deadlock yang umum.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

Dalam situasi ini, setiap transaksi menunggu transaksi lain melepaskan kuncinya, yang menghasilkan deadlock.

3. Cara Memeriksa Status Kunci MySQL (Berdasarkan Versi)

Untuk menentukan apakah kunci sedang terjadi, Anda perlu menjalankan perintah yang sesuai dengan versi MySQL Anda.

3.1 Cara Memeriksa Kunci di MySQL 5.6 dan Sebelumnya

Di MySQL 5.6 dan sebelumnya, Anda dapat memeriksa informasi kunci menggunakan SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Menjalankan perintah ini menampilkan informasi terperinci tentang kunci yang sedang aktif.

3.2 Cara Memeriksa Kunci di MySQL 5.7

Di MySQL 5.7 dan yang lebih baru, metode termudah adalah menggunakan tabel sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

Dengan menanyakan tabel ini, Anda dapat mengidentifikasi transaksi mana yang menunggu kunci.

3.3 Cara Memeriksa Kunci di MySQL 8.0 dan Versi Selanjutnya

Di MySQL 8.0 dan versi selanjutnya, Anda dapat mengambil informasi kunci yang lebih detail menggunakan performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Untuk mengidentifikasi sesi yang memegang kunci, gunakan SQL berikut:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

Ini memungkinkan Anda menargetkan proses yang bertanggung jawab atas kunci tersebut.

4. Cara Melepaskan Kunci MySQL (Risiko Dijelaskan)

Jika sebuah kunci terjadi di MySQL dan tidak ditangani dengan benar, proses dapat terhenti dan kinerja basis data dapat menurun.
Di bagian ini, kami akan menjelaskan cara melepaskan kunci dan risiko yang terlibat.

4.1 Mengidentifikasi Sesi yang Memegang Kunci

Sebelum melepaskan kunci, Anda harus mengidentifikasi sesi mana yang memegangnya. Gunakan SQL berikut untuk memeriksa sesi yang mengalami penantian kunci:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Kueri ini menampilkan sesi yang menunggu kunci.

Di MySQL 8.0 dan versi selanjutnya, Anda dapat memperoleh informasi kunci yang detail menggunakan:

SELECT * FROM performance_schema.data_locks;

4.2 Melepaskan Kunci Menggunakan Perintah KILL

Setelah Anda mengidentifikasi sesi yang memegang kunci, Anda dapat melepaskannya dengan memaksa menghentikan proses.

1. Periksa proses yang memegang kunci

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Hentikan sesi menggunakan perintah KILL

KILL <process_id>;

Sebagai contoh, untuk menghentikan proses dengan ID=12345, jalankan:

KILL 12345;

⚠️ Risiko Perintah KILL

  • Transaksi yang dihentikan secara paksa akan di-rollback
  • Misalnya, perubahan yang dibuat oleh pernyataan UPDATE yang terputus dapat dibuang.
  • Hal ini dapat menyebabkan kesalahan aplikasi
  • Jika Anda sering perlu menggunakan KILL, Anda harus meninjau desain aplikasi Anda.

4.3 Melepaskan Kunci dengan ROLLBACK (Metode Lebih Aman)

Sebelum menggunakan perintah KILL, jika memungkinkan, coba mengakhiri secara manual transaksi yang menyebabkan kunci.

1. Pertama, periksa transaksi saat ini

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Jika Anda menemukan transaksi yang bermasalah, jalankan ROLLBACK

ROLLBACK;

Metode ini memungkinkan Anda melepaskan kunci sambil menjaga konsistensi data.

4.4 Mengotomatisasi Penanganan Kunci dengan SET innodb_lock_wait_timeout

Alih-alih melepaskan kunci secara manual, Anda dapat mengatur batas waktu penantian kunci sehingga transaksi secara otomatis berakhir jika kunci tidak dilepaskan dalam waktu tertentu.

SET innodb_lock_wait_timeout = 10;

Dengan pengaturan ini, jika kunci tidak dilepaskan dalam 10 detik, MySQL mengembalikan error dan secara otomatis mengakhiri transaksi.

5. Catatan Penting dan Praktik Terbaik untuk Kunci MySQL

Manajemen kunci yang tepat membantu mengurangi risiko deadlock dan penurunan kinerja. Berikut adalah praktik terbaik untuk meminimalkan kunci dan mengelolanya secara efisien.

5.1 Cara Mencegah Deadlock

Untuk mencegah deadlock, perhatikan poin-poin berikut:

1. Standarisasi Urutan Eksekusi Transaksi

  • Misalnya, saat memperbarui beberapa tabel, selalu perbarui dalam urutan yang sama .
  • Contoh:
    -- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: Urutan eksekusi yang berbeda dapat menyebabkan deadlock

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Sesi 2 (deadlock dapat terjadi jika dijalankan dalam urutan terbalik)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks .
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .
    CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads