Race condition: missing stream state

Hi! 👋🏻
We're facing a race condition about twice a week, where we query `_airbyte_state` and the state of a stream is missing from that table.

### Explanation
Our investigation led to this [session.commit()](https://github.com/airbytehq/PyAirbyte/blob/68078b6864f63575c82dffa1adec82ec741fcc92/airbyte/caches/_state_backend.py#L174).
At the end of a synchronization, two queries are sent:
1. Delete the state of the stream
2. Insert the new state.

As it is not happening in a single transaction, it is possible to try to read `_airbyte_state` during the short time the state has been deleted, and before the new one is inserted.

---

### Context
Our infrastructure may run some very specific streams concurrently. They are configured to do a `full_refresh` and use the `append` write strategy, and concurrent run are fine from our perspective.
Before a stream runs, we check the `_airbyte_state` table to detect if the state already exists. When there's no state, we try to automatically migrate the stream from Airbyte to PyAirbyte.

We stumbled upon cases where we try to read the state after PyAirbyte deleted a state and before it inserted the new one.
Using a Snowflake cache is certainly part of what highlights the race condition, as queries can be queued there.