Introduction to the transaction in MySQL. As it turned out, everyone knows, but not everyone understands

The transaction is a mechanism that allows you to interpret multiple changes in the database as a single operation. Either all changes will be taken, or they will all be rejected. Neither other session cannot access the table while there is an open transaction, within which any changes are performed in this table. If you try to make a data selection immediately after you change, all made changes will be available.

Such a transaction support database mechanism such as InnoDB or BDB starts a start transaction transaction. Transaction is completed when confirming or cancel changes. You can complete the transaction with two commands. Commit command saves all changes in the database. The rollback command cancels all changes.

In the example, a table with transaction support is created below, data is inserted into it, then a transaction is launched, within which the data is deleted, and the transaction roll back (cancellation of deletion) is performed:

CREATE TABLE SAMPLE_INNODB (ID INT (11) NOT NULL AUTO_INCREMENT, NAME VARCHAR (150) DEFAULT NULL, PRIMARY KEY (ID)) ENGINE \u003d INNODB DEFAULT CHARSET \u003d UTF8; INSERT INTO SAMPLE_INNODB VALUES (1, "Alexander"), (2, "Dmitry"); Start Transaction; DELETE FROM SAMPLE_INNODB WHERE ID \u003d 1; Delete from sample_innodb where id \u003d 2; rollback;

Since the transaction rollback occurred, the data from the table was not deleted.

And if instead of Rollback, we wrote a commit, then both lines would be deleted.

Transactions are required when it is necessary that several requests are accurately applied and implemented "simultaneously", or none of them was fulfilled if something goes wrong.

As an example, you can bring the system of payment on some site. At the time of purchase, the order must be marked as paid, and at the same time you need to write off the money from the user's balance sheet. If something is wrong - there will be either a user without a purchased product and without money, or a store without a product and without money. And with the help of transactions, we can easily avoid this.

For a debt, I have to sometimes hold interviews to the position "[Senior | Junior] developer Python / Django", "Timlid". To my great surprise, I found that 9 out of 10 applicants, in the summary of which the words "MySQL / InnoDB / TRANSACTIONS / TRIGGERS / STORED PROC ETC." are, absolutely nothing can tell about their past experience with them. Not a single description of the use option, unfortunately, I never received.

Next, I offered to try to offer a solution option for the next situation:

Suppose we are an online service, which in turn enjoys some external paid API (activation of the service, paid content, or that your soul is pleased), that is, our service pays money for the use of the API. The user in our system creates a request to activate the service, fills all the fields and on the last page of the "Activate Service" button. That is, at the time of sending an HTTP request, we have an entry in our database (request to activate the service). What is our algorithm? - I ask and I continue:

Take the user's balance from the database;
- if the balance is enough, then pull the API;
- if everything is fine, then we write off the balance amount for the service, do Update, Commitimim, otherwise you roll back;
- We answer the user.

Everything seemed to be trivially, but when I bring the first and most obvious problem in the form of 10 competitive requests (that they all at the beginning receive the same balance and start calling to the API), the solutions begin to offer the most sophisticated, ranging from performing 5 selectors (it is worth a confession, I I did not understand anything in this embodiment), the use of auto-closet meters, external cashes, new tables in the database, slips and still do not understand what.

As you know (they knew all the candidates!), InnoDB in MySQL provides a transactional mechanism and the possibility of line-up lock. In order to apply this shortest Lock, it is enough to add for Update expression at the end of the SELECT-A, for example:

SELECT * from Requests WHERE ID \u003d 5 FOR UPDATE

The transaction starts and all other sessions to the database will not be able to perform a similar request before the completion of our transaction, they will simply wait. To read the same record will be available in a state that depends on the level of transaction isolation.

It is also worth noting that the use of for Update is better to do with the autocommit turned off, because, regardless of what you were locked, after the first update, the locomotion.

It seems to be a trifle, it seems obvious, but 9 out of 10 ...

Upd.
The former name "transaction in MySQL", not disclosed in the article was replaced by "transactions in MySQL and SELECT for Update"

Zy.
The article does not say that the API needs to pull within the transaction and what to do in the event of a failure and how to process exceptional situations.

The transactional mechanism is supported only by InnOdB and BDB. Therefore, all the tables with which you want to work through transactions should be converted to the appropriate type. Can .

By default, MySQL works in Autocommit mode. This means that the results of the execution of any SQL operator, changing the data, will immediately be saved.
Autocommit mode can be turned off like this: set autocommit \u003d 0;

If you want to switch from the AutocomMit mode only to perform one sequence of commands, you can use the Begin or Start Transaction command to do this (starting with the MYSQL version 4.0.11.)

An example of a SQL query that fixes the number of goods selected by buying in the Corrections table and makes changes to the goods table:
Start Transaction; INSERT INTO CORRECTIONS SET ID_GOODS \u003d: ID_GOODS, NUMBER \u003d: NUMBER, ID_ODERS \u003d: ID_ODATERS ON DUPLICATE KEY UPDATE NUMBER \u003d NUMBER +: NUMBER; Update Goods Set Reserve \u003d Reserve +: Number, Available \u003d Available -: Number Where ID \u003d: id_goods; COMMIT;

