starred/hoppscotch

Fork 0

mirror of https://github.com/hoppscotch/hoppscotch.git synced 2026-04-25 16:55:59 +03:00

[GH-ISSUE #5666] [bug]: 100X performance improvement after fixing Database deadlocks in collection/request operations under concurrent load. #2196

New issue

Closed

opened 2026-03-16 23:32:33 +03:00 by kerem · 3 comments

kerem commented

2026-03-16 23:32:33 +03:00

Owner

Originally created by @shaezard on GitHub (Dec 5, 2025).
Original GitHub issue: https://github.com/hoppscotch/hoppscotch/issues/5666

Originally assigned to: @shaezard on GitHub.

Is there an existing issue for this?

I have searched existing issues and this bug hasn't been reported yet

Platform

Desktop App

Browser

Chrome

Operating System

macOS

Bug Description

What happened?

When multiple users perform concurrent operations on collections and requests (create, delete, reorder), the backend encounters PostgreSQL deadlock errors causing operations to fail with INTERNAL_SERVER_ERROR.

The deadlocks occur because multiple transactions attempt to update orderIndex fields simultaneously, creating circular wait conditions.

Affected operations:

createRootCollection / createRESTRootUserCollection
deleteCollection / deleteUserCollection
createRequestInCollection / createRESTUserRequest
Collection/request reordering

Steps to reproduce

Deploy Hoppscotch backend with PostgreSQL
Simulate 50+ concurrent users performing collection CRUD operations
Observe deadlock errors in server logs and failed operations

Load Test Results (Before Fix)

Team Collections:

Metric	Value
Total Requests	756
Successful (OK)	493 (65.2%)
Failed (KO)	263 (34.8%)
Deadlock Errors	20 (7.6%)
Max Response Time	13,277ms

User Collections:

Metric	Value
Total Requests	759
Successful (OK)	499 (65.7%)
Failed (KO)	260 (34.3%)
Deadlock Errors	21 (8.08%)
Max Response Time	17,413ms

Errors observed

Team Collections:

deadlock detected - 20 occurrences (7.6%)
team_coll/reordering_failed - 91 (34.6%)
team_coll/creation_failed - 129 (49%)

User Collections:

deadlock detected - 21 occurrences (8.08%)
user_coll/reordering_failed - 86 (33%)
user_request/creation_failed - 88 (33.85%)
user_collection/creation_failed - 36 (13.85%)

Root Cause

The deadlocks are caused by concurrent transactions acquiring exclusive locks on rows in different orders when updating orderIndex fields. This creates circular wait conditions that PostgreSQL detects as deadlocks.

Proposed Fix

Implement row-level locking using PostgreSQL's SELECT ... FOR UPDATE to acquire explicit locks on affected rows before performing updates. This ensures transactions acquire locks in a consistent order, preventing deadlocks.

New locking methods added to PrismaService:

lockTeamCollectionByTeamAndParent()
lockTeamRequestByCollections()
lockUserCollectionByParent()
lockUserRequestByCollections()

Load Test Results (After Fix)

Team Collections:

Metric	Before	After	Improvement
Successful Requests	493	25,744	52x
Failed Requests	263	154	-98%
Deadlock Errors	20	0	Eliminated
Max Response Time	13,277ms	375ms	97% faster

User Collections:

Metric	Before	After	Improvement
Successful Requests	499	21,635	43x
Failed Requests	260	0	100% eliminated
Deadlock Errors	21	0	Eliminated
Max Response Time	17,413ms	1,802ms	90% faster

Files Changed

packages/hoppscotch-backend/src/prisma/prisma.service.ts
packages/hoppscotch-backend/src/team-collection/team-collection.service.ts
packages/hoppscotch-backend/src/user-collection/user-collection.service.ts
packages/hoppscotch-backend/src/team-request/team-request.service.ts
packages/hoppscotch-backend/src/user-request/user-request.service.ts

Additional context

Load tests performed using Gatling with 50 concurrent virtual users
Each user performs: authenticate → create collection → create request → get collections → delete collection
Tests ran for approximately 1 minute each

Deployment Type

Hoppscotch Cloud

Version

No response

Originally created by @shaezard on GitHub (Dec 5, 2025). Original GitHub issue: https://github.com/hoppscotch/hoppscotch/issues/5666 Originally assigned to: @shaezard on GitHub. ### Is there an existing issue for this? - [x] I have searched existing issues and this bug hasn't been reported yet ### Platform Desktop App ### Browser Chrome ### Operating System macOS ### Bug Description ## What happened? When multiple users perform concurrent operations on collections and requests (create, delete, reorder), the backend encounters PostgreSQL deadlock errors causing operations to fail with `INTERNAL_SERVER_ERROR`. The deadlocks occur because multiple transactions attempt to update `orderIndex` fields simultaneously, creating circular wait conditions. **Affected operations:** - `createRootCollection` / `createRESTRootUserCollection` - `deleteCollection` / `deleteUserCollection` - `createRequestInCollection` / `createRESTUserRequest` - Collection/request reordering ## Steps to reproduce 1. Deploy Hoppscotch backend with PostgreSQL 2. Simulate 50+ concurrent users performing collection CRUD operations 3. Observe deadlock errors in server logs and failed operations ## Load Test Results (Before Fix) **Team Collections:** | Metric | Value | |--------|-------| | Total Requests | 756 | | Successful (OK) | 493 (65.2%) | | Failed (KO) | 263 (34.8%) | | Deadlock Errors | 20 (7.6%) | | Max Response Time | 13,277ms | **User Collections:** | Metric | Value | |--------|-------| | Total Requests | 759 | | Successful (OK) | 499 (65.7%) | | Failed (KO) | 260 (34.3%) | | Deadlock Errors | 21 (8.08%) | | Max Response Time | 17,413ms | ## Errors observed **Team Collections:** - `deadlock detected` - 20 occurrences (7.6%) - `team_coll/reordering_failed` - 91 (34.6%) - `team_coll/creation_failed` - 129 (49%) **User Collections:** - `deadlock detected` - 21 occurrences (8.08%) - `user_coll/reordering_failed` - 86 (33%) - `user_request/creation_failed` - 88 (33.85%) - `user_collection/creation_failed` - 36 (13.85%) ## Root Cause The deadlocks are caused by concurrent transactions acquiring exclusive locks on rows in different orders when updating `orderIndex` fields. This creates circular wait conditions that PostgreSQL detects as deadlocks. ## Proposed Fix Implement **row-level locking** using PostgreSQL's `SELECT ... FOR UPDATE` to acquire explicit locks on affected rows before performing updates. This ensures transactions acquire locks in a consistent order, preventing deadlocks. **New locking methods added to `PrismaService`:** - `lockTeamCollectionByTeamAndParent()` - `lockTeamRequestByCollections()` - `lockUserCollectionByParent()` - `lockUserRequestByCollections()` ## Load Test Results (After Fix) **Team Collections:** | Metric | Before | After | Improvement | |--------|--------|-------|-------------| | Successful Requests | 493 | 25,744 | **52x** | | Failed Requests | 263 | 154 | **-98%** | | Deadlock Errors | 20 | 0 | **Eliminated** | | Max Response Time | 13,277ms | 375ms | **97% faster** | **User Collections:** | Metric | Before | After | Improvement | |--------|--------|-------|-------------| | Successful Requests | 499 | 21,635 | **43x** | | Failed Requests | 260 | 0 | **100% eliminated** | | Deadlock Errors | 21 | 0 | **Eliminated** | | Max Response Time | 17,413ms | 1,802ms | **90% faster** | ## Files Changed - `packages/hoppscotch-backend/src/prisma/prisma.service.ts` - `packages/hoppscotch-backend/src/team-collection/team-collection.service.ts` - `packages/hoppscotch-backend/src/user-collection/user-collection.service.ts` - `packages/hoppscotch-backend/src/team-request/team-request.service.ts` - `packages/hoppscotch-backend/src/user-request/user-request.service.ts` ## Additional context - Load tests performed using Gatling with 50 concurrent virtual users - Each user performs: authenticate → create collection → create request → get collections → delete collection - Tests ran for approximately 1 minute each ### Deployment Type Hoppscotch Cloud ### Version _No response_

kerem

2026-03-16 23:32:33 +03:00

closed this issue
added the
bug
label

kerem commented

2026-03-16 23:32:49 +03:00

Author

Owner

@mirarifhasan commented on GitHub (Jan 16, 2026):

@shaezard Thank you for your work and effort — your PR looks promising.
Would you be interested in contributing to Hoppscotch? If so, feel free to open a PR in our repository. We’d be happy to review it.

@mirarifhasan commented on GitHub (Jan 16, 2026): @shaezard Thank you for your work and effort — your PR looks promising. Would you be interested in contributing to Hoppscotch? If so, feel free to open a PR in our repository. We’d be happy to review it.

kerem commented

2026-03-16 23:32:54 +03:00

Author

Owner

@shaezard commented on GitHub (Jan 19, 2026):

Thanks @mirarifhasan . I have opened the PR on the repo.
https://github.com/hoppscotch/hoppscotch/pull/5647/files

This PR has three issues fixed
https://github.com/hoppscotch/hoppscotch/issues/5646 ([bug]: Cross-user/Cross-team orderIndex corruption due to missing teamID/userUid filter in updateMany queries)

https://github.com/hoppscotch/hoppscotch/issues/5654 ([Enhancement] Add cascade delete for collections to improve deletion performance and simplify code)

https://github.com/hoppscotch/hoppscotch/issues/5666 ([bug]: 100X performance improvement after fixing Database deadlocks in collection/request operations under concurrent load.)

@shaezard commented on GitHub (Jan 19, 2026): Thanks @mirarifhasan . I have opened the PR on the repo. https://github.com/hoppscotch/hoppscotch/pull/5647/files This PR has three issues fixed https://github.com/hoppscotch/hoppscotch/issues/5646 ([bug]: Cross-user/Cross-team orderIndex corruption due to missing teamID/userUid filter in updateMany queries) https://github.com/hoppscotch/hoppscotch/issues/5654 ([Enhancement] Add cascade delete for collections to improve deletion performance and simplify code) https://github.com/hoppscotch/hoppscotch/issues/5666 ([bug]: 100X performance improvement after fixing Database deadlocks in collection/request operations under concurrent load.)

kerem commented

2026-03-16 23:32:59 +03:00

Author

Owner

@jamesgeorge007 commented on GitHub (Jan 28, 2026):

This is now addressed in the v2026.1.0 release.

@jamesgeorge007 commented on GitHub (Jan 28, 2026): This is now addressed in the [v2026.1.0](https://github.com/hoppscotch/hoppscotch/releases/tag/2026.1.0) release.

kerem referenced this issue

2026-03-17 01:18:13 +03:00

[PR #2295] [CLOSED] Update Traditional Chinese translation #3714