.::: How to Sample Simulation Blocking, Identifying and Avoiding Deadlocks MariaDB MySQL :::.

 1. open 3 terminal to connect mariadb

[root@teguhth ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use teguhth;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [teguhth]>

2. run Transaction 1

START TRANSACTION;
SELECT * FROM pembelian WHERE KODE_PEMBELIAN = 'BEL-E011' LOCK IN SHARE MODE;

3. run Transaction 2 (wait)

START TRANSACTION;
UPDATE pembelian
SET KODE_CUSTOMER = 'J-1111'
WHERE KODE_PEMBELIAN = 'BEL-E011';
 

4. Transaction 3 (deadlock)
START TRANSACTION;
UPDATE pembelian
SET KODE_CUSTOMER = 'J-2222'
WHERE KODE_PEMBELIAN = 'BEL-E011';
 

5. monitoring

SELECT @@hostname,id,User,host,db,command,time,time_ms,state,info FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY time_ms desc;

SELECT  @@hostname as Hostname,
    r.trx_id AS waiting_trx_id,  
    r.trx_mysql_thread_id AS waiting_thread,  
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b
        ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx AS r
        ON r.trx_id = w.requesting_trx_id;