Why does InnoDB CREATE ... SELECT use an exclusive lock?

I'm trying to wrap my head around an InnoDB deadlock that occasionally shows itself:

------------------------
LATEST DETECTED DEADLOCK
------------------------
110511 10:45:59
*** (1) TRANSACTION:
TRANSACTION 0 959459752, ACTIVE 0 sec, process no 24148, OS thread id 2958613424 starting index read
mysql tables in use 16, locked 16
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 13029007, query id 85826239 localhost andrew updating
DELETE FROM `clients_permission_assignments` WHERE permission_assignment_id = 3761 AND client_id IN (52621)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1490959 n bits 864 index `unique_index` of table `test/clients_permission_assignments` trx id 0 959459752 lock_mode X waiting
Record lock, heap no 202 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000cd8d; asc     ;; 1: len 4; hex 80000eb1; asc     ;; 2: len 6; hex 0000006b7d5c; asc    k}\;;

*** (2) TRANSACTION:
TRANSACTION 0 959459751, ACTIVE 0 sec, process no 24148, OS thread id 1996331952 fetching rows, thread declared inside InnoDB 354
mysql tables in use 16, locked 16
20 lock struct(s), heap size 2496, undo log entries 1
MySQL thread id 13019094, query id 85826237 localhost andrew Copying to tmp table
CREATE TEMPORARY TABLE tmp_tests_people_cleanup_table (SELECT unit_code FROM (
              SELECT u.unit_code, COUNT(u.unit_code) AS cnt FROM staging.client_test_utilization u
              LEFT JOIN permission_assignments pa ON pa.person_id =
                  (SELECT person_id FROM permission_assignments pa WHERE pa.id = OLD.permission_assignment_id)
              LEFT JOIN permissions p ON pa.permission_id = p.id
              LEFT JOIN clients_permission_assignments cpa ON cpa.permission_assignment_id = pa.id
              LEFT JOIN clients c ON c.id = cpa.client_id
              LEFT JOIN staging.client_test_utilization u2 ON CONCAT('C',u2.client_number) = c.number
                  AND u2.unit_code = u.unit_code
              WHERE p.label = 'Receive Test Updates'
                  AND CONCAT('C',u.client_number) = (SELECT number from clients WHERE id = OLD.client_id)
                  AND u2.id IS NULL GROUP BY u.unit_code
          ) tbl
          WHERE cnt = (SELECT COUNT(*) FROM permission_assignments pa
                      LEFT JOIN permissions p ON pa.permission_id = p.id
                      LEFT JOIN clients_permission_assignments cpa ON cpa.permission_assignment_id = pa.id
                      WHERE p.label = 'Receive Test Updates' AND pa.person_id =
                          (SELECT person_id FROM permission_assignments pa WHERE pa.id = OLD.permission_assignment_id))
              OR (SELECT COUNT(*) FROM permission_assignments pa
                      LEFT JOIN permissions p ON pa.permission_id = p.id
                      LEFT JOIN clients_per开发者_Python百科mission_assignments cpa ON cpa.permission_assignment_id = pa.id
                      WHERE p.label = 'Receive Test Updates' AND pa.person_id =
                          (SELECT person_id FROM permission_assignments pa WHERE pa.id = OLD.permission_assignment_id)) = 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1490959 n bits 864 index `unique_index` of table `test/clients_permission_assignments` trx id 0 959459751 lock_mode X locks rec but not gap
Record lock, heap no 202 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000cd8d; asc     ;; 1: len 4; hex 80000eb1; asc     ;; 2: len 6; hex 0000006b7d5c; asc    k}\;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1490959 n bits 864 index `unique_index` of table `test/clients_permission_assignments` trx id 0 959459751 lock mode S waiting
Record lock, heap no 202 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000cd8d; asc     ;; 1: len 4; hex 80000eb1; asc     ;; 2: len 6; hex 0000006b7d5c; asc    k}\;;

*** WE ROLL BACK TRANSACTION (1)

I didn't know at the time, but it seems like INSERT ... SELECT, CREATE ... SELECT locks all tables in the SELECT query. As you can see, there are a lot of tables with the joins and sub-queries. I thought the lock would be a shared (S) lock, but from the data above, it seems it holds an exclusive (X) lock; I don't understand why.

Maybe if someone can help me figure why there is an exclusive (X) lock on the CREATE ... SELECT query I can attempt to work through this deadlock. Or maybe I could get some further assistance with the deadlock.

Thank you.

---

I discovered that the exclusive lock was not from the CREATE ... SELECT query, but the query before it, which must be in the same transaction.

I left out an important detail: The CREATE ... SELECT query is an AFTER DELETE trigger on clients_permission_assignments. I found that the problem was a DELETE query is holding exclusive locks on some clients_permission_assignments records, and after each delete, the CREATE ... SELECT query attempts to get a shared lock on a record that already has an exclusive lock from the DELETE query. The CREATE ... SELECT query must wait for all the rows to be deleted and the exclusive locks to be released. However, while this process was happening, the user was not enjoying the wait, so they started the DELETE query over again, which caused the deadlock.

Thank you.