Thread: INSERTS waiting with wait_event is "transactionid"

INSERTS waiting with wait_event is "transactionid"

From

Nagaraj Raj

Date:

08 April 2021, 23:14:21

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;


681216	postgres	active	07:32.7	INSERT	transactionid
679688	postgres	active	06:32.2	INSERT	transactionid
679572	postgres	active	05:44.0	INSERT	transactionid
681080	postgres	active	01:44.6	INSERT	transactionid
681112	postgres	active	01:08.2	INSERT	transactionid
679556	postgres	active	51:42.9	INSERT	transactionid
679696	postgres	active	46:20.9	INSERT	transactionid
681144	postgres	active	45:20.9	INSERT	transactionid
679932	postgres	active	44:55.4	INSERT	transactionid
679580	postgres	active	41:15.4	INSERT	transactionid
679400	postgres	active	39:51.2	INSERT	transactionid
679852	postgres	active	37:05.3	INSERT	transactionid
681188	postgres	active	36:23.2	INSERT	transactionid
679544	postgres	active	35:33.4	INSERT	transactionid
675460	postgres	active	26:06.8	INSERT	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,

Re: INSERTS waiting with wait_event is "transactionid"

From

Laurenz Albe

Date:

09 April 2021, 12:15:53

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:

09 April 2021, 21:22:17

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,

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