mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Tabel pertama [tabel1]
mysql> CREATE TABLE table1 [ id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR[50]];
mysql> INSERT INTO table1 SET data = 'data #1';Tabel kedua [tabel2]
mysql> CREATE TABLE table2 LIKE table1;
mysql> INSERT INTO table2 SET data = 'data #2';Kami mengeksekusi transaksi kami dalam dua sesi berbeda dengan urutan sebagai berikut
Memerintah
Transaksi #1 [T1]
Transaksi #2 [T2]
1
PILIH * DARI tabel1;
[OKE]
PILIH * DARI tabel1;
[OKE]
2
UPDATE table1 SET data = 'T1 memperbarui baris' WHERE id = 1;
[OKE]
3
UPDATE table2 SET data = 'T2 memperbarui baris' WHERE id = 1;
[OKE]
4
UPDATE table1 SET data = ‘T2 sedang memperbarui baris’ WHERE id = 1;
[Hang sebentar dan akhirnya mengembalikan kesalahan "Kunci batas waktu tunggu terlampaui; coba mulai ulang transaksi"]
5
MELAKUKAN;
[OKE]
6
MELAKUKAN;
[OKE]
Namun, hasil akhir setelah langkah #6 mungkin mengejutkan jika kita tidak mencoba kembali pernyataan waktu habis pada langkah #4
mysql> SELECT * FROM table1 WHERE id = 1;
+----+-----------------------------------+
| id | data |
+----+-----------------------------------+
| 1 | T1 is updating the row |
+----+-----------------------------------+
mysql> SELECT * FROM table2 WHERE id = 1;
+----+-----------------------------------+
| id | data |
+----+-----------------------------------+
| 1 | T2 is updating the row |
+----+-----------------------------------+Setelah T2 berhasil dikomit, orang akan mengharapkan untuk mendapatkan keluaran yang sama “T2 memperbarui baris ” untuk table1 dan table2 tetapi . Orang mungkin berpikir bahwa jika ada kesalahan dalam transaksi, semua pernyataan dalam transaksi akan secara otomatis dibatalkan, atau jika transaksi berhasil dilakukan, seluruh pernyataan dieksekusi secara atomik. Ini berlaku untuk kebuntuan, tetapi tidak untuk batas waktu tunggu kunci InnoDB.
Kecuali jika Anda menyetel innodb_rollback_on_timeout=1 [default adalah 0 – dinonaktifkan], rollback otomatis tidak akan terjadi untuk kesalahan waktu tunggu kunci InnoDB. Ini berarti, dengan mengikuti pengaturan default, MySQL tidak akan gagal dan mengembalikan seluruh transaksi, atau mencoba kembali pernyataan yang telah habis waktunya dan hanya memproses pernyataan berikutnya hingga mencapai COMMIT atau ROLLBACK. Ini menjelaskan mengapa transaksi T2 dilakukan sebagian.
dengan jelas mengatakan "InnoDB hanya mengembalikan pernyataan terakhir pada waktu tunggu transaksi secara default". Dalam hal ini, kami tidak mendapatkan atomisitas transaksi yang ditawarkan oleh InnoDB. Atomisitas sesuai dengan ACID adalah kita mendapatkan semua atau tidak sama sekali dari transaksi, yang berarti transaksi sebagian tidak dapat diterima.
Berurusan Dengan Timeout Tunggu InnoDB Lock
Jadi, jika Anda mengharapkan transaksi untuk auto-rollback ketika menemukan kesalahan menunggu kunci InnoDB, sama seperti apa yang akan terjadi dalam kebuntuan, tetapkan opsi berikut di file konfigurasi MySQL
innodb_rollback_on_timeout=1Diperlukan restart MySQL. Saat men-deploy cluster berbasis MySQL, ClusterControl akan selalu menyetel innodb_rollback_on_timeout=1 di setiap node. Tanpa opsi ini, aplikasi Anda harus mencoba kembali pernyataan yang gagal, atau melakukan ROLLBACK secara eksplisit untuk mempertahankan atomisitas transaksi.
Untuk memverifikasi apakah konfigurasi dimuat dengan benar
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+Untuk memeriksa apakah konfigurasi baru berfungsi, kita dapat melacak penghitung com_rollback saat kesalahan ini terjadi
mysql> SHOW GLOBAL STATUS LIKE 'com_rollback';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback | 1 |
+---------------+-------+Melacak Transaksi Pemblokiran
Ada beberapa tempat yang bisa kita cari untuk melacak transaksi atau laporan pemblokiran. Mari kita mulai dengan melihat status mesin InnoDB di bagian TRANSAKSI
mysql> SHOW ENGINE INNODB STATUSG
------------
TRANSACTIONS
------------
...
---TRANSACTION 3100, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct[s], heap size 1136, 1 row lock[s]
MySQL thread id 50, OS thread handle 139887555282688, query id 360 localhost ::1 root updating
update table1 set data = 'T2 is updating the row' where id = 1
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `mydb`.`table1` trx id 3100 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000c19; asc ;;
2: len 7; hex 020000011b0151; asc Q;;
3: len 22; hex 5431206973207570646174696e672074686520726f77; asc T1 is updating the row;;
------------------
---TRANSACTION 3097, ACTIVE 46 sec
2 lock struct[s], heap size 1136, 1 row lock[s], undo log entries 1
MySQL thread id 48, OS thread handle 139887556167424, query id 358 localhost ::1 root
Trx read view will not see trx with id >= 3097, sees < 3097Dari informasi di atas, kita bisa mendapatkan gambaran transaksi yang sedang aktif di server. Transaksi 3097 saat ini sedang mengunci baris yang perlu diakses oleh transaksi 3100. Namun, keluaran di atas tidak memberi tahu kami teks kueri sebenarnya yang dapat membantu kami menentukan bagian mana dari kueri/pernyataan/transaksi yang perlu kami selidiki lebih lanjut. Dengan menggunakan pemblokir MySQL thread ID 48, mari kita lihat apa yang dapat kita kumpulkan dari daftar proses MySQL
mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+
| 4 | event_scheduler | localhost | | Daemon | 5146 | Waiting on empty queue | |
| 10 | root | localhost:56042 | performance_schema | Query | 0 | starting | show full processlist |
| 48 | root | localhost:56118 | mydb | Sleep | 145 | | |
| 50 | root | localhost:56122 | mydb | Sleep | 113 | | |
+----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+Thread ID 48 menunjukkan perintah sebagai 'Tidur'. Namun, ini tidak banyak membantu kami untuk mengetahui pernyataan mana yang memblokir transaksi lainnya. Ini karena pernyataan dalam transaksi ini telah dieksekusi dan transaksi terbuka ini pada dasarnya tidak melakukan apa-apa saat ini. Kita perlu menyelam lebih jauh ke bawah untuk melihat apa yang terjadi dengan utas ini
Untuk MySQL 8. 0, instrumentasi tunggu kunci InnoDB tersedia di bawah tabel data_lock_waits di dalam database performance_schema [atau tabel innodb_lock_waits di dalam database sys]. Jika peristiwa lock wait terjadi, kita akan melihat sesuatu seperti ini.
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Perhatikan bahwa di MySQL 5. 6 dan 5. 7, informasi serupa disimpan di dalam tabel innodb_lock_waits di bawah database information_schema. Perhatikan nilai BLOCKING_THREAD_ID. Kita dapat menggunakan informasi ini untuk mencari semua pernyataan yang dijalankan oleh utas ini di tabel events_statements_history.
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Sepertinya informasi utas sudah tidak ada lagi. Kami dapat memverifikasi dengan memeriksa nilai minimum dan maksimum kolom thread_id di tabel events_statements_history dengan kueri berikut
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Utas yang kami cari [87] telah terpotong dari tabel. Kami dapat mengonfirmasi ini dengan melihat ukuran tabel event_statements_history
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Di atas berarti events_statements_history hanya dapat menyimpan 10 utas terakhir. Untungnya, performance_schema memiliki tabel lain untuk menyimpan lebih banyak baris yang disebut events_statements_history_long, yang menyimpan informasi serupa tetapi untuk semua utas dan dapat berisi lebih banyak baris
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Namun, Anda akan mendapatkan hasil kosong jika mencoba menanyakan tabel events_statements_history_long untuk pertama kalinya. Ini diharapkan karena secara default, instrumentasi ini dinonaktifkan di MySQL seperti yang dapat kita lihat pada tabel setup_consumers berikut
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Untuk mengaktifkan tabel events_statements_history_long, kita perlu memperbarui tabel setup_consumers seperti di bawah ini
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Verifikasi apakah ada baris di tabel events_statements_history_long sekarang
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Keren. Sekarang kita bisa menunggu hingga event InnoDB lock wait muncul lagi dan ketika itu terjadi, Anda akan melihat baris berikut di tabel data_lock_waits .
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Sekali lagi, kami menggunakan nilai BLOCKING_THREAD_ID untuk memfilter semua pernyataan yang telah dijalankan oleh utas ini terhadap tabel events_statements_history_long .
mysql> CREATE SCHEMA mydb;
mysql> USE mydb;Akhirnya kami menemukan pelakunya. Kita dapat mengetahuinya dengan melihat urutan kejadian pada thread 57 dimana transaksi di atas [T1] masih belum selesai [tidak ada COMMIT atau ROLLBACK], dan kita dapat melihat pernyataan terakhir telah memperoleh kunci eksklusif ke baris untuk pembaruan . Itu menjelaskan mengapa kita melihat 'Tidur' di keluaran daftar proses MySQL
Seperti yang bisa kita lihat, pernyataan SELECT di atas mengharuskan Anda untuk mendapatkan nilai thread_id terlebih dahulu. Untuk menyederhanakan kueri ini, kita dapat menggunakan klausa IN dan subquery untuk menggabungkan kedua tabel. Permintaan berikut menghasilkan hasil yang identik seperti di atas
mysql> CREATE TABLE table1 [ id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR[50]];
mysql> INSERT INTO table1 SET data = 'data #1';Namun, tidak praktis bagi kami untuk mengeksekusi kueri di atas setiap kali peristiwa menunggu kunci InnoDB terjadi. Selain kesalahan dari aplikasi, bagaimana Anda tahu bahwa peristiwa menunggu kunci sedang terjadi? . sh track_lockwait.sh .
mysql> CREATE TABLE table1 [ id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR[50]];
mysql> INSERT INTO table1 SET data = 'data #1';Terapkan izin yang dapat dieksekusi dan daemonisasi skrip di latar belakang
mysql> CREATE TABLE table1 [ id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR[50]];
mysql> INSERT INTO table1 SET data = 'data #1';Sekarang, kita hanya perlu menunggu laporan dibuat di direktori /root/lockwait. Bergantung pada beban kerja basis data dan pola akses baris, Anda mungkin melihat banyak file di bawah direktori ini. Pantau direktori dengan cermat jika tidak maka akan dibanjiri dengan terlalu banyak file laporan
Jika Anda menggunakan ClusterControl, Anda dapat mengaktifkan fitur Log Transaksi di bawah Kinerja -> Log Transaksi di mana ClusterControl akan memberikan laporan tentang kebuntuan dan transaksi yang berjalan lama yang akan memudahkan .
Kesimpulan
Singkatnya, jika kita menghadapi kesalahan "Lock Wait Timeout Exceeded" di MySQL, pertama-tama kita harus memahami efek kesalahan tersebut terhadap infrastruktur kita, kemudian melacak transaksi ofensif dan menindaklanjutinya dengan . sh track_lockwait.sh , atau perangkat lunak manajemen basis data seperti ClusterControl.
Jika Anda memutuskan untuk menggunakan skrip shell, perlu diingat bahwa skrip tersebut dapat menghemat uang Anda tetapi akan menghabiskan waktu Anda, karena Anda perlu mengetahui satu atau dua hal tentang cara kerjanya, menerapkan izin, dan mungkin membuatnya berjalan di