prisma: Prisma `update()`: `Timed out fetching a new connection from the connection pool.`
Bug description
I’m trying to do a massive update on a database with about 54.000 rows. Each line can be have a different value to be updated. So a tried frist using for of loop to wait each update finishing before execute the next, but that took a long time. So I made this request parallel with map an async await and this way is throwing an error: Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 13)
. I also tried running only 5 parallel updates and the error occurred again. More details about error:
(node:3088) UnhandledPromiseRejectionWarning: Error:
Invalid `db.tbl_general_approval.update()` invocation in
C:\Libbs\Workspace\approvals-panel-server\src\update_status\AtualizacaoSAPController.ts:402:64
399 let contador = 0;
400 for(let docs of slicedArray){
401 Promise.all(docs.map(async doc => {
→ 402 return await db.tbl_general_approval.update(
Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 13)
at Object.request (C:\Libbs\Workspace\approvals-panel-server\node_modules\@prisma\client\runtime\index.js:45405:15)
at async PrismaClient._request (C:\Libbs\Workspace\approvals-panel-server\node_modules\@prisma\client\runtime\index.js:46301:18)
at async C:\Libbs\Workspace\approvals-panel-server\src\update_status\AtualizacaoSAPController.ts:402:18
at async Promise.all (index 1)
(Use `node --trace-warnings ...` to show where the warning was created)
(node:3088) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unh
andled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:3088) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
How to reproduce
All parallel
Promise.all(controlledDocs.docs.map(async doc => {
return await db.tbl_general_approval.update({
where: {
itemKey: doc.itemKey
},
data: {
statusId: doc.statusId
}
});
}));
5 parallel updates
let slicedArray = [] , maxLength = 5;
for (var i = 0; i < controlledDocs.docs.length; i = i + maxLength) {
slicedArray.push(controlledDocs.docs.slice(i, i + maxLength));
}
for(let docs of slicedArray){
Promise.all(docs.map(async doc => {
return await db.tbl_general_approval.update({
where: {
itemKey: doc.itemKey
},
data: {
statusId: doc.statusId
}
});
}));
console.log('Terminou uma leva de update')
}
Expected behavior
I just expect that all parallel promises from await execute succesfully.
Prisma information
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
}
model sysdiagrams {
name String @db.NVarChar(128)
principal_id Int
diagram_id Int @id(map: "PK__sysdiagr__C2B05B6196C32836") @default(autoincrement())
version Int?
definition Bytes?
@@unique([principal_id, name], map: "UK_principal_name")
}
model tbl_status {
id Int @id(map: "PK__tbl_stat__3213E83FCA804D33") @default(autoincrement())
status String @db.VarChar(30)
tbl_general_approval tbl_general_approval[]
}
model tbl_general_approval {
id Int @default(autoincrement())
itemKey String @id(map: "PK__tbl_gene__A3039D594C9DECD2") @db.VarChar(50)
process String @db.VarChar(50)
sistem String @db.VarChar(50)
date DateTime @db.Date
link String? @db.VarChar(200)
statusId Int?
tbl_status tbl_status? @relation(fields: [statusId], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "FK__tbl_gener__statu__2EDAF651")
tbl_notification_control tbl_notification_control[]
tbl_user_approval tbl_user_approval[]
}
Environment & setup
- OS: Windows 11 Pro
- Database: Azure SQL 10.2 (Similar to SQL Server)
- Node.js version: v14.16.1
Prisma Version
"dependencies": {
"@prisma/client": "^3.14.0",
},
"devDependencies": {
"prisma": "^3.15.0",
"typescript": "^4.6.4"
}
About this issue
- Original URL
- State: open
- Created 2 years ago
- Reactions: 3
- Comments: 19 (11 by maintainers)
I just don’t want to experiment on that simple app as it’s old and working as-is. I haven’t had the issue come up elsewhere so if other users are reporting it it’s probably better to try to investigate there.
For my case I believe this is accurate. I only added the chunking and async/await to purposely slow things down, so it was surprising when removing the
await
made it more resilient. But I’m also speaking to something from a year ago, so details could be hazy. I’m happy to make changes on that repo if you want to see the output (assuming it still errors even…)For that particular case, the code is fairly simple and linked above (here)
I’m describing this a bit from memory, but it has 70k items to upsert, and adding 70k at once would result in a connection timeout almost always. Then chunking them (looks like 3k was a sweet spot based on the code) in a for loop, it would work, but only if I removed the
await
from the outer loop. Not sure why, but it was fairly sporadic.It runs in a GH action, and after making that change, it runs daily without issue (so the past ~10 months): https://github.com/KevinBatdorf/steam-api/actions
Another oddity is that it sometimes finishes in under 10 minutes, and other times takes 2+ hours. Didn’t bother investigating that though. Maybe the db server is slow at times.