The transaction is completed by the COMMIT operator. Changes are saved. In case of error in one of the requests, the changes will not be saved in any table.

If you need to create a more complex change mechanism, use the SavePoint and Rollback to SavePoint commands

The following operators implicitly complete the transaction (as if Commit was issued before execution):

Alter table
DROP DATABASE.
Load Master Data.
Set autocommit \u003d 1
Begin.
Drop Index
Lock Tables
Start Transaction.
Create Index
Drop Table
Rename Table
Truncate Table

PHP PDO offers its means of working with transactions. They can be read about them.

Parallel transactions and insulation levels (joint access)

Imagine that during the execution of the 1st transaction transaction, another user created a second parallel transaction and made a Select * from user request after the first request "INSERT INTO User (ID, NIK) Values \u200b\u200b(1," was performed in our transaction Nikola ').
What will the user of the second transaction sees?
Will he be able to see the inserted record even when the results of the first transaction have not yet been fixed (did COMMIT not occurred)? Or can he see the changes only after the results of the first transaction will be fixed?

It turns out there are both options. It all depends on the level of isolation of the transaction.

There is a transaction 4 insulation levels:

0 - Reading unconfirmed data (dirty reading) (read uncommitted, Dirty Read) - the lowest level of insulation. At this level, it is possible to read inadequate changes in parallel transactions. In this case, the second user will see the inserted entry from the first unfixed transaction. There is no guarantee that the unfixed transaction will be rejected at any time, so such reading is a potential source of errors.
1 - Read confirmed data (read committed) - it is possible to read data only recorded transactions. But at this level there are two problems. In this mode, the lines that participate in the sample within the transaction, are not blocked for other parallel transactions, the problem No. 1 implies from this:
"Unatforward Reading" (NON-REPEATABLE READ) is a situation where a few samples (SELECT) occurs within the transaction, and a parallel transaction is performed between these samples, which changes the data involved in these samples. Since the parallel transaction has changed the data, the result for the next sample on the same criteria in the first transaction will be different.

Problem number 2 - "Phantom reading" - This case is discussed below.
2 - Repeatable reading (REPEATABLE READ, SNAPSHOT) - at this isolation level it is also possible to read data only fixed transactions. Also, at this level, there is no problem of "non-refused reading", that is, lines that participate in the sample within the transaction, are blocked and cannot be changed by other parallel transactions. But the tables are not entirely blocked. Because of this, the problem of "phantom reading" remains. "Phantom reading" is when, during the execution of one transaction, the result of the same samples may change due to the fact that the entire table is blocked, but only those lines that participate in the sample. This means that parallel transactions can insert strings in the table in which the sample is performed, so the two select * from table requests can give a different result at different times when inserting data parallel transactions.
3 - Serializable (Serializable) - serializable transactions. The most reliable level of transaction isolation, but at the same time the slowest one. At this level, there are no problems of parallel transactions at all, but for this will have to pay the speed of the system, and the speed in most cases is extremely important.

By default, the level of insulation No. 2 is installed in MySQL (REPEATABLE READ). And, as I think, the MySQL developers did not in vain made the default exactly this level, since it is most successful for most cases. The first time it may seem that the best option number 3 is the most reliable, but in practice you can experience great inconvenience because of the very slow work of your application. Remember that much depends not on how good the level of isolation of transactions in the database is, and on how your application is designed. With competent programming, you can even use the lowest level of transaction isolation - it all depends on the characteristics of the structure and literacy of the development of your application. But it is unnecessary to strive for the lowest level of insulation - no, just if you use not the most secure mode, you should remember about the problems of parallel transactions, in this case you are not confused and do everything correctly.

SET Transaction - This operator sets the level of isolation of the next transaction, globally or only for the current session.

Set Transaction Isolation Level (Read Uncommitted | Read Committed | Repeatable Read | Serializable)

Existing compounds are not affected. To perform this operator, you need to have a SUPER privilege. Applying the session keyword sets the default level of all future transactions only for the current session.

You can also set the initial global insulation level for the MYSQLD server by running it with the -transAction-ISOLATION option.

The transaction is an operation consisting of one or more database queries. The essence of transactions - to ensure the correct execution of all requests within a single transaction, as well as provide the transaction isolation mechanism from each other to solve the problem of joint data access.

Any transaction is either fulfilled completely or not performed at all.

There are two fundamental concepts in the transactional model: COMMIT and ROLLBACK. Commit means fixing all changes in the transaction. Rollback means cancellation (rollback) of changes that occurred in the transaction.

When the transaction starts, all subsequent changes are saved in a temporary storage. In the case of Commit, all changes made within the framework of a single transaction will remain in the physical database. If Rollback executes, all changes made within this transaction will roll back.

In MySQL transactions are supported only by the InnODB tables. MYISAM transaction tables do not support. InnoDB is the default Autocommit, this means that by default each request is equivalent to a single transaction.

The transaction begins with a special query "Start Transaction" or "Begin". To complete the transaction, you need to either fix the changes (query Commit), or roll back to them (Rollback query).

