InnoDB transactions
InnoDB is a transactional, relational engine based on MySQL server. Since 2001, it comes in a standard build, and from version 5.1 it can be installed as a plug-in (without the need to recompile the server core). The syntax is very simple.
About the definition
The definition of transactionality and relationality means, firstly, meaningful coherence of tables through FK and, as a result, data integrity when deleting rows. With MyIsam, as you know, you had to manually delete the associated data in several tables, in InnoDB - cascading deletion with one query. Secondly, since for a database parallel versions of data are inconceivable as in SVN and there is no one to combine these versions into one branch, but parallel work of several processes (users) with one data is necessary, then the transaction becomes a solution.
The queue of requests-cars is now replenished with an atomic transaction-bus. 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, the default is blocking at the row level (by PK ), while in MyIsam the atomic operation locks the entire table.
Responsiveness = blocking
Two engines therefore cannot be compared - InnoDB because of trans activity has to go down to the row level, since the probability of a queue to the same row is lower for two processes, therefore the work will be faster. But as a result, blocking flags must be made for each line, which means a bit more memory. Due to the difference in data blocking levels, it is quite difficult to compare InnoDB with MyIsam by performance depending on the number of processes.
There are several types of locks.
As an educational program, you can manually block the whole table (but not necessary, because for InnoDB this is a slow process that inhibits all processes). Reblocking removes previous locks. You can also block virtual tables (view)
In cases where the two processes simultaneously and partially affect the general data, then not all data will be completely blocked. There are concessions when parallel transactions gain access to incomplete transactions.
The current level can be obtained from the settings, you can register in the settings or execute a query - both for the duration of the transaction and for the entire connection.
According to the degree of accuracy (lock severity) in descending order according to the SQL92 standard, there are:
In REPEATABLE READ there is a phantom insert problem . Since only rows on UPDATE are blocked, but not on INSERT, an insertion can be made in parallel with a repeat-read transaction, which causes a phantom series. To avoid this, InnoDB uses three locking methods - a string, a range, and the next line in case of insertion (I didn’t get any deeper)
This whole theory is certainly useful, but in reality they are used by real queries.
With these requests for the time of the execution of the transaction, it goes into a new mode.
Deadlock injuries
Deadlock and deadlock of simultaneous processes (threads) that need the same or dependent data often arise in programming. InnoDB is no exception. For example, if there are two transactions and each wants to change the resources (lines / range of lines) that are now locked. It turns out that no transaction can end.
In such situations, InnoDB is forced to roll back one of the transactions and issue an error
Such problems arise when large parallel insertion / modification / deletion of rows by several processes. MySQL advise all transactions to provide a re-launch of transactions.
On this topic..
Original article
START TRANSACTION;
...
COMMIT; -- ROLLBACK; -About the definition
The definition of transactionality and relationality means, firstly, meaningful coherence of tables through FK and, as a result, data integrity when deleting rows. With MyIsam, as you know, you had to manually delete the associated data in several tables, in InnoDB - cascading deletion with one query. Secondly, since for a database parallel versions of data are inconceivable as in SVN and there is no one to combine these versions into one branch, but parallel work of several processes (users) with one data is necessary, then the transaction becomes a solution.
The queue of requests-cars is now replenished with an atomic transaction-bus. 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, the default is blocking at the row level (by PK ), while in MyIsam the atomic operation locks the entire table.
Responsiveness = blocking
Two engines therefore cannot be compared - InnoDB because of trans activity has to go down to the row level, since the probability of a queue to the same row is lower for two processes, therefore the work will be faster. But as a result, blocking flags must be made for each line, which means a bit more memory. Due to the difference in data blocking levels, it is quite difficult to compare InnoDB with MyIsam by performance depending on the number of processes.
There are several types of locks.
- READ (while I am reading - no one will write) - the default setting is SELECT
- WRITE (while I am writing - no one will read or write) - the default is UPDATE
- LOW_PRIORITY WRITE (I will quickly read if someone is waiting)
As an educational program, you can manually block the whole table (but not necessary, because for InnoDB this is a slow process that inhibits all processes). Reblocking removes previous locks. You can also block virtual tables (view)
LOCK TABLES user WRITE, company READ;
UNLOCK TABLES;Isolation levels
In cases where the two processes simultaneously and partially affect the general data, then not all data will be completely blocked. There are concessions when parallel transactions gain access to incomplete transactions.
The current level can be obtained from the settings, you can register in the settings or execute a query - both for the duration of the transaction and for the entire connection.
SELECT @@global.tx_isolation;
SET TRANSACTION ISOLATION LEVEL READ COMMITED;According to the degree of accuracy (lock severity) in descending order according to the SQL92 standard, there are:
- SERIALIZEABLE - complete transaction independence, incl. your reading
- REPEATABLE READ (repeated read) is the default value for InnoDB. Reading shared lines in transactions is allowed, but not changing them.
- READ COMMITED (fixed reading) - write lock, but general reading. There is a recurring reading problem, i.e. in the first transaction, common data is read several times in different ways, because the second transaction changes them.
- READ UNCOMMITED (“dirty” uncommitted reading) - no read / write lock. With two simultaneous UPDATEs, the field will receive the value of the last change in both transactions. Many problems are possible, especially if another one reads before the ROLLBACK of one change transaction.
In REPEATABLE READ there is a phantom insert problem . Since only rows on UPDATE are blocked, but not on INSERT, an insertion can be made in parallel with a repeat-read transaction, which causes a phantom series. To avoid this, InnoDB uses three locking methods - a string, a range, and the next line in case of insertion (I didn’t get any deeper)
This whole theory is certainly useful, but in reality they are used by real queries.
- Read with REPEATABLE READ level (write lock). Waiting if someone is working on the data.
SELECT... LOCK IN SHARE MODE - Read in SERIALIZEABLE mode (read / write lock)
SELECT... FOR UPDATE
With these requests for the time of the execution of the transaction, it goes into a new mode.
Deadlock injuries
Deadlock and deadlock of simultaneous processes (threads) that need the same or dependent data often arise in programming. InnoDB is no exception. For example, if there are two transactions and each wants to change the resources (lines / range of lines) that are now locked. It turns out that no transaction can end.
In such situations, InnoDB is forced to roll back one of the transactions and issue an error
ERROR 1213 (40001): Deadlock found when trying to get lock; try
restarting transactionSuch problems arise when large parallel insertion / modification / deletion of rows by several processes. MySQL advise all transactions to provide a re-launch of transactions.
On this topic..
Original article
')
Source: https://habr.com/ru/post/57226/
All Articles