[Bug]: could not serialize access due to concurrent update

[bielitom]

What type of bug is this?

Locking issue

What subsystems and features are affected?

Data node, Distributed hypertable

What happened?

Probably this bug occurs again in 2.8.0 PG14: https://github.com/timescale/timescaledb/issues/1721

TimescaleDB version affected

2.8.0

PostgreSQL version used

14.4

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Docker

What platform did you run on?

Not applicable

Relevant log output and stack trace

2022-09-07 02:36:01.962 UTC [66] ERROR:  [dn1]: could not serialize access due to concurrent update
2022-09-07 02:36:01.962 UTC [66] STATEMENT:  insert into data(device, ts, context, channel, original_ts, value, manual, string_value, source_id) values($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT DO NOTHING

How can we reproduce the bug?

Multiple parallel inserts without batching with randomly conflicting primary key.

DDL:

CREATE TABLE public."data" (
	channel int8 NOT NULL,
	context int8 NOT NULL,
	device int8 NOT NULL,
	ts timestamp NOT NULL,
	manual bool NOT NULL,
	original_ts timestamp NULL,
	source_id int8 NULL,
	string_value text NULL,
	value numeric(20, 10) NULL,
	CONSTRAINT data_pkey PRIMARY KEY (channel, context, device, ts)
);

SELECT create_distributed_hypertable('data', 'ts', 'device', 20, chunk_time_interval => INTERVAL '14 days', replication_factor => 2)

[nikkhils]

@Tomasz365 this is expected behavior in Multinode. We will use REPEATABLE READ transaction isolation level by default for connections made for the INSERT into the datanodes from the access node.

the behavior is similar to regular Postgres when using REPEATABLE READ. An example is provided below:

-- create regular postgres table
postgres=# create table test(time timestamp, id integer, value integer, primary key(time,value) );

On first psql1 session

postgres=# begin transaction isolation level repeatable read ;
BEGIN
postgres=*# insert into test values ('2022-04-01 00:01:00', 1, 7) ON CONFLICT DO NOTHING;                                                                 
INSERT 0 1

On second psql2 session

postgres=# begin transaction isolation level repeatable read ;
BEGIN
postgres=*# insert into test values ('2022-04-01 00:01:00', 1, 7) ON CONFLICT DO NOTHING;
-- the above waits for psql1 session now

continue txn on psql1 session

postgres=*# COMMIT;
COMMIT

psql2 session will now error out

postgres=*# insert into test values ('2022-04-01 00:01:00', 1, 7) ON CONFLICT DO NOTHING;
ERROR:  could not serialize access due to concurrent update
postgres=!#  

We use the stricter REPEATABLE READ transaction isolation level for datanodes so that they use the same transaction snapshot across the entire transaction that is driven by the access node.

[github-actions[bot]]

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days. Thank you!

[github-actions[bot]]

Dear Author,

We are closing this issue due to lack of activity. Feel free to add a comment to this issue if you can provide more information and we will re-open it. Thank you!

[Blizzard-JSONC]

Can we reopen this issue please?

[erimatnor]

Here's a POC for how different isolation levels can be supported with a GUC: https://github.com/timescale/timescaledb/compare/main...erimatnor:timescaledb:set-remote-isolation-level

[Blizzard-JSONC]

@erimatnor Thanks for the update! I'm going to see if I can build this and test it on our development test cluster. I appreciate the work! :)

[github-actions[bot]]

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days.

Thank you!

[github-actions[bot]]

Dear Author,

We are closing this issue due to lack of activity. Feel free to add a comment to this issue if you can provide more information and we will re-open it.

Thank you!