InnoDB Transactions

    InnoDB is a transactional, relational engine powered by a MySQL server. Since 2001, it has been supplied in the standard assembly, and from version 5.1 it can be installed as a plug-in (without having to recompile the server core). The syntax is very simple.
    START TRANSACTION;
    ...
    COMMIT; -- или же ROLLBACK; если что-то пошло в логике не так

    About definition


    The definition of transactionality and relationality means, first of all, meaning the full connectivity of tables through FK and, as a result, data integrity when deleting rows. With MyIsam, as you know, you had to manually delete the related data in several tables, in InnoDB - cascading deletion with a single query. Secondly, since parallel versions of data as in SVN are unthinkable for the database and there is no one to combine these versions into one branch, but parallel work of several processes (users) with the same data is necessary, then the transaction becomes the solution.
    The request-car queue is now replenished with an atomic bus transaction. Naturally, this is bad, because the longer and longer the transaction is performed, the more parallel processes will wait for it. To speed up work, stops are created - types and levels of data blocking. For InnoDB, by default, this is row-level locking ( PK-based ), while in MyIsam, an atomic operation locks the entire table.


    Transactivity = Lock


    Therefore, two engines cannot be compared - InnoDB has to go down to the row level because of the transactional nature, since the probability of a queue to the same line in two processes is lower, therefore, it will work faster. But as a result, you have to make lock flags on each line, which means a little more memory. Due to the difference in data blocking levels, comparing InnoDB with MyIsam in terms of performance depending on the number of processes is quite difficult.
    There are several types of locks.
    • READ (while I read - no one will write) - by default, SELECT is set
    • WRITE (while I write - no one will read or write) - by default, UPDATE is set
    • LOW_PRIORITY WRITE (I'll give you a quick read if someone is waiting)

    As an educational program, you can manually block the whole table (but it is not necessary because for InnoDB it miserably slows down all processes). Re-locking removes previous locks. You can also block virtual tables (view)
    LOCK TABLES user WRITE, company READ;
    UNLOCK TABLES;


    Isolation levels


    In cases where two processes simultaneously and partially affect common data, not all data will be completely blocked. There are exemptions when concurrent transactions gain access to pending transactions.
    The current level can be obtained from the settings, can be registered in the settings or executed by request - both for the duration of the transaction and for the duration of the entire connection. According to the degree of accuracy (lock strictness), in descending order according to the SQL92 standard, there are:
    SELECT @@global.tx_isolation;
    SET TRANSACTION ISOLATION LEVEL READ COMMITED;


    • SERIALIZEABLE - complete transaction independence, incl. your reading
    • REPEATABLE READ - The default value for InnoDB. Reading common lines in transactions is allowed, but not changing them.
    • READ COMMITED (read fixed) - write lock, but general read. There is a problem of repeated reading, i.e. in the first transaction, the general data is read several times in different ways, because the second transaction changes them.
    • READ UNCOMMITED ("dirty" reading uncommitted) - no blocking on reading and writing. With two simultaneous UPDATEs, the field will receive the value of the last change in both transactions. Many problems are possible, especially if before ROLLBACK one transaction reads another.

    There is a phantom insertion problem in REPEATABLE READ . Since only rows on UPDATE are blocked, but not on INSERT, an insert can be made in parallel with the repeat reading transaction, which will result in a phantom row. To avoid this, InnoDB uses three locking methods - a line, a range, and the next line in case of insertion (I didn’t get a deeper reading)
    This whole theory is of course useful, but in reality they are used by real queries.
    1. Read with REPEATABLE READ level (write lock). Waiting if someone is working on the data.
      SELECT... LOCK IN SHARE MODE
    2. Reading in SERIALIZEABLE mode (read / write lock)
      SELECT... FOR UPDATE

    With these requests for the duration of the transaction, it enters a new mode.

    Deadlock injuries


    Deadlocks, i.e. deadlock of simultaneous processes (threads) that need the same or data dependent on each other often arise in programming. InnoDB is no exception. For example, if there are two transactions going on and each wants to change the resources (rows / row range) that are currently locked. It turns out that no transaction can end.
    In such situations, InnoDB is forced to roll back one of the transactions and give an error. Such problems arise when large processes insert / modify / delete rows in parallel. MySQL advises that all transactions be equipped with a re-run of transactions. On this topic..
    ERROR 1213 (40001): Deadlock found when trying to get lock; try
    restarting transaction





    Original article

    Also popular now: