Open
Description
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.