On Thu, 19 Sept 2024 at 02:01, veem v <veema0...@gmail.com> wrote: > > On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 9/17/24 12:34, veem v wrote: >> > >> >> It does if autocommit is set in the client, that is common to other >> databases also: >> >> https://dev.mysql.com/doc/refman/8.4/en/commit.html >> >> >> https://docs.oracle.com/en/database/oracle/developer-tools-for-vscode/getting-started/disabling-and-enabling-auto-commit.html >> >> >> https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver16 >> >> You probably need to take a closer look at the client/driver you are >> using and the code that interacting with it. >> >> In fact I would say you need to review the entire data transfer process >> to see if there are performance gains that can be obtained without >> adding an entirely new async component. >> >> >> > You were spot on. When we turned off the "auto commit" we started seeing > less number of commits as per the number of batches. > > However we also started seeing deadlock issues. We have foreign key > relationships between the tables and during the batch we do insert into the > parent first and then to the child , but this does happen from multiple > sessions for different batches. So why do we see below error, as we > ensure in each batch we first insert into parent and then into the child > tables? > > caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected > Detail: Process 10443 waits for ShareLock on transaction 220972157; > blocked by process 10454. > Process 10454 waits for ShareLock on transaction 220972155; blocked by > process 10443. > Hint: See server log for query details. > Where: while inserting index tuple (88736,28) in relation > "TAB1_p2024_08_29" >
As we are able to get hold of one session, we see "insert into <parent partition table>" was blocked by "insert into <child partition table>". And the "insert into <child partition table> " was experiencing a "client read" wait event. Still unable to understand why it's happening and how to fix it?
