Thread: INSERTS waiting with wait_event is "transactionid"

INSERTS waiting with wait_event is "transactionid"

From
Nagaraj Raj
Date:
Hi,

We are trying to load data around 1Bil records into one table with INSERT statements (not able to use COPY command) and they are been waiting for a lock and the wait_event is "transactionid", I didn't find any information in the documents. Queries have been waiting for hours.

Table DDL's
CREATE TABLE test_load
(
    billg_acct_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL,
    accs_mthd_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL,
    soc character varying(10) COLLATE pg_catalog."default" NOT NULL,
    soc_desc character varying(100) COLLATE pg_catalog."default",
    service_type_cd character varying(10) COLLATE pg_catalog."default",
    soc_start_dt date,
    soc_end_dt date,
    product_eff_dt date,
    product_exp_dt date,
    curr_ind character varying(1) COLLATE pg_catalog."default",
    load_dttm timestamp without time zone NOT NULL,
    updt_dttm timestamp without time zone,
    md5_chk_sum character varying(100) COLLATE pg_catalog."default",
    deld_from_src_ind character(1) COLLATE pg_catalog."default",
    orphan_ind character(1) COLLATE pg_catalog."default",
    CONSTRAINT test_load_pk PRIMARY KEY (billg_acct_cid_hash, accs_mthd_cid_hash, soc)
);

query results from pg_locks ;

 SELECT COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,
    now() - blockeda.query_start AS waiting_duration,
    blockeda.pid AS blocked_pid,
    left(blockeda.query,7) AS blocked_query,
    blockedl.mode AS blocked_mode,
    blockinga.pid AS blocking_pid,
    left(blockinga.query,7) AS blocking_query,
    blockingl.mode AS blocking_mode
   FROM pg_locks blockedl
     JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
     JOIN pg_locks blockingl ON (blockingl.transactionid = blockedl.transactionid OR blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype) AND blockedl.pid <> blockingl.pid
     JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid
  WHERE NOT blockedl.granted 
  order by blockeda.query_start

"transactionid" "18:20:06.068154" 681216 "INSERT " "ShareLock" 679840 "INSERT " "ExclusiveLock"
"transactionid" "18:19:05.504781" 679688 "INSERT " "ShareLock" 679856 "INSERT " "ExclusiveLock"
"transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679612 "INSERT " "ShareLock"
"transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679580 "INSERT " "ShareLock"
"transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 681108 "INSERT " "ExclusiveLock"
"transactionid" "18:14:17.969603" 681080 "INSERT " "ShareLock" 681204 "INSERT " "ExclusiveLock"
"transactionid" "18:13:41.531575" 681112 "INSERT " "ShareLock" 679636 "INSERT " "ExclusiveLock"
"transactionid" "18:04:16.195069" 679556 "INSERT " "ShareLock" 679776 "INSERT " "ExclusiveLock"
"transactionid" "17:58:54.284211" 679696 "INSERT " "ShareLock" 678940 "INSERT " "ExclusiveLock"
"transactionid" "17:57:54.220879" 681144 "INSERT " "ShareLock" 679792 "INSERT " "ExclusiveLock"
"transactionid" "17:57:28.736147" 679932 "INSERT " "ShareLock" 679696 "INSERT " "ExclusiveLock"
"transactionid" "17:53:48.701858" 679580 "INSERT " "ShareLock" 679572 "INSERT " "ShareLock"


query results from pg_stat_activity  ;


SELECT pg_stat_activity.pid,
    pg_stat_activity.usename,
pg_stat_activity.state,
    now() - pg_stat_activity.query_start AS runing_time,
    LEFT(pg_stat_activity.query,7) ,
    pg_stat_activity.wait_event
   FROM pg_stat_activity
  ORDER BY (now() - pg_stat_activity.query_start) DESC;

681216postgresactive07:32.7INSERT transactionid
679688postgresactive06:32.2INSERT transactionid
679572postgresactive05:44.0INSERT transactionid
681080postgresactive01:44.6INSERT transactionid
681112postgresactive01:08.2INSERT transactionid
679556postgresactive51:42.9INSERT transactionid
679696postgresactive46:20.9INSERT transactionid
681144postgresactive45:20.9INSERT transactionid
679932postgresactive44:55.4INSERT transactionid
679580postgresactive41:15.4INSERT transactionid
679400postgresactive39:51.2INSERT transactionid
679852postgresactive37:05.3INSERT transactionid
681188postgresactive36:23.2INSERT transactionid
679544postgresactive35:33.4INSERT transactionid
675460postgresactive26:06.8INSERT transactionid


select version ();
PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

CPU: v32
RAM: 320 GB
shared_buffers = 64GB
effective_cache_size = 160 GB

any comments on the issue?


Thanks,
Rj

Re: INSERTS waiting with wait_event is "transactionid"

From
Laurenz Albe
Date:
On Thu, 2021-04-08 at 20:14 +0000, Nagaraj Raj wrote:
> We are trying to load data around 1Bil records into one table with INSERT statements
>  (not able to use COPY command) and they are been waiting for a lock and the wait_event
>  is "transactionid", I didn't find any information in the documents. Queries have been
>  waiting for hours.

That means that your statement is stuck behind a row lock.

Row locks are stored on the table row itself and contain the transaction ID.
So the process has to wait until the transaction goes away, which is implemented
as waiting for a lock on the transaction ID.

There must be a long running transaction that locks a row that is needed for
the INSERT.  It could be a row in a different table that is referenced by a
foreign key.

Make that long running transaction go away.  Transactions should never last that long.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: INSERTS waiting with wait_event is "transactionid"

From
Nagaraj Raj
Date:
Hi Laurenz, Thanks for the response. 

Yeah understand that, but I'm trying to figure out why it is taking too long. there is foreign key relation to this table. 


Thanks,
Rj
On Friday, April 9, 2021, 02:16:08 AM PDT, Laurenz Albe <laurenz.albe@cybertec.at> wrote:


On Thu, 2021-04-08 at 20:14 +0000, Nagaraj Raj wrote:

> We are trying to load data around 1Bil records into one table with INSERT statements
>  (not able to use COPY command) and they are been waiting for a lock and the wait_event
>  is "transactionid", I didn't find any information in the documents. Queries have been
>  waiting for hours.


That means that your statement is stuck behind a row lock.

Row locks are stored on the table row itself and contain the transaction ID.
So the process has to wait until the transaction goes away, which is implemented
as waiting for a lock on the transaction ID.

There must be a long running transaction that locks a row that is needed for
the INSERT.  It could be a row in a different table that is referenced by a
foreign key.

Make that long running transaction go away.  Transactions should never last that long.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com