MySQL lock when using a connection

Question

I have a question about mysql / innodb blocking when selecting "FOR UPDATE" in a table 2 join query. Example:

2 tables - items , queue . queue.id - 1: 1 connection with items.id (FOREIGN KEY). queue.status is an enumeration with an index. there are very few rows in the queue, and the table of elements is relatively large.

 SELECT * FROM `items` INNER JOIN queue ON items.id = queue.id WHERE queue.status = 'new' FOR UPDATE; 

• Will the selected rows in the items table be locked?
• What other locks are received? (except, obviously, all rows in the queue table that have statistics = 'new').
• Are there common locks that can cause a deadlock. I read somewhere that the insert puts the next key shared lock in the auto-increment index, and then when using SELECT ... FOR UPDATE (in the same transaction), the lock is updated to exceptional, this makes the deadlock easier - 2 threads can get shared and then they will both be waiting for each other to free the castle in order to get an exclusive castle. is this possible in this case (also heard that foreign keys make common locks).

Answer 1

Yes, each selected row (*) will be locked. You really don't need to worry about a dead end. It takes quite a lot to create it, and when that happens, it is mainly a customer mistake.