Mysql Transaction When? How? Why?

Question

I’ve been reading the question What is a Mysql Transaction for? and I was left with many doubts on this subject.

When we should use transactions?
Why should we use transactions?
How we should use transactions?

Some examples:

• In case I’m in product sales website, in PHP and that has stocks, and that they cannot fall from zero what happens or what I can do if: The stock of one product is with a unit and we have two customers trying to buy the product. Starts the transaction in one of them, the other can have access to BD? Or the query SQL throws an exception on the second customer trying to buy the product? It will not cause much embarrassment if there are many customers trying to buy the same product with stock equal to a?

• If several users are registering at the same time, does it make sense for each user to have a new transaction? Will each of them have to wait for the other’s transaction to end? Otherwise, there may be conflicts of login’s equal?

• It makes sense in these cases to use transactions? Because if I have the comic book prepared not to accept negative values, in the case of stocks and so that the login’s be unique no longer need to use transactions, right?

• Transactions would no longer be indicated for dependencies between tables? Such as foreign keys.

Answer 1

I will give you a partial answer, because I also do not know in detail how transactions work (whether in Mysql or any other bank):

1. First of all, do not confuse isolation with lock. The first is to allow two operations to occur in parallel without one interfering with the other (i.e. each sees the database as being "frozen" the moment it began until the moment it ends). The second is about avoiding concurrent access problems - where two or more parallel operations try to move the same data (i.e. they are "competing" for access to it).

   One transaction has two distinct objectives: 1) to ensure the atomicity of the operation - either it is 100% successful, or it is as if it had never started; atomicity ensures that even in a "catastrophic" failure in the system (e.g., power outage) the bank does not keep with part of the operation made and part no. 2) isolation between different operations. See definition of ACID for more details.

   To my knowledge (Disclaimer: I don’t know much), transactions have nothing to do with Locks. So, answering your first point, what will happen in this situation depends more on how Locking that your bank uses (read lock, write lock, pessimistic lock, optimistic lock) than the use or not of transactions.

   In your stock example, transactions will not prevent an eventual running condition between the two operations: 1) both began, and saw that the stock was 1; 2) both proceeded, unaware of each other (i.e. isolated); 3) both, in the end, assigned the value of the stock to 0 (note: assigned; there is, in relational databases, the notion of "decreased"). The fact that the value has been assigned to zero twice from the bank’s point of view is not "abnormal": after all, "the value was to be zero, no? two distinct processes assigned it to zero..."

2. Responding to their second point, it doesn’t really make sense in a system with dozens of simultaneous users to make one wait for the other before being able to do whichever operation (pessimistic lock). You will have to find another way to avoid running conditions (the other answers give viable alternatives at first glance, but I don’t have enough knowledge to evaluate them reliably).

3. Whether it makes sense or not to use transactions, this is something you need to define based on ACID principles: is it bad if "half transaction" is completed? (e.g., the "mark item for customer shipping" operation successfully completed, but the "customer paid" operation failed) Is it bad if a transaction reads data that is not yet in the database? (e.g.: customer A started buying, stock went to zero, customer B tried to buy but saw that the stock was already zero, customer A canceled the purchase and the stock is no longer zero). Etc..

4. Referential integrity It has nothing to do with transactions: the bank will take care that every foreign key points to a correct record, with or without transactions. Likewise, restrictions guy UNIQUE etc will be honored even in the presence of competing accesses.

   In fact, this property gives us one more way to deal with possible race conditions: in a system I developed, the balance in a bank account would be controlled; it is possible, at first, that two users would touch this balance at the same time, so that the result could be incorrect (e.g.: the balance is 1000; user The deposit 500 at the same time that user B removes 200; by isolation, one does not know the other; no matter which of the two "has the final word", the result would be incorrect - 1500 in the case of A, 800 in the case of B). This is a problem even after using a solution similar to J.A’s. (separate isolated transactions from bank conciliation - where these transactions are actually confirmed).

   The solution was to create a "chain of balances": the "current balance" of an account is a table row; it has account, value, date, and reference to the previous balance (a foreign key to the table itself). When assigning a new balance (create another line), the last recorded balance of that account is placed as "previous". If two transactions start at the same time, one of them will successfully complete, but the following will fail: because when trying to assign the previous balance, the restriction UNIQUE will prevent - as two balances tried to use the same value in the column anterior. That way, the second transaction would have to start again - hence already getting the updated amount of the balance.

   A similar strategy could be used to prevent two customers from taking the last product from stock. In this case, the one who completed the transaction first would "win", and the others would not have their purchase successfully completed. Everyone could start a transaction in parallel, and cancelling one would not affect the others - since only one transaction completed would have any effect on the conclusion of the others. I am not saying that it is the best way (I repeat: I am inexperienced in BD), but as far as my knowledge reaches is a correct way.

Answer 2

Yeah, the other one gets access to the comic, what has to be done before you remove the product from stock you have to make a check if there is still available quantity of product x in stock just before performing the operation to remove product x from stock and if possible carry out a new check query if the quantity is not less than 0, if you stayed you will have to perform a rollback of that transaction and display some error message to the user, as two purchase operations of the same product were made at the same time with different fractions of seconds, it is something difficult to happen, but if it can happen it has to be treated.

But why do the second check? Because if the quantity is less than 0 is because someone bought practically together, but some was even 1 ms was before, then you have to make a check before and after removing the product from stock.

But wouldn’t that entail too much access to the bank and harm the server? For you to have an Ecommerce with so much access you will need a pretty robust server, so that’s not what has to give a problem, because an Ecommerce that has millions or thousands of people buying at the same time it generates a lot of money, so server investment and security is a must.

Answer 3

I think the purpose of Mysql Transactions is not to avoid collisions in a BD but to ensure that an operation is completed (but I may be wrong).

However, to avoid collisions ... in the usual model each user is directly activating the execution of operations fold the BD ... in this case, the decrease of any value of the stock table ... the increment in the shopping cart, etc ... and of course if two click at the same time there may be the bad luck of the collidire operations ...

The model I follow to avoid collisions is to create a table where the intentions of the users are recorded ... and only the intention ... if two million click at the same time, the only transaction performed on the BD is inserting the purchase in the table of intentions ... after that a Trigger (this yes activated by the user) executes the instructions one to one of the table of intentions in order of entry.

If millions want the same item, the first purchase and all others receive messages that they can no longer.