MySQL things processing

MySQL is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on. In this way, these database operation statements form a single thing. Notice the points:

• In MySQL, transactions are supported only by databases or tables that use the InnoDB database engine.
• Transaction processing can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either all executed or not executed.
• Things are used to manage insert,update,delete statements.

Let's start with a brief introduction to things. A thing is the unit of execution of a DBMS. It consists of a limited list of database operations, but not any sequence of database operations can be a thing. In general, a thing must meet 4 conditions (ACID):

Atomicity: The statement that makes up a thing deals with a logical unit that cannot be executed only as part of it. In other words, things are the smallest unit of indivisible. For example, in the course of bank transfer, it is unreasonable to change an account by subtracting the amount of the transfer from one account and adding it to another account.

Consistency: MySQL databases are consistent before and after the execution of things. In other words, things should correctly transform the state of the system. For example, in the course of a bank transfer, either the transfer amount is from one account to another, or two accounts are unchanged, no other situation.

Isolation: One thing handling has no effect on the other. For example, in the course of a bank transfer, another transfer thing can only wait until the transaction is not submitted.

Reliability: The effects of the processing of things can be permanently preserved. Conversely, things can withstand all the failures. including servers, processes, communications, media failures, and so on. For example, in the process of bank transfer, the status of the account after the transfer can be saved.

There are two main ways in which MySQL handles things:

1. Begin,rollback,commit to achieve

Begin a Thing

Rollback Things Roll back

Commit things confirm, things submit

2, directly using set to change the MySQL auto-commit mode

MySQL default z is automatically submitted, that is, when you submit a query, it executes directly.

Set autocommit = 0 Disables autocommit mode

Set autocommit = 1 to turn on autocommit mode

MySQL only uses InnoDB and BDB types of data tables to support the processing of things (remember)!

Take a look at an example:

Let's assume the background of the problem: online purchase, a book "MySQL database" number 123, only the last one, and this time two users almost at the same time the book issued a request for a book, lets us take a look at the whole process:

Before specific analysis, let's look at the definition of the data table:

for User A, he moves a little bit faster than B, and the actions triggered in the purchase process are roughly the same:

1.select book_number from book where book_id = 123;

Book_number greater than 0, confirm purchase behavior and update Book_number

2.update Book Set book_number = book_number-1 where book_id = 123;

successful book purchase

on the surface, the operation of a B was successful, they all bought a book, but only one in stock, how can they all succeed? Look at the contents of the data table Book_number, has become "1", which is certainly not allowed (in fact, declaring such a column type should be added to the unsigned property to ensure that it can not be negative, this is to illustrate the problem, so there is no such setting)

OK, the problem is clear, and then to see how to use the transaction to solve the problem, open the MySQL manual, you can see that you want to use the transaction to protect your SQL correctly executed is very simple, basically three statements: start, Commit, rollback.

Start: The start transaction or BEGIN statement can start a new transaction

Commit: Commit can commit the current transaction, change becomes permanent change

Rollback: Rollback can roll back the current transaction and cancel its change

Additionally, SET autocommit = {0 | 1} can disable or enable the default autocommit mode for the current connection.

is that just a matter of wrapping up our SQL statements with a transactional statement to make sure it's right? For example, the following code:

The answer is no, this still does not prevent the problem, if you want to avoid such a situation, the actual should be as follows:

because a for UPDATE is added, a row lock is added to this record, and if the transaction does not end completely, the other transactions are using SELECT ... The FOR update request will wait until the last transaction finishes before it can continue, avoiding the problem, and note that if your other transactions are using a SELECT statement without a for update, this protection will not be available.

MySQL things processing