Date: 2023-12-19
Not-Implemented
We are facing a concurrency issue when update flags concurrently. The multiple queries from clients simultaneously access the user_flags column of the message_mailbox table in PostgreSQL. Currently, the James fetches the current data, performs changes, and then updates to database. However, this approach does not ensure thread safety and may lead to concurrency issues.
CRDT (conflict-free replicated data types) principles semantic can lay the ground to solving concurrency issues in a lock-free manner, and could thus be used for the problem at hand. This explores a different paradigm for addressing concurrency challenges without resorting to traditional transactions.
To address the concurrency issue when clients make changes to the user_flags column, we decide to use PostgreSQL's built-in functions to perform direct operations on the user_flags array column (without fetching the current data and recalculating on James application).
Specifically, we will use PostgreSQL functions such as array_remove, array_cat, or array_append to perform specific operations as requested by the client (e.g., add, remove, replace elements).
Additionally, we will create a custom function, say remove_elements_from_array, for removing elements from the array since PostgreSQL does not support array_remove with an array input.
Pros:
Cons:
Optimistic Concurrency Control (OCC): Using optimistic concurrency control to ensure that only one version of the data is updated at a time. However, this may increase the complexity of the code and require careful management of data versions. The chosen solution using PostgreSQL functions was preferred for its simplicity and direct support for array operations.
Read-Then-Write Logic into Transactions: Transactions come with associated costs, including extra locking, coordination overhead, and dependency on connection pooling. By avoiding the use of transactions, we aim to reduce these potential drawbacks and explore other mechanisms for ensuring data consistency.