Problems caused by 1-5 major transactions

When the system crashes, it is found that there are no committed transactions, and some operations will be rolled back.

(1) begin; select from t where id in the first window

1 3 5

(2) in the second window begin; insert into t values (2); commit

(3) select from t where id in the first window

1 3 5 commit; changes the current transaction isolation level

Set sesstion tx_isolation='read-committed'; (read submitted)

Select from t where id < 7; 1 3 5 2

(4) in the second window begin; insert into t values (4); commit

(5) in the first window select from t where id < 7; 1 3 5 2 4

The fourth isolation level

Serializable serializable has the highest isolation level, and every row of data read is locked, resulting in a large number of lock timeouts, so this isolation level is rarely used, with isolation from low to high and concurrency from high to low.

Innodb default isolation level is repeatable

What is a big deal?

Define transactions that take a long time to run and operate more data

Risk:

1 locking too much data, causing a lot of blocking and lock timeout

2 the rollback takes a long time and execution time, which is easy to cause master-slave delay.

3 if the transaction of the master database is executed for several hours and then committed, it will be written to the binlog, and the binlog log will be read from the slave database before synchronization begins.

4 innodb is a row-level lock, which is equivalent to locking the entire table when all records are involved

How to deal with big affairs

1 avoid dealing with too much data at once

2 remove unnecessary select operations in the transaction