Mysql – How do Locks Work in MySQL or MariaDB

There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of these statements may be not proper, so they may lock each other (across two transactions). But what are possible causes of deadlocks when each transaction (request with auto commit) has a single statement (without subqueries)? Could someone prove, that if a select statement uses "update where in" clause against a key field, it actually makes two locks: one for all keys in the table since the row is updated, and the other one for the key in criteria since "in" operator is used?

It seems like this question was unclear, so I post some content from InnoDB status query (some sensitive data were hidden):

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-02-08 08:47:09 2b9abb9f8700
*** (1) TRANSACTION:
TRANSACTION 13865307, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 152875, OS thread handle 0x2b99d4797700, query id 4452586 {{IP-Address}} {{Username}} update
INSERT INTO friend (user_id, friend_id) VALUES ({{User-ID}}, {{Friend-ID}})
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 1147 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 2 total table locks 5  trx id 13865307 lock mode S locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** (2) TRANSACTION:
TRANSACTION 13865292, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
2476 lock struct(s), heap size 276008, 58981 row lock(s)
MySQL thread id 151667, OS thread handle 0x2b9abb9f8700, query id 4452574 {{IP-Address}} {{Username}} updating
UPDATE user SET user.app_status = '1' WHERE (user.app_id = 1) AND (user.app_user_id in ({{App-User-ID}}))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 40 page no 1147 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 1 total table locks 5  trx id 13865292 lock_mode X lock hold time 0 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 6095 n bits 88 index `PRIMARY` of table `{{DB-Name}}`.`user` trx table locks 1 total table locks 5  trx id 13865292 lock_mode X waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (1)

So the question arising is the following: How it is possible, that a single UPDATE (transaction no. 2) requires two locks on the same key (PRIMARY) and goes into a deadlock? My initial hypothesis was, that it may be connected with WHERE IN statement (though app_user_id is a regular key). So I tried to clarify, whether this criteria may cause a double acquiring of the lock. And if it does, then could a simple check for equality (namely, WHERE user.app_user_id = '{{App-User-ID}}') make this update more atomic?

Some notes regarding database instance. This case happened on 10.0.17-MariaDB. But as far as I understand, its behavior should be similar to MySQL 5.6, since they are mutually compatible. Each table has a sequential primary key; there are two foreign keys from user table to friend one; and also app_user_id is an indexed field.

Pardon me for being not clear enough originally and too verbose, but I primary work with in-application concurrency, so competitions happening on database layer are not always clear and predictable for me.

Here are create statements for user and friend tables as asked in comments by Rick James

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `app_id` int(11) NOT NULL,
  `app_user_id` char(100) NOT NULL,
  `full_name` char(250) NOT NULL,
  `first_name` char(100) DEFAULT NULL,
  `last_name` char(100) DEFAULT NULL,
  `image` char(250) DEFAULT NULL,
  `session_id` char(50) DEFAULT NULL,
  `last_date` datetime DEFAULT NULL,
  `app_status` tinyint(1) NOT NULL DEFAULT '0',
  `app_last_date` datetime DEFAULT NULL,
  `date_create` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_app_user_id` (`app_user_id`),
  KEY `app_id` (`app_id`),
  KEY `app_user_id` (`app_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=152258 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `friend` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `friend_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK__user` (`user_id`),
  KEY `FK__user_2` (`friend_id`),
  CONSTRAINT `FK__user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK__user_2` FOREIGN KEY (`friend_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=232236 DEFAULT CHARSET=utf8mb4;