Example with COMMIT:

Start Transaction; (also, you can write Begin.;) ... Some actions with the database (insert, update, delete ...) cOMMIT; // Fixation of actions, record them in physical database

Example with Rollback:

Set autocommit \u003d 0; // Turn off AutoCommit Start Transaction; ... some action with database (insert, update, delete ...) rollback; // Withdraw a series of actions, do not write to the physical database

In MySQL, there is no mechanism of nested transactions. One bd connection is one transaction. A new transaction within a single connection can start only after the previous one is completed.

For some operators, it is not possible to roll back using ROLLBACK. These are data definition statements (DATA Definition Language - DDL). This includes Create, Alter, Drop, Truncate, Comment, Rename requests.

The following operators implicitly complete the transaction (as if Commit was issued before execution):

Alter table
DROP DATABASE.
Load Master Data.
Set autocommit \u003d 1
Begin.
Drop Index
Lock Tables
Start Transaction.
Create Index
Drop Table
Rename Table
Truncate Table

Please note that in the case of SQL errors, the transaction itself does not roll back. Usually errors are processed by SQL Wrappers in the application itself, such as PHP PDO for example. If you want to roll back the changes in the event of an error directly in MySQL, you can create a special procedure and perform Rollback in it in the handler:

CREATE PROCEDURE PRC_TEST () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; // here roll back the transaction in case of END error; Start Transaction; INSERT INTO TMP_TABLE VALUES ("NULL"); COMMIT; End; Call Prc_Test ();

But this method is more likely to get acquainted, not a guide to action. Why? I really don't recommend this to do so, since mostly database errors are processed using SQL wrappers on the application side, such as PHP PDO for example, to completely control transactions from there.

Consider a practical example: there are 2 tables, users - Users and user information - user_info. Imagine that we need to either execute 3 database requests, or do not fulfill them at all, as otherwise it will lead to the failures of the application.

Start Transaction; INSERT INTO USER (ID, NIK) VALUES (1, "NIKOLA"); INSERT INTO USER_INFO (ID, ID_USER, ITEM_NAME, ITEM_VALUE) VALUES (1, 1, "Name", "Nikolay"); INSERT INTO USER_INFO (ID, ID_USER, ITEM_NAME, ITEM_VALUE) VALUES (2, 1, "AGE", "24"); COMMIT;

In general, I think the principle of operation of the transaction is understandable. But everything is not so simple. There are problems of parallel transactions. Consider an example. Imagine that during the execution of this transaction, another user created a second parallel transaction and made a Select * from user request after the first request "INSERT INTO USER (ID, NIK) VALUES (1, 'Nikola" was performed in our transaction " What will the user of the second transaction sees? Will he be able to see the inserted record even when the results of the first transaction have not yet been fixed (did COMMIT not occurred)? Or can he see the changes only after the results of the first transaction will be fixed? It turns out there are both options. It all depends on the level of isolation of the transaction.

Transactions have 4 levels of insulation:

0 - reading unconfirmed data (dirty reading) (Read Uncommitted, Dirty Read) - the lowest level of insulation. At this level, it is possible to read inadequate changes in parallel transactions. In this case, the second user will see the inserted entry from the first unfixed transaction. There is no guarantee that the unfixed transaction will be rejected at any time, so such reading is a potential source of errors.
1 - reading confirmed data (READ COMMITTED) - It is possible to read the data of only fixed transactions. But at this level there are two problems. In this mode, the lines that participate in the selection within the transaction, are not blocked for other parallel transactions, the problem No. 1 flows out of this: "non-repeated reading" (Non-Repeatable Read) is a situation when several samples occur within the transaction (Select ) According to the same criteria, a parallel transaction is performed between these samples, which changes the data involved in these samples. Since the parallel transaction has changed the data, the result for the next sample on the same criteria in the first transaction will be different. Problem number 2 - "Phantom reading" - this case is considered below.
2 - repeated reading (REPEATABLE READ, SNAPSHOT) - At this insulation level, it is also possible to read the data of only fixed transactions. Also, at this level, there is no problem of "non-refused reading", that is, lines that participate in the sample within the transaction, are blocked and cannot be changed by other parallel transactions. But the tables are not entirely blocked. Because of this, the problem of "phantom reading" remains. "Phantom reading" is when, during the execution of one transaction, the result of the same samples may change due to the fact that the entire table is blocked, but only those lines that participate in the sample. This means that parallel transactions can insert strings in the table in which the sample is performed, so the two select * from table requests can give a different result at different times when inserting data parallel transactions.
3 - serializable (Serializable) - serializable transactions. The most reliable level of transaction isolation, but at the same time the slowest one. At this level, there are no problems of parallel transactions at all, but for this will have to pay the speed of the system, and the speed in most cases is extremely important.

SET Transaction - This operator sets the level of isolation of the next transaction, globally or only for the current session.

SET Transaction Isolation Level
(Read uncommitted | Read Committed | Repeatable Read | Serializable)

You can also set the initial global insulation level for the MYSQLD server by running it with the -transAction-ISOLATION option.