Testing data integrity using Mysql Transactions

Question

I’m using an Ecommerce system that uses Mysql Transactions to maintain data integrity, for example in the simultaneous purchase of multiple customers of the same product where the system needs to decrease inventory and prevent it from being negative.

Technically, how Mysql does the control to prevent a request/query from one user from conflicting with another user(s) user(s) and thus maintaining this integrity?

What is the best practice for testing this integrity? Maybe use an HTTP client to generate multiple HTTP requests in which to simulate purchases?

Is there a number of simultaneous queries where the use of Transactions turns out to be unnecessary? If yes, from how many concurrent users we have to worry about integrity using Mysql Transactions?

Answer 1

Before answering your question, note that to ensure the atomicity of your database operations, you should be concerned about the Isolation Level, that is, ensure that during your purchase the executed selects and subsequent commits are executed atomically, ie without competition. Note that without this, even with transactions you may still have competition problems.

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 purchase transactions.

Now, by answering your question, you can create unit tests that run the same purchase operation on Threads separate. Another way is to use the Jmeter or Gatling to perform these tests.