Updating relations in random order is causing deadlock
### Node Version

v20.17.0

### NPM/Yarn/PNPM Version

NPM: 10.8.2

### Strapi Version

4.25.20

### Operating System

MacOs/Linux (Debian/Ubuntu)/Docker

### Database

MySQL: 8.0.26

### Javascript or Typescript

Javascript

### Reproduction URL

_No response_

### Bug Description

Hello,
I'm having a problem during mass update of our models in database. We are importing 5000 products in 20 batches wrapping batch in `Promise.all`. 

```
const batchSize = 20;
const productsToImport = [...];

for (let i = 0; i < productsToImport.length; i += batchSize) {
    const productsBatch = productsToImport.slice(i, i + batchSize);
    await Promise.all(productsBatch.map(product => {
        
        return strapi.entityService.update("api::ecommerce.product", product.id, { 
            data: product.data 
        });
    }));
}
```

Our product model has couple of relations like category (one-to-one), attributes (component). From time to time, randomly we are getting error like that:

```
Error: insert into products_category_links (category_id, category_order, product_id) values (23, 1, 169469) on duplicate key update category_order = values(category_order) - Deadlock found when trying to get lock; try restarting transaction
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/app/node_modules/mysql2/lib/connection.js:478:34)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:519:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
```

After checking status in database:
```
SHOW ENGINE INNODB STATUS;
```

and generating report from AI:
```
---
title: "InnoDB Monitor Output Analysis Report"
date: "2025-02-07T09:36:03"
---

## InnoDB Monitor Output Analysis

**Timestamp:** 2025-02-07 09:36:03  
**Main Thread ID:** 126513667864320

---

### Background Information

- **Background Thread Activity:**
  - `srv_master_thread loops:` 96 active, 0 shutdown, 5783 idle.
  - Log flushes and writes: 0.
  
- **Semaphore Statistics:**
  - **OS WAIT ARRAY INFO:**  
    - Reservation count: 248  
    - Signal count: 2248
  - Lock spin rounds and OS waits for RW-shared, RW-exclusive, and RW-sx locks are all 0.

---

### Latest Detected Deadlock

**Timestamp:** 2025-02-07 09:16:00

Two transactions are involved in this deadlock:

#### **Transaction (1) – trx id 10287426**

- **Activity:**  
  - Performing an insert into the `products_components` table using a multi-row insert.
- **Lock Details:**  
  - **Holds:**  
    - A record lock on the PRIMARY index of the table `cms.products_category_links` (space id 31316, page no 46).  
    - *Physical Record:* Contains a “supremum” value (hex `73757072656d756d`).
  - **Waiting For:**  
    - A record lock on the PRIMARY index of the table `cms.products_components` (space id 31317, page no 681) in "X insert intention waiting" mode.

---

#### **Transaction (2) – trx id 10287487**

- **Activity:**  
  - Inserting into the `products_category_links` table with an `INSERT ... ON DUPLICATE KEY UPDATE` statement.
- **Lock Details:**  
  - **Holds:**  
    - A record lock on the PRIMARY index of the table `cms.products_components` (space id 31317, page no 681).
  - **Waiting For:**  
    - A record lock on the PRIMARY index of the table `cms.products_category_links` (space id 31316, page no 46) in "X insert intention waiting" mode.
- **Outcome:**  
  - Transaction (2) is chosen as the deadlock victim and is rolled back.

---

### Investigation & Analysis

- **Circular Lock Dependency:**  
  Each transaction holds a lock on one table and is waiting to acquire a lock on the other:
  - Transaction (1) holds a lock on `products_category_links` and waits for `products_components`.
  - Transaction (2) holds a lock on `products_components` and waits for `products_category_links`.

- **Lock Type and Supremum Records:**  
  Both transactions use exclusive ("X") record locks on the PRIMARY indexes. The “supremum” record (used internally by InnoDB to mark the upper bound) is involved in the conflict.

- **Concurrency and Order:**  
  Although the operations are “independent” at the application level, the reverse order of lock acquisition between these two tables is causing the deadlock.

---

### Recommendations to Avoid the Deadlock

1. **Ensure Consistent Locking Order:**  
   Modify the application logic so that all transactions affecting both tables acquire locks in the same order. For example, always update or insert into `products_category_links` first and then `products_components` (or vice versa). This prevents circular waiting.

2. **Serialize Related Operations:**  
   If both operations are part of the same business process, consider executing them sequentially within one transaction rather than concurrently (avoid using `Promise.all` for these operations).

3. **Review Transaction and Connection Usage:**  
   - Ensure that each query uses its intended transaction object.
   - Avoid mixing transaction contexts by not using `Promise.all` with methods that require a single transaction context, as warned by packages like [@untype/pg](&#8203;:contentReference[oaicite:0]{index=0}).

4. **Adjust Transaction Isolation Levels:**  
   Depending on your database schema and workload, tweaking the isolation level or applying specific lock hints might reduce contention.

5. **Utilize Database Diagnostic Tools:**  
   - For MySQL/InnoDB, execute `SHOW ENGINE INNODB STATUS;` immediately after a deadlock to gather detailed information.
   - Monitor the database logs for deadlock reports, which can help in pinpointing the problematic queries.

---

### Conclusion

The deadlock is a result of two transactions acquiring locks in a conflicting order:
- **Transaction (1)** holds a lock on `products_category_links` and waits for `products_components`.
- **Transaction (2)** holds a lock on `products_components` and waits for `products_category_links`.

To resolve this, you can enforce a consistent locking order, avoid concurrent transaction methods (such as using `Promise.all`), and adjust transaction or lock settings as necessary. Implementing these changes should help prevent similar deadlocks in the future.
```

