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)

Most upvoted comments

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.

The change in behavior of it being “more fickle” via await again is a bit unexpected. The await should make it slower to run, but “fickle” seems to refer to getting an error message - and slower run should do the opposite. Is my understanding correct @KevinBatdorf?

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.