.::: 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;

 

additional

 

SELECT
    @@hostname AS Hostname,
    pl.id AS thread_id,
    pl.USER,
    pl.HOST,
    pl.DB,
    pl.COMMAND,
    pl.TIME,
    pl.TIME_MS,
    pl.STATE,
    pl.INFO,

    -- Blocking Info
    rw.trx_id AS waiting_trx_id,
    rw.trx_query AS waiting_query,
    bw.trx_id AS blocking_trx_id,
    bw.trx_query AS blocking_query,
    bw.trx_mysql_thread_id AS blocking_thread

FROM
    INFORMATION_SCHEMA.PROCESSLIST pl
LEFT JOIN information_schema.innodb_trx rw
    ON pl.id = rw.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
    ON rw.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx bw
    ON lw.blocking_trx_id = bw.trx_id
ORDER BY
    pl.TIME_MS DESC;