I'm using an ecommerce system that uses MySQL Transactions to maintain data integrity, for example, when buying several customers of the same product at the same time, where the system needs to reduce inventory and prevent it from becoming negative.
Technically, how does MySQL control to prevent a user's request/query from conflicting with another user(s) and thus maintaining this integrity?
What is the best practice for testing this integrity? Maybe use some HTTP client to generate multiple HTTP requests which would simulate purchases?
Is there a number of simultaneous queries where the use of Transactions ends up being unnecessary? If so, how many concurrent users do we have to worry about integrity using MySQL Transactions on?
Before answering your question, note that to guarantee the atomicity of your database operations, you should be concerned with the Isolation Level , that is, ensure that during your purchase the executed selects and subsequent commits are executed atomically, that is, without competition. Realize that without this, even with transactions you can still have concurrency issues.
Another way to solve your problem is to make the code that performs such operations
synchronized , that is, only one Thread is executed at a time for buy operations.