Deadlock while inserting multiple STAC items at once

[duckontheweb]

Ported from stac-utils/stac-fastapi#407.

Original Issue:

To speed up storing STAC items I am using asyncio.

async def _store_item(self, session, payload):
    url = f"{self._stac_url}/collections/{self._collection_name}/items"
    async with session.post(url, json=payload) as response:
            return await response.json()

But looks like there is a deadlock in pgstac implementation.

 Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 398, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/usr/local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/fastapi/applications.py", line 199, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.8/site-packages/brotli_asgi/__init__.py", line 76, in __call__
    await responder(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/gzip.py", line 35, in __call__
    await self.app(scope, receive, self.send_with_gzip)
  File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 580, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 241, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 52, in app
    response = await func(request)
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 219, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 152, in run_endpoint_function
    return await dependant.call(**values)
  File "/app/stac_fastapi/api/stac_fastapi/api/routes.py", line 55, in _endpoint
    await func(request_data, request=request), response_class
  File "/app/stac_fastapi/pgstac/stac_fastapi/pgstac/transactions.py", line 24, in create_item
    await dbfunc(pool, "create_item", item)
  File "/app/stac_fastapi/pgstac/stac_fastapi/pgstac/db.py", line 82, in dbfunc
    return await conn.fetchval(q, *p)
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 607, in fetchval
    data = await self._execute(query, args, 1, timeout)
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1625, in _execute
    result, _ = await self.__execute(
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1650, in __execute
    return await self._do_execute(
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 199, in bind_execute
asyncpg.exceptions.DeadlockDetectedError: deadlock detected
DETAIL:  Process 455912 waits for AccessExclusiveLock on relation 321236 of database 16467; blocked by process 455913. 

Which means several simultaneous requests to insert an item will fail, so it becomes a bottleneck. Please let me know if anyone has seen this issue and if there is a workaround.

[lhcorralo]

Hi!

I can confirm this, it happens under the same condition, multiple POST requests simultaneously for inserting an item.

The stack trace is

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 407, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "/usr/local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.8/site-packages/stac_fastapi/api/middleware.py", line 76, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/cors.py", line 84, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/brotli_asgi/__init__.py", line 87, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/usr/local/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 235, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 161, in run_endpoint_function
    return await dependant.call(**values)
  File "/usr/local/lib/python3.8/site-packages/stac_fastapi/api/routes.py", line 57, in _endpoint
    await func(request=request, **request_data.kwargs()), response_class
  File "/usr/local/lib/python3.8/site-packages/stac_fastapi/pgstac/transactions.py", line 67, in update_item
    await dbfunc(pool, "update_item", item)
  File "/usr/local/lib/python3.8/site-packages/stac_fastapi/pgstac/db.py", line 82, in dbfunc
    return await conn.fetchval(q, *p)
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 644, in fetchval
    data = await self._execute(query, args, 1, timeout)
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1658, in _execute
    result, _ = await self.__execute(
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1683, in __execute
    return await self._do_execute(
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1730, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.DeadlockDetectedError: deadlock detected
DETAIL:  Process 585 waits for AccessExclusiveLock on relation 18373 of database 16384; blocked by process 586.
Process 586 waits for AccessExclusiveLock on relation 18373 of database 16384; blocked by process 585.

I did not pin the version of stac-fastapi, so everything is "latest", sorry... anyway, it will probably happen in the current latest version, and I had to format my PC a few days ago.

About pgstac, I am using the docker image ghcr.io/stac-utils/pgstac:v0.6.11

I do not know any workaround (I am not familiar with stac-fastapi, nor pgstac as a developer).