Deadlock while inserting multiple STAC items at once

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.

Start by reproducing concurrent item creation against the PostgreSQL backend, then read stac_fastapi/pgstac/stac_fastapi/pgstac/transactions.py and db.py around create_item and dbfunc. Inspect the reported PostgreSQL lock conflict and verify behavior with simultaneous POST requests. Done means concurrent inserts no longer fail with DeadlockDetectedError, with regression coverage if the existing test structure supports it.