Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uCxca-004Zhg-GZ for pgsql-admin@arkaria.postgresql.org; Thu, 08 May 2025 09:32:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uCxcY-0029JG-2C for pgsql-admin@arkaria.postgresql.org; Thu, 08 May 2025 09:32:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uCxcX-0029Ia-Ms for pgsql-admin@lists.postgresql.org; Thu, 08 May 2025 09:32:49 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uCxcV-000k3m-0Y for pgsql-admin@lists.postgresql.org; Thu, 08 May 2025 09:32:48 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-5fbfdf7d353so970953a12.0 for ; Thu, 08 May 2025 02:32:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1746696766; x=1747301566; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=k2vFjPS94NV06qoynbhahoZ0ymjdf0Y5tila4HNKiNw=; b=eoNYVv30+2CYXO2BYqwQrygzTgSP+7SyNp+D/zIORjXwR7p0sp4K0gctl6M9j+BSiZ qGrmwFaRmcE6JZhba74gwmP+2FrbqVSMDZgQzsyv39gTjpazQRoAbPi8sf9dA05rLrwY fd2GW2WrkC4yKCc20Wo8g9Rk3XSrXoJdmMCHB7xZ4wlyRzJ66MgxR7hWbWSqwCHo7Oi1 Cb/EkZHLhiGF+tV//EMcnE/oNZrPGwL1sxXAz2q/x2pOU0DkzkUuvKTq+A8twiuFUl+t d4nncUyF770+nC9oYqNSRdfFjD7XRy3bHm3epzFBcXlkiNKsH5BE9eX69z5AZ5Trl8zq EDXA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1746696766; x=1747301566; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=k2vFjPS94NV06qoynbhahoZ0ymjdf0Y5tila4HNKiNw=; b=l3Qe0XHgj9wQuDiqJdV51oljAU4Qh0LqLnvbOthrfC2/LLuB+dgJRatyz8LZ7MDh3V bpwbVvqA+YMrkQU3sFnBJa5zH1bO18B3cPjGKe+AiS7MpMISqqcdtvAJL3p7TKQ4MSPn IQYNXKDUZhZxQVm0MegFMGObC7TKEI3xyKmoWJ6akCIlQtnEbQOBqfMcwSfblgLidFiN g4fTFFdTxEc9jVfjbtqhesqlWqu4fqmcs63No/il/jvfI47MX92hpbnwADahusW1tO88 K0nuQbciOJFqm8zjkhTEM13YX5DX1q5jHEi/2awm1pvZsKkECFojzR6waB7Y/yfGkin+ TVag== X-Gm-Message-State: AOJu0Yz1fwH1HhWNX2PoGAzgdyT8Ia9jnoY6JQRwC4CqpLmRq6OGmlbE 1h8DICYHMqge1wGfkjhtsaYH5+xt29EJFKIbZoW0XZIY9TpyF8nwrGAqXVmS67/OAswCoNngZUs ACJCNQL4Q6ispj/ellAE7UJxtuV8= X-Gm-Gg: ASbGnct78NLNnKpEmFnAK1SujLJ9MdvQssrH4D0zfXV6dQaWmrL1eBq8TUauF9y+4sc HA3nR4To4TjcoI1VDk5xlZARGWjeosjSJd0CSdIQWdqojmZ+covIqZ+zGtLrfKZXpolONSQXC2V zWqJZuG8UMRdTDmQA9fiHRFbprAjPICxVMHMzad5fG425ZAz3VeQEOSs0= X-Google-Smtp-Source: AGHT+IFZpWSQ4/aaO6imHLVarJoeI1L+kvAwpSp8mJVw0uhhTGne9t6Rc6XjQQeVa4LjDNHI120Zi5tmzsa034GQmdA= X-Received: by 2002:a05:6402:3506:b0:5fc:8d7f:869a with SMTP id 4fb4d7f45d1cf-5fc8d7f8a58mr819150a12.33.1746696766099; Thu, 08 May 2025 02:32:46 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Gambhir Singh Date: Thu, 8 May 2025 15:02:35 +0530 X-Gm-Features: ATxdqUEcWYVkoeHMVm5iLeUIOpWEXCUIU0Qjtp0-dAiE2HM9OWA2lLbQ9h8_Bs4 Message-ID: Subject: Re: Update command causing lock in DB. To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000046162806349c8845" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000046162806349c8845 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrote: > On Wed, May 7, 2025 at 7:16=E2=80=AFAM Gambhir Singh > 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 thi= s >> situation. >> > > Do the applications use implicit autocommit, or do they use explicit > transaction blocks? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --00000000000046162806349c8845 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 <ronljohnsonjr@gmail.com> wrote:
On Wed, May 7, 2025 at 7:16=E2=80=AFAM Gambhi= r Singh <= gambhir.singh05@gmail.com> wrote:
Hi,<= br clear=3D"all">

Application team was executin= g 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 lock= s in DB, I found that both the sessions are updating the same record. My co= ncern 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 updat= ed the row and other one should be able to acquire the row lock. Maybe I am= wrong, please suggest how to handle this situation.
=C2=A0
Do the applications use implicit autoco= mmit, or do they use explicit transaction blocks?

--
Death to <= Redacted>, and butter sauce.
Don't boil me, I'm still alive.=
<Redacted> lobster!
--00000000000046162806349c8845--