I have an endpoint that register users to my database. Each user is associated with a user id in incremental order such as 1, 2, 3, 4, etc. If I insert a new user, the new user will have a user id of (current max user id + 1).
Suppose I have three users at my database with user id of 1, 2, and 3 correspondingly. If two users make a request to register concurrently, both users will have an user id of 4 which causes a duplicate conflict. One way to solve it is to use transaction since transactions are isolated. More specifically, user A has to wait until user B finishes his/her write to the database, then user A will proceed his/her write.
Transaction follows ACID rule. What about a normal insert method without using a transaction. For example, just db.collection("users").insertOne(user_json). Will this also follow ACID rule as well?
In my example above which two users make concurrent requests to register, will the two requests be isolated?
Updates to a single document still have the same ACID properties: a write to a single document is either successful or the document is not changed. You will not have a partially updated document and each update is applied atomically.
However, ACID transactions are a step up from an ACID update (in MongoDB as well as other databases). If a transaction is in progress and a write outside the transaction modifies a document, the transaction will abort with a write conflict and can be retried. See In-progress Transactions and Write Conflicts for more information.
Incrementing IDs are generally a performance anti-pattern for a distributed database as they require a single point of coordination (which is why the default unique identifiers are pseudorandom ObjectIDs that can be independently generated).
However, there are a few references for your use case:
You’ll notice the above references rely on the ACID properties of updating a counter document with a unique index rather than using multi-document transactions.