how to KNOW if a transaction holds exclusive lock in a row?

I am learning transactions, locks, etc and I want to know how to know if a transaction holds exclusive lock in a row

I tried to simulate it using the MySQL command line, but I'm not sure if it's a correct way to KNOW if a transaction holds exclusive lock in a row, could you confirm if my test is correct? I know it's working because I'm seeing it, but maybe it's just a coincidence.

I have tried to make this test in the sequence as is:

open transaction in connection 1:

begin;
UPDATE users SET rol = 'Admin' WHERE email LIKE '%example.net%';
Query OK, 182 rows affected (0.03 sec)
Rows matched: 182  Changed: 182  Warnings: 0

now, open another transaction in connection 2 (This is a row that meets the condition of connection 1):

begin;
UPDATE users SET rol = 'Admin' WHERE id = 11; // Blocked, it's waiting

now, I'm going to close transaction in Connection 1:

rollback;
Query OK, 0 rows affected (0.02 sec)

Immediately (automatically) connection 2:

Query OK, 1 row affected (18.33 sec) // "Free"
Rows matched: 1  Changed: 1  Warnings: 0
rollback; // I close the another transaction

1. If my example above to KNOW if a transaction holds exclusive locks on certain rows is correct, would it work for a DELETE and SELECT ... FOR UPDATE since it also sets exclusive locks?

On the other hand, the documentation says as follows:

Atomic statement

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

2. Does "each statement is atomic" refer to a single statement (only one)?
3. Is a single statement still atomic even if the condition "spans" multiple rows (e.g. the UPDATE of connection 1)?

1. If my example above to KNOW if a transaction holds exclusive locks on certain rows is correct, would it work for a DELETE and SELECT ... FOR UPDATE since it also sets exclusive locks?

Yes, you can run similar tests for other locking statements to prove it.

2. Does "each statement is atomic" refer to a single statement (only one)?

Yes, autocommit mode means each single statement starts and commits one transaction.

3. Is a single statement still atomic even if the condition "spans" multiple rows (e.g. the UPDATE of connection 1)?

Yes. In the default transaction isolation level, the statement acquires locks on all rows it examines. That may be more rows than are affected by an UPDATE or returned by a SELECT, because the conditions in the query may filter the rows.

For example, in your example UPDATE:

UPDATE users SET rol = 'Admin' WHERE email LIKE '%example.net%';

The condition LIKE with a wildcard pattern examines all the rows in the table, even if you have defined an index on the column to search. Therefore all the rows will be locked by this UPDATE, even those rows that don't satisfy the condition.

The statement acquires locks atomically. That means it either acquires all the locks needed, or else if it fails (for instance, because some rows are already locked by another session), it must release the other locks it tried to acquire in that statement, even those that would have been successful.