Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to solve the problem of MYSQL transaction error not rolling back". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

About two weeks ago, a classmate threw out this picture and asked what was going on. I didn't have time to write it down immediately. I had time to deal with it. I originally wanted to be lazy during the holiday, but I had to do what I promised others.

In fact, the figure above is a classic MYSQL record locks problem, which should be caused by a record in the testdb.a table, such as

Select name from a where name = 'Jassica' for update

During operation, the above situation may occur if there are other statements that also operate on the record name = 'Jassica' in another session.

That's what the official documentation says, but in fact, some people are probably not convinced how they can simulate the above lock information that appears in that show engine innodb status.

Next, let's do it and see what kind of situation can show the above information.

1 Please create a simple table, exactly how simple it is, 1 should have a primary key, 2 should have a non-primary key field, such as varchar (30), and then enter some information, like the following.

Then start two sessions

1 session 1 begin

2 session 1 update a set name = 'aaa' where name >' PPP'

3 session 2 begin

4 session 2 update a set name = 'PPP' where name >' Jassica'

Then I have the lock information similar to the screenshot given to me by my classmate above.

There are two questions.

1 name > 'PPP' We don't know exactly how many records have been UPDATE

We don't know how many records there are in 2 name > 'Jassica'.

Question 1: five records have been updated, and five records conform to name > 'PPP'.

Question 2 > 'Jassica''

So in the deadlock information

The information is sinomina, x_professor, x_man four records.

Conflict with 5 records in SESSION 2 to be updated.

All above are in MYSQL 8.019 RC mode.

The reason for the error message so far is probably because it is clear. In fact, our topic today has just begun. The question is whether to roll back or not if there are other udpate statements in the transaction before the update statement.

The answer is: no rollback

Let's see if it's like this:

1 session 1 begin

2 session 1 update a set name = 'aaa' where name >' PPP'

3 session 2 begin

4 session 2 update a set name = '11111111' where name = 'PPP'

5 session 2 update a set name = 'PPP' where name >' Jassica'

6 session 1 commit

7 session 2 commit

Session 2 failed, did PPP become 111111? This is the key today, according to the traditional database, of course not, should all be rolled back.

Then your MYSQL here is 8.019 as an example, what is the answer?

Answer: as expected, if there are other DML statements on your failed transaction, it will be executed.

This is the same as the default transaction execution of SQL SERVER. If the transaction is wrong, the above execution will not return OMG. I think it is definitely different from what developers think.

In fact, MYSQL is the same as SQL SERVER, specific SQL SERVER how to avoid this problem (please do your own Baidu, or find a text like this written a long time ago).

Regardless of SQL SERVER here, MYSQL actually has a parameter that defaults to disabled.

We need to turn on the parameter innodb_rollback_on_timeout equals 1. Its function is that automatic rollback does not cause InnoDB lock wait timeout errors. And this parameter needs to be disabled and MYSQL is configured in the configuration file, which takes effect when the restart takes effect.

Session 2

Session 1

Therefore, if there is a development response database when the data is not right, then the DB door should pay attention to whether the parameter is ENABLED OR DISABLED.

This is the end of the content of "how to solve the problem that MYSQL transaction errors do not roll back". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.