public inbox for [email protected]help / color / mirror / Atom feed
Update command causing lock in DB. 3+ messages / 2 participants [nested] [flat]
* Update command causing lock in DB. @ 2025-05-07 11:15 Gambhir Singh <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Gambhir Singh @ 2025-05-07 11:15 UTC (permalink / raw) To: [email protected] Hi, Application team was executing UPDATE statement in DB through Abinitio graph. When they trigger a job, a session is spawned in DB, in parallel another session is also spawned and executed the same UPDATE statement. When I checked the locks in DB, I found that both the sessions are updating the same record. My concern is how UPDATE causes locking in DB. Here is how MVCC works. If one session is updating a record, it should release the lock once it updated the row and other one should be able to acquire the row lock. Maybe I am wrong, please suggest how to handle this situation. -- Thanks & Regards Gambhir Singh ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update command causing lock in DB. @ 2025-05-07 13:31 Ron Johnson <[email protected]> parent: Gambhir Singh <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2025-05-07 13:31 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Wed, May 7, 2025 at 7:16 AM Gambhir Singh <[email protected]> wrote: > Hi, > > Application team was executing UPDATE statement in DB through Abinitio > graph. When they trigger a job, a session is spawned in DB, in parallel > another session is also spawned and executed the same UPDATE statement. > > When I checked the locks in DB, I found that both the sessions are > updating the same record. My concern is how UPDATE causes locking in DB. > > Here is how MVCC works. If one session is updating a record, it should > release the lock once it updated the row and other one should be able to > acquire the row lock. Maybe I am wrong, please suggest how to handle this > situation. > Do the applications use implicit autocommit, or do they use explicit transaction blocks? -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update command causing lock in DB. @ 2025-05-08 09:32 Gambhir Singh <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Gambhir Singh @ 2025-05-08 09:32 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> They are using explicit transaction block and also got he root cause. They to provide the commit explicitly. Thanks & Regards Gambhir Singh On Wed, 7 May 2025 at 19:01, Ron Johnson <[email protected]> wrote: > On Wed, May 7, 2025 at 7:16 AM Gambhir Singh <[email protected]> > wrote: > >> Hi, >> >> Application team was executing UPDATE statement in DB through Abinitio >> graph. When they trigger a job, a session is spawned in DB, in parallel >> another session is also spawned and executed the same UPDATE statement. >> >> When I checked the locks in DB, I found that both the sessions are >> updating the same record. My concern is how UPDATE causes locking in DB. >> >> Here is how MVCC works. If one session is updating a record, it should >> release the lock once it updated the row and other one should be able to >> acquire the row lock. Maybe I am wrong, please suggest how to handle this >> situation. >> > > Do the applications use implicit autocommit, or do they use explicit > transaction blocks? > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-05-08 09:32 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-05-07 11:15 Update command causing lock in DB. Gambhir Singh <[email protected]> 2025-05-07 13:31 ` Ron Johnson <[email protected]> 2025-05-08 09:32 ` Gambhir Singh <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox