A story about spring transactional

Preface : Today, when I was doing a spring transaction, I thought it would be very simple to complete, but encountered a bit of twists and turns, spring's transaction needs to rely on the MySQL transaction isolation level, the following to record this process.

The cause of the matter is this, due to the update of a withdrawal amount and the corresponding insertion of a withdrawal record, there is no transaction, resulting in the occurrence of a probabilistic event, the withdrawal record increased, but because the withdrawal record at the time of the update due to other transaction occupancy reasons, and did not execute, The first time did not notice this reason, because there is no error in the log, the code logic does not appear to be the problem, but the problem arises, then after thinking, it should be because another thread of the transaction affected the execution of the UPDATE statement, did a simulation test, as follows

START TRANSACTION; UPDATE money_user SET Dayintomoney = ten WHERE uid = 2;insert into Money_transfer (id,serial_number) VALUES; UPDATE money_user SET Dayintomoney = ten WHERE uid = 2;

The INSERT statement was inserted because the following INSERT statement and UPDATE statement did not add a transaction, but the UPDATE statement was not executed because the previous transaction did not commit.

Modify:

And then I did a little bit. Method of processing spring transaction timeout unit is seconds

@Transactional (timeout=2) public int Updateforzenmoney (moneytransfer moneytransfer, int uid) {//If it is a withdrawal request, the pre-deduction of the frozen funds if ( Moneytransfer.gettype (). Intvalue () = = 1) {Moneytransferservice.updatetotalmoneyforpertransfermoney (UID, Moneytransfer.getamount ());//Insert the request into the Money_transfer table Moneytransferservice.addmoneytransfer (Moneytransfer); return 1 ;} else {moneytransferservice.addmoneytransfer (moneytransfer); return 2;}}

First to try the UPDATE statement, then data insertion, so that the UPDATE statement execution, because of the impact of another transaction, there will be a timeout of two seconds, and then rollback, but actually in the process of execution, I first to execute the other transaction, and then to execute the method , and found that the transaction rollback time is far greater than 2 seconds, why,

Go on:

I first thought about the transaction execution Timeout setting for MyBatis (my project is Spring+mybatis+mysql),

The timeout unit is milliseconds this setting driver waits for the database to return the result of the request and throws the maximum wait value for the exception time. Default is not set (drive self-processing).

And then you do the following.

<update id= "Updatetotalmoneyforpertransfermoneyout" parametertype= "HashMap" timeout= ">"

Continue to execute, found that the execution time is still far greater than 2+2+2, nearly 50 milliseconds, after a series of experiments, suddenly thought is not MySQL default transaction timeout time reason, because MySQL InnoDB default timeout time is 50s, and my money table is just the InnoDB engine, And then

Mysql> Show variables like ' innodb_lock_wait_timeout '; +--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| Innodb_lock_wait_timeout |    |+--------------------------+-------+1 row in Set

Ok, find him, then exactly is not it, modify

mysql> Set innodb_lock_wait_timeout = 10; Query OK, 0 rows affectedmysql> show variables like ' innodb_lock_wait_timeout '; +--------------------------+-------+ | Variable_name            | Value |+--------------------------+-------+| Innodb_lock_wait_timeout |    |+--------------------------+-------+1 row in Set

Continue execution, found that the execution time is close to about 10 seconds, and then set 10 seconds to 20 seconds, OK, the test out of the time-out is about 20 seconds, indicating that this idea is correct, but why spring's transaction timeout time does not play a role, continue to investigate

mysql> set innodb_lock_wait_timeout = 1; Query OK, 0 rows affectedmysql> show variables like ' innodb_lock_wait_timeout '; +--------------------------+-------+ | Variable_name            | Value |+--------------------------+-------+| Innodb_lock_wait_timeout | 1     |+--------------------------+-------+1 row in Set

Set the MySQL time to a bit shorter and then set the spring transaction timeout to 5 seconds again, removing the MyBatis timeout

@Transactional (timeout=5) public int Updateforzenmoney (moneytransfer moneytransfer, int uid) {

And then execute, look at the log time

DEBUG 2014-12-12 16:53:49,784 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  preparing:update Money_ Usdebug 2014-12-12 16:53:49,785 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> parameters:debug 2014-12-12 16:54:00,795

The transaction rollback time is almost exactly the time of the two statement transaction execution time 5+5=10 seconds.

Conclusion:

Although the transaction timeout for MyBatis has not been investigated again, it is confirmed that the transaction time-out time of spring and the transaction time-out time of MySQL are mutually affected, and the last option I confirmed is that the InnoDB timeout for modifying MySQL is 20m. Then remove the timeout on the Java method

@Transactional <span style= "white-space:pre" ></span>public int Updateforzenmoney (moneytransfer Moneytransfer, int uid) {

Again, testing, the rollback time of the transaction is about 20m.

Summary : What happens for a reason, and is interrelated, so long as we resist the need to try and investigate, we will find the answer.

A story about spring transactional