Conclusion is that Strapi is updating relations in async, unordered way [Which may cause deadlock](https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks-handling.html). Quick fix was just to give up from `Promise.all` and replace update with synchronous updates. But it is just quick fix not resolving main issue. I can imagine that when our app will get more traffic and users will be updating other model like order this issue might be causing deadlocks.

I'm using Strapi v4 I did not tested this with Strapi v5. Since v5 introduce breaking changes I don't know when I will be able to migrate to v5. I would be nice to fix this issue in v4 also.


### Steps to Reproduce

1. Create content type product with 20 fields and 10 relations, one-to-one, one-to-many and components
2. Create a script to create 5000 products in batches of 20 with Promise.all
3. Create a script to update 5000 products in batches of 20 with Promise.all
4. Create products then update them and expect to have a deadlock


### Expected Behavior

Mass updating unrelated data should not cause deadlock.

### Logs

```shell
Error:

Error: insert into products_category_links (category_id, category_order, product_id) values (23, 1, 169469) on duplicate key update category_order = values(category_order) - Deadlock found when trying to get lock; try restarting transaction
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/app/node_modules/mysql2/lib/connection.js:478:34)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:519:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

Inno DB status:

====================================== 2025-02-07 09:36:03 126513667864320 INNODB MONITOR OUTPUT =====================================

Per second averages calculated from the last 25 seconds

----------------- BACKGROUND THREAD -----------------
srv_master_thread loops: 96 srv_active, 0 srv_shutdown, 5783 srv_idle  
srv_master_thread log flush and writes: 0

---------- SEMAPHORES ----------
OS WAIT ARRAY INFO: reservation count 248  
OS WAIT ARRAY INFO: signal count 2248  
RW-shared spins 0, rounds 0, OS waits 0  
RW-excl spins 0, rounds 0, OS waits 0  
RW-sx spins 0, rounds 0, OS waits 0  
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

------------------------ LATEST DETECTED DEADLOCK ------------------------
2025-02-07 09:16:00 126512858662656

*** (1) TRANSACTION:
TRANSACTION 10287426, ACTIVE 0 sec
inserting mysql
tables in use 1, locked 1
LOCK WAIT 26 lock struct(s), heap size 3488, 97 row lock(s), undo log entries 50
MySQL thread id 552, OS thread handle 126513657673472, query id 45097
172.22.0.1 cms update insert into `products_components` 
  (`component_id`, `component_type`, `entity_id`, `field`, `order`) values 
  (14236, 'general.key-value', 175863, 'specification', 1),
  (14237, 'general.key-value', 175863, 'specification', 2),
  (14239, 'general.key-value', 175863, 'specification', 3),
  (14241, 'general.key-value', 175863, 'specification', 4),
  (14243, 'general.key-value', 175863, 'specification', 5),
  (14245, 'general.key-value', 175863, 'specification', 6),
  (14247, 'general.key-value', 175863, 'specification', 7),
  (14249, 'general.key-value', 175863, 'specification', 8),
  (14251, 'general.key-value', 175863, 'specification', 9),
  (14252, 'general.key-value', 175863, 'specification', 10),
  (14254, 'general.key-value', 175863, 'specification', 11),
  (14255, 'general.key-value', 175863, 'specification', 12),
  (14257, 'general.key-value', 175863, 'specification', 13),
  (14259, 'general.key-value', 175863, 'specification', 14),
  (14262, 

*** (1) HOLDS THE LOCK(S):
  RECORD LOCKS space id 31316 page no 46 n bits 464 index PRIMARY of table `cms`.`products_category_links`
  trx id 10287426 lock_mode X  
  Record lock, heap no 1  
  PHYSICAL RECORD: n_fields 1; compact format; info bits 0  
    0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 31317 page no 681 n bits 184 index PRIMARY of table `cms`.`products_components`
  trx id 10287426 lock_mode X insert intention waiting  
  Record lock, heap no 1  
  PHYSICAL RECORD: n_fields 1; compact format; info bits 0  
    0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 10287487, ACTIVE 0 sec
inserting mysql
tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3488, 92 row lock(s), undo log entries 45
MySQL thread id 554, OS thread handle 126513670883072, query id 45108
172.22.0.1 cms update insert into `products_category_links` 
  (`category_id`, `category_order`, `product_id`) values (20, 1, 175487)
  on duplicate key update `category_order` = values(`category_order`)

*** (2) HOLDS THE LOCK(S):
  RECORD LOCKS space id 31317 page no 681 n bits 184 index PRIMARY of table `cms`.`products_components`
  trx id 10287487 lock_mode X  
  Record lock, heap no 1  
  PHYSICAL RECORD: n_fields 1; compact format; info bits 0  
    0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 31316 page no 46 n bits 464 index PRIMARY of table `cms`.`products_category_links`
  trx id 10287487 lock_mode X insert intention waiting  
  Record lock, heap no 1  
  PHYSICAL RECORD: n_fields 1; compact format; info bits 0  
    0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

------------ TRANSACTIONS ------------
Trx id counter 10287592
Purge done for trx's n:o < 10287590
undo n:o < 0 state: running but idle
History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 407988581964152, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581963344, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581962536, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581960920, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581961728, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581967384, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581960112, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 407988581959304, not started
  0 lock struct(s), heap size 1128, 0 row lock(s)

-------- FILE I/O --------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 2

5530 OS file reads, 14752 OS file writes, 7049 OS fsyncs  
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX -------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations: insert 0, delete mark 0, delete 0
discarded operations: insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 57 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 45 buffer(s)
Hash table size 34679, node heap has 93 buffer(s)
Hash table size 34679, node heap has 49 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
Hash table size 34679, node heap has 8 buffer(s)
5124.60 hash searches/s, 1660.25 non-hash searches/s

--- LOG ---
Log sequence number 13337392736  
Log buffer assigned up to 13337392736  
Log buffer completed up to 13337392736  
Log written up to 13337392736  
Log flushed up to 13337392736  
Added dirty pages up to 13337392736  
Pages flushed up to 13337392736  
Last checkpoint at 13337392736  
6480 log i/o's done, 0.00 log i/o's/second

---------------------- BUFFER POOL AND MEMORY ----------------------
Total large memory allocated 137039872  
Dictionary memory allocated 1756525  
Buffer pool size 8191  
Free buffers 2309  
Database pages 5618  
Old database pages 2053  
Modified db pages 0  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 3525, not young 934  
0.00 youngs/s, 0.00 non-youngs/s  
Pages read 5373, created 251, written 6399  
0.00 reads/s, 0.00 creates/s, 0.00 writes/s  
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  
LRU len: 5618, unzip_LRU len: 0  
I/O sum[6]:cur[0], unzip sum[0]:cur[0]

-------------- ROW OPERATIONS --------------
0 queries inside InnoDB, 0 queries in queue  
0 read views open inside InnoDB  
Process ID=1, Main thread ID=126512848176896, state=sleeping

Number of rows inserted 1800, updated 3808, deleted 661, read 19127067  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8484.66 reads/s

Number of system rows inserted 263, updated 419, deleted 193, read 1229858  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.48 reads/s

---------------------------- END OF INNODB MONITOR OUTPUT ============================


Report from AI about InnoDB Status:

---
title: "InnoDB Monitor Output Analysis Report"
date: "2025-02-07T09:36:03"
---

## InnoDB Monitor Output Analysis

**Timestamp:** 2025-02-07 09:36:03  
**Main Thread ID:** 126513667864320

---

### Background Information

- **Background Thread Activity:**
  - `srv_master_thread loops:` 96 active, 0 shutdown, 5783 idle.
  - Log flushes and writes: 0.
  
- **Semaphore Statistics:**
  - **OS WAIT ARRAY INFO:**  
    - Reservation count: 248  
    - Signal count: 2248
  - Lock spin rounds and OS waits for RW-shared, RW-exclusive, and RW-sx locks are all 0.

---

### Latest Detected Deadlock

**Timestamp:** 2025-02-07 09:16:00

Two transactions are involved in this deadlock:

#### **Transaction (1) – trx id 10287426**

- **Activity:**  
  - Performing an insert into the `products_components` table using a multi-row insert.
- **Lock Details:**  
  - **Holds:**  
    - A record lock on the PRIMARY index of the table `cms.products_category_links` (space id 31316, page no 46).  
    - *Physical Record:* Contains a “supremum” value (hex `73757072656d756d`).
  - **Waiting For:**  
    - A record lock on the PRIMARY index of the table `cms.products_components` (space id 31317, page no 681) in "X insert intention waiting" mode.

---

#### **Transaction (2) – trx id 10287487**

- **Activity:**  
  - Inserting into the `products_category_links` table with an `INSERT ... ON DUPLICATE KEY UPDATE` statement.
- **Lock Details:**  
  - **Holds:**  
    - A record lock on the PRIMARY index of the table `cms.products_components` (space id 31317, page no 681).
  - **Waiting For:**  
    - A record lock on the PRIMARY index of the table `cms.products_category_links` (space id 31316, page no 46) in "X insert intention waiting" mode.
- **Outcome:**  
  - Transaction (2) is chosen as the deadlock victim and is rolled back.

---

### Investigation & Analysis

- **Circular Lock Dependency:**  
  Each transaction holds a lock on one table and is waiting to acquire a lock on the other:
  - Transaction (1) holds a lock on `products_category_links` and waits for `products_components`.
  - Transaction (2) holds a lock on `products_components` and waits for `products_category_links`.

- **Lock Type and Supremum Records:**  
  Both transactions use exclusive ("X") record locks on the PRIMARY indexes. The “supremum” record (used internally by InnoDB to mark the upper bound) is involved in the conflict.

- **Concurrency and Order:**  
  Although the operations are “independent” at the application level, the reverse order of lock acquisition between these two tables is causing the deadlock.

---

### Recommendations to Avoid the Deadlock

1. **Ensure Consistent Locking Order:**  
   Modify the application logic so that all transactions affecting both tables acquire locks in the same order. For example, always update or insert into `products_category_links` first and then `products_components` (or vice versa). This prevents circular waiting.

2. **Serialize Related Operations:**  
   If both operations are part of the same business process, consider executing them sequentially within one transaction rather than concurrently (avoid using `Promise.all` for these operations).

3. **Review Transaction and Connection Usage:**  
   - Ensure that each query uses its intended transaction object.
   - Avoid mixing transaction contexts by not using `Promise.all` with methods that require a single transaction context, as warned by packages like [@untype/pg](&#8203;:contentReference[oaicite:0]{index=0}).

4. **Adjust Transaction Isolation Levels:**  
   Depending on your database schema and workload, tweaking the isolation level or applying specific lock hints might reduce contention.

5. **Utilize Database Diagnostic Tools:**  
   - For MySQL/InnoDB, execute `SHOW ENGINE INNODB STATUS;` immediately after a deadlock to gather detailed information.
   - Monitor the database logs for deadlock reports, which can help in pinpointing the problematic queries.

---

### Conclusion

The deadlock is a result of two transactions acquiring locks in a conflicting order:
- **Transaction (1)** holds a lock on `products_category_links` and waits for `products_components`.
- **Transaction (2)** holds a lock on `products_components` and waits for `products_category_links`.

To resolve this, you can enforce a consistent locking order, avoid concurrent transaction methods (such as using `Promise.all`), and adjust transaction or lock settings as necessary. Implementing these changes should help prevent similar deadlocks in the future.
```

### Code Snippets

```
const batchSize = 20;
const productsToImport = [...];

for (let i = 0; i < productsToImport.length; i += batchSize) {
    const productsBatch = productsToImport.slice(i, i + batchSize);
    await Promise.all(productsBatch.map(product => {
        
        return strapi.entityService.update("api::ecommerce.product", product.id, { 
            data: product.data 
        });
    }));
}
```

### Media

_No response_

### Additional information

_No response_

### Confirmation Checklist

- [x] I have checked the existing [issues](https://github.com/strapi/strapi/issues) for duplicates.
- [x] I agree to follow this project's [Code of Conduct](https://github.com/strapi/strapi/blob/develop/CODE_OF_CONDUCT.md).