Help understanding InnoDB Deadlock Detection
Your (2) transaction holds S-lock on table Resources.ResourceAllocations. (1) and (2) transactions wait for X-lock for that table -> deadlock.

What happened:


Trx(2) got S-lock on ResourceAllocations with the select statement (because of create table ... select)
Trx(1) needs X-lock to make update on ResourceAllocations -> wait for X-lock
Trx(2) needs X-lock (for unknown reason) on ResourceAllocations -> wait for X-lock


As the result Trx(1) waits for X-lock from Trx(2) (because Trx(2) has S-lock) and Trx(2) waits for X-lock from Trx(1) (because it is behind Trx(1) in a queue for X-lock).

It's hard for me to say what can be done because I do not see other queries in transactions, but the solution definitely would be: do not require for X-lock in transaction (2).

In simple words: commit transaction(2) after create table statement or do not update tables in this statement from any other transactions until it is done.