1. Shared Lock (S)

A shared lock allows other sessions to read the row but prevents them from writing to it. This is useful when you want to protect data from changes while reading.

Syntax:

SELECT * FROM products WHERE id = 5 LOCK IN SHARE MODE;    

Example:

START TRANSACTION;
SELECT * FROM products WHERE id = 5 LOCK IN SHARE MODE;
COMMIT;    

2. Exclusive Lock (X)

An exclusive lock prevents other sessions from both reading and writing the locked rows. Use this when you plan to update or delete the row.

Syntax:

SELECT * FROM products WHERE id = 5 FOR UPDATE;    

Example:

START TRANSACTION;
SELECT * FROM products WHERE id = 5 FOR UPDATE;
UPDATE products SET price = price + 10 WHERE id = 5;
COMMIT;    

Autocommit and Locking Behavior

By default, MySQL operates in autocommit mode, which commits each query
immediately and releases any acquired locks. To manage locks manually, disable autocommit
and use transactions.

Syntax:

SET autocommit = 0;
START TRANSACTION;
-- your queries here
COMMIT; -- or ROLLBACK;    

Example:

SET autocommit = 0;
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 101;
COMMIT;    

Viewing Locks (Information Schema)

InnoDB provides system views to inspect current locks and active transactions.
These are useful for diagnosing blocking issues, lock contention, and deadlocks.

Current Locks:

SELECT * FROM information_schema.innodb_locks;    

Example:

SELECT lock_id, lock_mode, lock_type, lock_table, lock_index
FROM information_schema.innodb_locks;    

Active Transactions:

SELECT * FROM information_schema.innodb_trx;    

Example:

SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx;