MySQL menggunakan metadata lock untuk mengatur akses ke objek basis data yang terhubung secara bersamaan dan menjamin konsistensi data. Hal ini mengakibatkan perintah akan menunggu metadata lock dari perintah sebelumnya dilepas.
Salah satu permasalahan yang timbul adalah ketika aplikasi menggunakan koneksi persisten dan administrator ingin melakukan perubahan pada tabel, prosedur atau objek lainnya dengan perintah alter.
Masalah lainnya adalah ketika aplikasi membuka transaksi tetapi tidak melakukan commit, entah karena ada error atau sebab lain sehingga commit tidak dijalankan .
Melepas Meta Data Lock
Metadata lock pada MySQL menyebabkan administrator tidak dapat melakukan alter pada objek basis data sampai metadata lock dilepas, baik dari aplikasi yang menggunakan objek basis data maupun dengan cara memutus koneksi/thread dari perintah yang mengakses objek basis data tersebut.
— Tips Melepas MySQL Metadata Lock
Alur Pikir
Berikut adalah sintaks umum untuk mengaktifkan monitoring metadata lock dan membuat list ID guna menjalankan perintah kill.
#sintaks umum
#aktifkan monitoring metadata-locks
#UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
#lihat locking
#perbedaan ada di ps_thread_id(ID) dan sys.ps_thread_id(ID)
#minimal MySQL 8.0.16
#SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>;
#SELECT *, ps_thread_id(ID) as thread_id FROM information_schema.PROCESSLIST where ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>);
#sebelum MySQL 8.0.16
#SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>;
#SELECT *, sys.ps_thread_id(ID) as thread_id FROM information_schema.PROCESSLIST where sys.ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>);
#generate perintah kill
#perbedaan ada di ps_thread_id(ID) dan sys.ps_thread_id(ID)
#minimal MySQL 8.0.16
#SELECT concat("kill ", ID, ";") as taskkill FROM information_schema.PROCESSLIST where ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>);
#sebelum MySQL 8.0.16
#SELECT concat("kill ", ID, ";") as taskkill FROM information_schema.PROCESSLIST where sys.ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks <filter>);Penjelasan dapat dilihat pada contoh penerapan berikut:
Pertama, aktifkan dahulu monitoring metadata lock
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';Kemudian, lihat informasi thread apa saja yang menggunakan objek tersebut. Sesuaikan informasi basis data dan tabel di bawah ini:
Basis Data
Tabel
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, OWNER_THREAD_ID FROM performance_schema.metadata_locks where object_schema='' and object_name='' ; +-------------+---------------+-------------+-----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OWNER_THREAD_ID | +-------------+---------------+-------------+-----------------+ | TABLE | db1 | tagihan | 106815 | | TABLE | db1 | tagihan | 107024 | | TABLE | db1 | tagihan | 104241 | | TABLE | db1 | tagihan | 106306 | | TABLE | db1 | tagihan | 108965 | | TABLE | db1 | tagihan | 109798 | | TABLE | db1 | tagihan | 108950 | | TABLE | db1 | tagihan | 108919 | | TABLE | db1 | tagihan | 109667 | | TABLE | db1 | tagihan | 105723 | | TABLE | db1 | tagihan | 107421 | | TABLE | db1 | tagihan | 107199 | | TABLE | db1 | tagihan | 107982 | | TABLE | db1 | tagihan | 107851 | | TABLE | db1 | tagihan | 107152 | | TABLE | db1 | tagihan | 110062 | | TABLE | db1 | tagihan | 112179 | | TABLE | db1 | tagihan | 111332 | | TABLE | db1 | tagihan | 104837 | | TABLE | db1 | tagihan | 104757 | | TABLE | db1 | tagihan | 104389 | | TABLE | db1 | tagihan | 104823 | +-------------+---------------+-------------+-----------------+ 22 rows in set (0.01 sec)
Dari contoh tersebut tampak bahwa ada 22 thread yang menggunakan objek dengan id thread yang tertera pada kolom OWNER_THREAD_ID (The thread requesting a metadata lock). OWNER_THREAD_ID merupakan ID thread pada sistem operasi.
OWNER_THREAD_ID digunakan untuk melihat informasi pada processlist ID-nya, karena ID pada perintah kill tidak merujuk pada OWNER_THREAD_ID tetapi ID processlist.
SELECT ID, USER, DB, TIME, sys.ps_thread_id(ID) as thread_id FROM information_schema.PROCESSLIST where sys.ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks where object_schema='' and object_name='' ); +--------+-----------+------+------+-----------+ | ID | USER | DB | TIME | thread_id | +--------+-----------+------+------+-----------+ | 106281 | crud_only | db1 | 931 | 106306 | | 110037 | crud_only | db1 | 441 | 110062 | | 107957 | crud_only | db1 | 722 | 107982 | | 107396 | crud_only | db1 | 801 | 107421 | | 113063 | crud_only | db2 | 14 | 113088 | | 104364 | crud_only | db1 | 1189 | 104389 | | 111307 | crud_only | db1 | 248 | 111332 | | 104732 | crud_only | db1 | 1145 | 104757 | | 109642 | crud_only | db1 | 509 | 109667 | | 109773 | crud_only | db1 | 489 | 109798 | | 108940 | crud_only | db1 | 581 | 108965 | | 104798 | crud_only | db1 | 1133 | 104823 | | 104812 | crud_only | db1 | 1130 | 104837 | | 107174 | crud_only | db1 | 804 | 107199 | | 112154 | crud_only | db2 | 129 | 112179 | | 106999 | crud_only | db1 | 880 | 107024 | | 108925 | crud_only | db1 | 583 | 108950 | | 107826 | crud_only | db1 | 739 | 107851 | | 105698 | crud_only | db2 | 1009 | 105723 | | 106790 | crud_only | db1 | 911 | 106815 | | 107127 | crud_only | db1 | 846 | 107152 | | 108894 | crud_only | db1 | 587 | 108919 | +--------+-----------+------+------+-----------+ 22 rows in set (0.32 sec)
Perintah tersebut diubah sedikit agar menampilkan perintah kill.
SELECT concat("kill ", ID, ";") as taskkill FROM information_schema.PROCESSLIST where sys.ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks where object_schema='' and object_name='' );
+---------------+
| taskkill |
+---------------+
| kill 106281; |
| kill 110037; |
| kill 107957; |
| kill 107396; |
| kill 113063; |
| kill 104364; |
| kill 111307; |
| kill 104732; |
| kill 109642; |
| kill 109773; |
| kill 108940; |
| kill 104798; |
| kill 104812; |
| kill 107174; |
| kill 112154; |
| kill 106999; |
| kill 108925; |
| kill 107826; |
| kill 105698; |
| kill 106790; |
| kill 107127; |
| kill 108894; |
+---------------+
22 rows in set (0.32 sec)Kill Metadata Lock Dengan Stored Procedure
Berdasarkan langkah di atas, dapat diringkas ke dalam stored procedure agar mudah dijalankan dengan perintah call kill_process('' , '' ).
CREATE DEFINER=`root`@`%` PROCEDURE `kill_process`(schema_ varchar(100), name_ varchar(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE taskkill_ INT;
DECLARE cur_ CURSOR FOR SELECT ID FROM information_schema.PROCESSLIST where sys.ps_thread_id(ID) in (SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks where object_schema=schema_ and object_name=name_);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_;
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl' AND (ENABLED = 'NO' OR TIMED = 'NO');
read_loop: LOOP
FETCH cur_ INTO taskkill_;
IF done THEN
LEAVE read_loop;
END IF;
KILL taskkill_;
END LOOP;
CLOSE cur_;
ENDDemikian, semoga bermanfaat. [bst]
Sumber
