Postgresql deadlock when insert\delete on device_queue table

ChirpStack Network Server
#1

Hi, we faced an issue: regular deadlock in DB that is caused by the same combination of SQL queries with different device ids. Is it a bug or there is a way to avoid this issue?

Sep 7, 2021 @ 08:07:45.697	ERROR:  deadlock detected
Sep 7, 2021 @ 08:07:45.697	DETAIL:  Process 8421 waits for ShareLock on transaction 6822714; blocked by process 8658.
Sep 7, 2021 @ 08:07:45.697		Process 8658 waits for ShareLock on transaction 6822711; blocked by process 8421.
Sep 7, 2021 @ 08:07:45.697		Process 8421: delete from device_queue where dev_eui = $1
Sep 7, 2021 @ 08:07:45.697		Process 8658: 
Sep 7, 2021 @ 08:07:45.697		        insert into device_queue (
Sep 7, 2021 @ 08:07:45.697		            created_at,
Sep 7, 2021 @ 08:07:45.697		            updated_at,
Sep 7, 2021 @ 08:07:45.697					dev_addr,
Sep 7, 2021 @ 08:07:45.697		            dev_eui,
Sep 7, 2021 @ 08:07:45.697		            frm_payload,
Sep 7, 2021 @ 08:07:45.697		            f_cnt,
Sep 7, 2021 @ 08:07:45.697		            f_port,
Sep 7, 2021 @ 08:07:45.697		            confirmed,
Sep 7, 2021 @ 08:07:45.697		            emit_at_time_since_gps_epoch,
Sep 7, 2021 @ 08:07:45.697		            is_pending,
Sep 7, 2021 @ 08:07:45.697		            timeout_after,
Sep 7, 2021 @ 08:07:45.697					retry_after
Sep 7, 2021 @ 08:07:45.697				) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
Sep 7, 2021 @ 08:07:45.697		        returning id
Sep 7, 2021 @ 08:07:45.697	HINT:  See server log for query details.
Sep 7, 2021 @ 08:07:45.697	CONTEXT:  while deleting tuple (1,5) in relation "device_queue"
Sep 7, 2021 @ 08:07:45.697	STATEMENT:  delete from device_queue where dev_eui = $1

Chirp versions:
AS: 3.17.0
NS: 3.15.0
GB: 3.13.1
FUOTA: 3.0.0-test.4

#2

Seems like that was because the node with postgres utilized 100% CPU. And this slows down the SQL queries up to 1s. Long queries interact with each other more often and that is why deadlock happens.

As a side effect of postgres overload is a drop of upstream messages with the error log record: “zero items collected” (you’ll see a lot of them)

Good luck

1 Like