A. Table-Level Locks

Table-level locking locks the entire table during a read or write operation. It is mainly used in storage engines like MyISAMand MEMORY. While simple to implement, it is less efficient in high-concurrency environments because it blocks other users from
accessing the same table simultaneously.

Syntax:

LOCK TABLES table_name READ;   -- Read-only access
LOCK TABLES table_name WRITE;  -- Read/write access, blocks others
UNLOCK TABLES;                 -- Releases all locks    

Example:

LOCK TABLES employees WRITE;
UPDATE employees SET salary = salary + 500;
UNLOCK TABLES;    

B. Row-Level Locks

Row-level locking locks only the rows affected by a query. It is supported
by the InnoDB storage engine. This type of locking allows higher concurrency and better performance compared to table-level locks, as different transactions can operate on different rows simultaneously.

Syntax:

START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
UPDATE orders SET status = 'shipped' WHERE id = 10;
COMMIT;

Example (inside a transaction):

START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
UPDATE orders SET status = 'shipped' WHERE id = 10;
COMMIT;