Encountering deadlocks when uploading advisories

[JimFuller-RedHat]

on latest master - after uploading (via ux) etc/test-data/csaf/* I see the following errors

2024-11-13T15:39:16.521222Z ERROR ThreadId(52) trustify_module_ingestor::service::advisory::csaf::loader: error=Query Error: error returned from database: deadlock detected
2024-11-13T15:39:16.523532Z ERROR ThreadId(52) trustify_module_ingestor::service: error=Query Error: error returned from database: deadlock detected

accompanied by some slow warnings:

2024-11-13T15:42:43.699888Z  WARN ThreadId(55) sqlx::query: slow statement: execution time exceeded alert threshold summary="UPDATE \"organization\" SET \"cpe_key\" …" db.statement="\n\nUPDATE\n  \"organization\"\nSET\n  \"cpe_key\" = $1,\n  \"website\" = $2\nWHERE\n  \"organization\".\"id\" = $3\nRETURNING\n  \"id\",\n  \"name\",\n  \"cpe_key\",\n  \"website\"\n" rows_affected=0 rows_returned=1 elapsed=12.21970387s elapsed_secs=12.21970387 slow_threshold=1s

[helio-frota]

Good catch I saw the same I reproduced it here and this the log

I thought this clone was hiding a bug after I removed it. But I added it back and saw 2 deadlocks

[helio-frota]

I found the point when/where the message changes:

• Query Error: error returned from database: duplicate key value violates unique constraint "package_pkey"
  commit 7f57a81

• Query Error: error returned from database: deadlock detected
  commit 47be914

dblog.txt

and for this I copy *.json from trustify/etc/test-data/csaf/ cloned in another directory to trustify/etc/test-data/csaf/ of these commits and then select all and upload.

[helio-frota]

when I comment this for loop, the test pass

running 1 test
test advisory::endpoints::test::upload_default_csaf_format_multiple_parallel ... ok