MySQL implements several locking strategies to maintain data integrity during concurrent operations. The main types include:

• Table-level locks (MyISAM)
• Row-level locks (InnoDB)
• Metadata locks
• Gap locks
• Next-key locks

The primary way to view locks in MySQL is through the INFORMATION_SCHEMA database or SHOW ENGINE commands.

Using INFORMATION_SCHEMA

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

Using SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

Here's a comprehensive query to identify blocking transactions:

SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query 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 r ON r.trx_id = w.requesting_trx_id;

For MySQL 5.7+ you can check metadata locks with:

SELECT * FROM performance_schema.metadata_locks;

Create a stored procedure for regular lock checks:

DELIMITER //
CREATE PROCEDURE monitor_locks()
BEGIN
  SELECT NOW() AS check_time, COUNT(*) AS lock_count 
  FROM information_schema.INNODB_LOCKS;
  
  SELECT * FROM information_schema.INNODB_LOCK_WAITS;
END //
DELIMITER ;

MySQL 8.0+ provides enhanced lock monitoring:

SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%lock%';

Case 1: Long-running transactions causing locks

-- Identify long transactions
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

Case 2: Uncommitted transactions holding locks

-- Check for idle transactions
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'RUNNING' AND trx_operation_state IS NULL;

---

To view all active locks in MySQL, you can query the performance_schema and information_schema tables. The most comprehensive approach combines several system tables:

SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM 
    performance_schema.data_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

For different MySQL versions, you might need these alternative queries:

Using SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

Look for the "TRANSACTIONS" section which shows lock information including wait-for graphs.

For MySQL 5.7+

SELECT * FROM sys.innodb_lock_waits;

Enable these performance schema instruments for detailed lock tracking:

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_waits%';

To distinguish between different lock modes:

SELECT 
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM 
    performance_schema.data_locks
WHERE 
    THREAD_ID = sys.ps_thread_id(connection_id());

Create a stored procedure for regular lock checks:

DELIMITER //
CREATE PROCEDURE monitor_locks(IN interval_seconds INT, IN max_checks INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    WHILE counter < max_checks DO
        SELECT NOW() AS check_time, * FROM sys.innodb_lock_waits;
        SET counter = counter + 1;
        DO SLEEP(interval_seconds);
    END WHILE;
END //
DELIMITER ;

Key columns to analyze:

• blocking_trx_id: The transaction holding the lock
• waiting_trx_id: The transaction waiting for the lock
• lock_mode: X (exclusive), S (shared), etc.
• lock_type: RECORD (row lock) or TABLE (table lock)

Enable deadlock logging in my.cnf:

[mysqld]
innodb_print_all_deadlocks = ON

Then check MySQL error log for detailed deadlock reports.