T
TL;DRSQL databases in general, not only MySQL, are http://pt.wikipedia.org/wiki/Transa%C3%A7%C3%A3o_em_base_de_dados , that is, they allow you to perform a sequence of operations as an indivisible block in order to ensure data integrity in an environment with concurrent access.The ProblemIn the example cited in the question, imagine queries #1, #2 and #3 are operations that affect the database and we do not use a transaction to control them. Let's use as an example an e-commerce:Updates customer delivery dataInsert a new record of the purchase made, verifying that you have stockDebits product stockNow imagine two customers trying to finish their purchases in this fictional e-commerce. The server receives two requests almost simultaneously and begins to process the requests in the sequence presented above. The two requests are being processed simultaneously in different threads.Imagine even that both client A and client B have selected a product that has only one unit in stock. We can end the following line of execution:Thread Updates customer data A (step #1), checks stock inserts purchase record (step #2)The thread A is blocked and B is executedThread B updates customer data B (step #1), check stock inserts purchase registration (step #2)Thread B updates the stock, which is now zealousThe thread B is blocked and A is executedThread The update the stock, which is now negative!Note that despite the code checking the stock the execution order makes the check not guaranteed in the next step.The SolutionTransactional databases use the concept http://pt.wikipedia.org/wiki/ACID :Atom: a transaction is a sequence of indivisible operations, or runs as a whole, or everything is broken.Consistency: At the end of the transaction, the state of the data must be consistent.Isolation: Although some systems allow to break isolation, in general, an ongoing transaction cannot be accessed by other transactions in order to avoid reading an inconsistent state, a "dirty".Durability: in case of success (commit) the persistence of data must be guaranteedTo ensure these concepts, in general, databases use locks when simultaneous access to the same data structure occurs. That is, if someone is already moving the data, the others have to wait for him to finish and wait his turn in the queue.In practiceBy using transactional databases, we can take advantage of this management control by the SGBDRs (Relational Database Management Systems).Including the ACID transaction concept in the previous example, let's see how the execution is:Thread Starting a transaction, updating customer data A (step #1), check the stock inserts the purchase record (step #2)The thread A is blocked and B is executedThread B starts a transaction, but when trying to update the B client data it is blocked because the A transaction is not over yetThread The update the stock, which is now zealous, and does commit in the transaction.The thread B is unlocked and is executedThe thread B updates customer B data (step #1), checks stock and returns an error as it does not find the product availableThread B runs a rollback to undo the changes already madeThe final result is as if only the thread Running it and B never existed.Not everything is a sea of rosesThere are some problems inherent in ACID transactions, the performance the biggest of them.Although it is important to ensure data integrity, for many systems where availability is the most critical factor, a model that blocks simultaneous access becomes unviable.This is one of the main factors for the emergence and adoption of various non- transactional database systems and http://pt.wikipedia.org/wiki/NoSQL .The important thing is to understand that the use of transactions has a cost and on some occasions this can be too high. One of the most common representations of trade-off of data persistence is the following (retreat http://berb.github.io/diploma-thesis/community/061_challenge.html The graph demonstrates that consistency, availability and partitioning (scaling the database across multiple nodes) are features that affect each other. You simply cannot have the best of the three, according to http://en.wikipedia.org/wiki/CAP_theorem .Relational databases usually sacrifice partitioning for consistency and availability, while some systems In the SQL sacrifice the consistency of the data.
